Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Updated 15/01/2019  

 

Synchronising a table with external data - Part 1

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:

•the number of records to be transferred or modified

•the number of records containing null values

•speed and file size

 

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

 

Click to download: Sync Data Example v5.2    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.

 

 

 

Colin Riddington      Mendip Data Systems                                 LAST UPDATED  15/01/2019

* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

1.  Update Existing /Append New / Delete Old

 

    Firstly UPDATE existing records

Click any image to view a larger version

Difficulty level :   Moderate

The following code assumes that:

 

•    the records in tblData are being updated from tblImport

•    both tables have the same fields and an autonumber primary key field.

•    tblImport contains ALL records that should be imported to tblData

 

 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  

 

 DELETE tblData.* FROM tblData LEFT JOIN tblImport ON tblData.ID = tblImport.ID

 WHERE (((tblImport.ID) Is Null));

 

 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  

2.  Combined Upend (AKA Upsert) / Delete Old  

 

    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 Upend Query

 

UPDATE tblData RIGHT JOIN tblImport ON tblData.ID = tblImport.ID

 SET tblData.StartDate = [tblImport].StartDate, tblData.EndDate = [tblImport].EndDate,

     tblData.NCheck = [tblImport].NCheck;

 

 DELETE tblData.* FROM tblData LEFT JOIN tblImport ON tblData.ID = tblImport.ID

 WHERE (((tblImport.ID) Is Null));

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

3.  Delete All / Append All

 

    First DELETE ALL records from tblData

 

 DELETE tblData.* FROM tblData;

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

 

 INSERT INTO tblData ( StartDate, EndDate, NCheck )

 SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck FROM tblImport;

4.  Make Table / Append All

 

    This can be done in more than one way.

    The first method overwrites the existing table

 

 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

 

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

SUMMARY

 

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

SyncData1 SyncData2 SyncData3 SyncData4 SyncData5

qryAppendNew

qryDeleteOld

qryUpend

SyncData4

qryDeleteOld

SyncData6

qryUpdate

qryDeleteAll

qryAppendAll

SyncData7

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

qryMakeTable

SyncData8

qryAddPrimaryKey

 

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

IMPORTANT:

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

DO NOT USE THIS

Doing this will cause the primary key to reseed 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 

 

 CREATE TABLE tblData (ID AUTOINCREMENT NOT NULL PRIMARY KEY,

      StartDate DATETIME, EndDate DATETIME, NCheck INT);

qryCreateDataTable

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

 

 INSERT INTO tblData ( StartDate, EndDate, NCheck )

 SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck FROM tblImport;

qryAppendAll

SyncData7 SyncData9

NOTE:

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

 

 

 

Click to download

    The sample database:                       Sync Data Example_ v5.2                                        Approx 2 MB (zipped)

    A PDF version of this article:              Synchronise Table with External Data Part 1

   

 

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

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