Last Updated 14 Mar 2019                                                Difficulty level :   Moderate

Section Links:
          Update Existing/Append New/Delete Old
          Combined Upend/Delete Old
          Delete All/Append All
          Make Table/Append All
          Summary
          Downloads


As with most processes in Access, there are several ways of achieving this.
Whichever method is used it is recommended that a backup is made before synchronising with external data in case any problems arise.

The best solution for an individual situation will depend on various factors including:
a)   the number of records to be transferred or modified
b)   the number of records containing null values
c)   speed and file size

The example application attached includes all the code used in this article

Click to download: Sync Data Example v5.3     approx 2 MB (zipped)

It is used to synchronise a table tblData originally containing 200 records with another table tblImport containing 20,000 records.
Several of the original records have missing or changed data that needs to be updated.

You can either run the individual queries manually or use the supplied procedures to run each query in turn.
If you use the procedures, the code measures the time taken and the increase in file size for each method.



The following code assumes that:

a)   the records in tblData are being updated from tblImport
b)   both tables have the same fields and an autonumber primary key field.
c)   tblImport contains ALL records that should be imported to tblData




1.   Update Existing /Append New / Delete Old                                                                                     Return To Top

First of all, UPDATE existing records

qryUpdate

UPDATE tblData INNER JOIN tblImport ON tblData.ID = tblImport.ID
SET tblData.StartDate = [tblImport].StartDate, tblData.EndDate = [tblImport].EndDate,
    tblData.NCheck = [tblImport].NCheck;

Next APPEND any new records omitting the autonumber ID field

qryAppendNew

INSERT INTO tblData ( StartDate, EndDate, NCheck )
SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck
FROM tblImport LEFT JOIN tblData ON tblImport.ID = tblData.ID
WHERE (((tblData.ID) Is Null));

Finally DELETE any old records that aren’t in the import table

qryDeleteOld

DELETE tblData.*
FROM tblData LEFT JOIN tblImport ON tblData.ID = tblImport.ID
WHERE (((tblImport.ID) Is Null));

2.   Combined Upend (AKA Upsert) / Delete Old                                                                                   Return To Top

An UPEND (or UPSERT) query can sometimes be used to combine the APPEND and UPDATE queries
This is an append query with an outer join.
For more details, see my article:Upend Query

qryUpend

UPDATE tblData RIGHT JOIN tblImport ON tblData.ID = tblImport.ID
SET tblData.StartDate = [tblImport].StartDate, tblData.EndDate = [tblImport].EndDate,
        tblData.NCheck = [tblImport].NCheck;

Next DELETE any old records that aren’t in the import table (same as 1. above)

qryDeleteOld

DELETE tblData.*
FROM tblData LEFT JOIN tblImport ON tblData.ID = tblImport.ID
WHERE (((tblImport.ID) Is Null));

3.   Delete All / Append All                                                                                                                       Return To Top

First DELETE ALL records from tblData

qryDeleteAll

DELETE tblData.* FROM tblData;

Now APPEND all records from the import table omitting the ID field

qryAppendAll

INSERT INTO tblData ( StartDate, EndDate, NCheck )
SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck FROM tblImport;

NOTE:
This continues the existing ID values so if 200 records are deleted, the first appended record has ID=201
As we don’t want to modify existing autonumber values, we need to RESET the autonumber seed to ID=1 before appending new records.
To do so, I have used a modified version of code by Allen Browne originally at: http://allenbrowne.com/func-ADOX.html#ResetSeed
The modified ResetSeed function is in the module modADOX in the attached example file.

Using this code requires the VBA reference: Microsoft ADO Ext 6.0 for DDL and Security



4.   Make Table / Append All                                                                                                               Return To Top

This can be done in more than one way.
The first method overwrites the existing table

qryMakeTable

SELECT tblImport.ID, tblImport.StartDate, tblImport.EndDate, tblImport.NCheck
INTO tblData FROM tblImport;

NOTE:
The new table will NOT have a primary key field.
This needs to be added using code or a data definition query. If you use the query designer, this can only be done in SQL view.

qryAddPrimaryKey

ALTER TABLE tblData ADD CONSTRAINT PK PRIMARY KEY (ID);

IMPORTANT:
The following code is INCORRECT as it also RESETS the autonumber field

DO NOT USE THIS

ALTER TABLE tblData ALTER COLUMN ID COUNTER(1, 1) NOT NULL PRIMARY KEY;

Doing this will cause the primary key to re-seed starting with ID=1 with any new records
As that ID value already exists, a key violation error occurs preventing new records being APPENDED

In my opinion, a MUCH BETTER approach is to use code to create the new table with an autonumber PK field using a data definition query

qryCreateDataTable

CREATE TABLE tblData (ID AUTOINCREMENT NOT NULL PRIMARY KEY,
        StartDate DATETIME, EndDate DATETIME, NCheck INT);

Next populate the table by APPENDING all records as in method 3

qryAppendAll

INSERT INTO tblData ( StartDate, EndDate, NCheck )
SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck
FROM tblImport;

Summary                                                                                                                                                 Return To Top

In each of the above cases, file size will increase as new records are being added
Methods 1 and 2 will cause less file 'bloat' as the number of records being added/deleted will be smaller
Methods 3 and 4 will cause more file bloat as all records are being replaced

The fastest method will normally be method 4 followed by method 1 or 3
Method 2 will normally be the slowest as the UPEND query is less efficient for Access to process

However, unless you have a very large number of records to synchronise, the time differences may be negligible



NOTE:
Indexing the fields will make the process slower as the indexes will also need to be updated.



Downloads                                                                                                                                             Return To Top

Click to download

    The example database:                     Sync Data Example_v5.3                                        Approx 2 MB (zipped)

    A PDF version of this article:              Synchronise Table with External Data - Part 1      Approx 0.3 MB (zipped)

UPDATE: 14 Mar 2019
Fixed error populating data tables in the previous version (5.2) of the example database



The second part of this article will consider ways of synchronising data where there is no primary key field in the import table.
This is often the case when you are importing data from an Excel or CSV file



Feedback                                                                                                                                                Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 14 Mar 2019



Return to Access Articles Page Return to Top Page 1 of 2 1 2