How do I import data that will append new records but update existing ones?

[Scott Huish]

How do I import data that will append new records but update existing ones?

Right now, I get an error that records were lost due to key violations. I can't allow duplicate email addresses, but the initial imports are coming from many, many files. I need existing ones to update and new ones to be added.

 

[ranman256]

Your import macro will run 2 queries.. (turn off notifictions, docmd.setwarnings off)

1st to append data (If it's keyed, it will ignore the duplicates)
2nd to update matching data.

 

[Scott Huish]

I don't know how to write macros for Access (Excel yes, Access no). I have just been looking at Update Queries, and I don't quite understand how to manually set them up yet.

The source Excel file for the Update has all the same fields except for ID which is autogenerated in the main customer file.
I need to do this a lot and the format will be the same, and so far, it says I can't update on * which the help file says I can use to select all fields. So I need all fields from the update file to append and update everything except ID.

 

[xenou]

It depends on the keys. Macros/queries are only semantics (you use macros to kick of the queries). So the general procedure ranman described is correct: first, append new data (so the dataset is "complete"), then second update data (this may mean that the records you just updated are updated, but that is irrelevant since they are updated to the same values since they are brand new).

But if the ID's aren't reliable then there is a problem of how to identify records. You must have some field or fields in the table that can uniquely and reliably identify each record (whether it is one field, such as ID, or multiple fields such as (FirstName, LastName, ZipCode) or any other reliable method (or hopefully reliable) of distinguishing a record from all the others.

 

[adam087]

How is your data coming in to the DB from the Excel sheet. I'm working on the assumption you have a table (tblImport) that is either linked or created from the Excel sheet.

I then created a 'helper' query (called qryCheckImport) which checks if the keys in the import file are in the main DB (tblMain) with this SQL

SELECT tblMain.myId, Count(tblMain.myId) AS CountOfmyId
FROM tblImport RIGHT JOIN tblMain ON tblImport.ID = tblMain.myId
GROUP BY tblMain.myId;

You can then use this to instruct an append query on which records to import.

INSERT INTO tblMain ( myId, myName )
SELECT tblImport.ID, tblImport.myName
FROM tblImport LEFT JOIN qryCheckImport ON tblImport.ID = qryCheckImport.myId
WHERE qryCheckImport.CountOfmyId Is Null;

And you can do the opposite to check which records to update.
You can create a Macro to do it without even touching code in Access (Ribbon --> Create --> Macros & Code --> Macro).

Hope that helps. At least a little!

/AJ

 

[Scott Huish]

I've been right clicking on the table, then clicking on Import, but this only seems to bring in new records. Data is coming in from multiple files, so we will need to append and update, because the email address (the consistent field) may contain other information that didn't exist in other files. So I need to be able to do both add and update. Our old method of keeping the lists was majorly convoluted and we are trying to clean it up.

I am copying the data from the spreadsheet into a form that has all the fields in my main Customer table, and then trying to get this, to add when the email doesn't exist and update existing info when it does.

 

[adam087]

In general I would always support the principal of bringing the data in to a holding table first (from one or more sources) and then appending/updating the data from there. It just gives us more control. You could create a macro to bring the data from multiple files in to one temp table and then run your queries on it. You could use some code a little like this (if it helps I could look in to the import code as well).

    Dim fDialog             As Office.FileDialog
    Dim varFile             As Variant
    
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
    With fDialog
        .AllowMultiSelect = True
        .Title = "Import Events"
        .Filters.Clear
        .Filters.Add "Excel Documents", "*.xlsx"
    End With
    
    If fDialog.Show Then
    'Check they didn't cancel the file dialog box
    
        'Scroll through the selected files
        For Each varFile In fDialog.SelectedItems

           'do your import

        Next varFile

    End If

Copying into a form is cool, but copy-paste removes some of your flexibility for validation; and it breaks down with large volumes of records.
I do think I agree that a new table with separate append/update queries is your only option for combining new records with updating old. Otherwise whatever import data routines you run from the native Access environment will toss out the duplicate keys and not do any updating.

/AJ