Synchronise a table with external data - Part 2 (of 2)

Page 1

Last Updated 14 Mar 2019 Difficulty level : Moderate

Section Links:
Update Existing/Append New (JOIN)/Delete Old
Update Existing/Append New (TEMP table)/Delete Old
Update Existing/Append New (NOT Exists)/Delete Old
Delete All/Append All
Make Table/Append All
Combined Upend/Delete Old
Summary
Downloads

The first part of this article discussed four ways of synchronising data where the external data source includes a primary key field.
This is the most likely situation where you are synchronising records with another Access table or external database

The second part looks at 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

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

The following code assumes that:

a) the records in tblData are being updated from tblImportNoPK
b) both tables have the same fields apart from there being no autonumber PK field in the import table.
c) tblImport contains ALL records that should be imported to tblData

SyncData10
This requires some changes to the code used in the first part of this article.

More care is needed in checking the outcomes will be what you want before synchronising the data
This is especially important if you have any null values in import or destination tables.
This is because a null value is not equal to anything else – NOT even another null value.

5. Update Existing / Delete Old (using JOIN) / Append New Return To Top

First update existing records.
The following query won’t work as it will only update records if they are the same in both tables. This is of course completely pointless

DO NOT USE THIS QUERY

SyncData11

Instead create a cartesian join query (unlinked tables) and specify conditions to use.
For example, this will update any records where any ONE field has been changed.

qryUpdateNoPK

SyncData12

INSERT INTO tblData ( StartDate, EndDate, NCheck )
SELECT DISTINCT tblImportNoPK.StartDate, tblImportNoPK.EndDate, tblImportNoPK.NCheck
FROM tblImportNoPK LEFT JOIN tblData ON (tblImportNoPK.StartDate = tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.[NCheck] = tblData.[NCheck])
WHERE (((tblData.StartDate) Is Null) AND ((tblData.EndDate) Is Null) AND ((tblData.NCheck) Is Null));

As no PK is involved, you should set Unique Records = Yes. This is shown as DISTINCTROW in the query SQL

UPDATE DISTINCTROW tblData, tblImportNoPK SET tblData.StartDate = [tblImportNoPK].[StartDate],
tblData.EndDate = [tblImportNoPK].[EndDate], tblData.NCheck = [tblImportNoPK].[NCheck]
WHERE (((tblData.StartDate)<>tblImportNoPK.StartDate) And ((tblData.EndDate)=tblImportNoPK.EndDate)
And ((tblData.NCheck)=tblImportNoPK.NCheck)) Or (((tblData.StartDate)=tblImportNoPK.StartDate)
And ((tblData.EndDate)<>tblImportNoPK.EndDate) And ((tblData.NCheck)=tblImportNoPK.NCheck))
Or (((tblData.StartDate)=tblImportNoPK.StartDate) And ((tblData.EndDate)=tblImportNoPK.EndDate)
And ((tblData.NCheck)<>tblImportNoPK.NCheck));

NOTE:
If you have a large number of existing records to check and update, this method will be VERY SLOW
This is particularly true if there are a large number of NULL values (as in the example database)

It will NOT pick up any records where TWO or more fields have been changed
Additional criteria would be needed to manage those situations.
The query could get very complicated in such cases. If so, this method is best avoided

Assuming the method is feasible in your situation, next DELETE any old records that aren’t in the import table.
Again set Unique Records = Yes

qryDeleteOldNoPK

SyncData13

DELETE DISTINCTROW tblData.*, tblImportNoPK.StartDate, tblImportNoPK.EndDate, tblImportNoPK.NCheck
FROM tblData LEFT JOIN tblImportNoPK ON (tblData.NCheck = tblImportNoPK.NCheck)
AND (tblData.EndDate = tblImportNoPK.EndDate) AND (tblData.StartDate = tblImportNoPK.StartDate)
WHERE (((tblImportNoPK.StartDate) Is Null) AND ((tblImportNoPK.EndDate) Is Null) AND ((tblImportNoPK.NCheck) Is Null));

NOTE:
Once again, this may be very slow if you have a large number of existing records to check

It may also DELETE records that should be retained if you have DUPLICATE or NULL values in more than one import fields or destination fields.
If so, edit the query to manage the issue or use a different approach

If this approach looks OK, finally APPEND all new records.
Set Unique Values = Yes (shown as DISTINCT in SQL view)

qryAppendNewNoPK

SyncData14

NOTE:
A similar warning to the UPDATE & DELETE parts of this method.
The above query may cause DUPLICATE records if you have NULL values in more than one import fields or destination fields.
You may be able to edit the query to manage the issue.

However, it may be safest to manage NULL values by first setting them to valid but unused values in each field of the import & destination fields.
For example, update null dates to 01/01/9999 and number fields to an unlikely value such as -1000000
You will need to do this for each field separately in the two tables. For example:

UPDATE tblData SET tblData.StartDate = #1/1/9999# WHERE (((tblData.StartDate) Is Null));

Once you have done this, run the above queries.
After completion run a further UPDATE query to revert the modified values in the destination table back to null.

Once again do this for each field separately. For example:

UPDATE tblData SET tblData.StartDate = Null WHERE (((tblData.StartDate)=#1/1/9999#));

Overall, this method is much more complex to administer particularly if you have DUPLICATE or NULL values to manage.
It is also likely to be VERY SLOW particularly for large datasets

You should also be aware that as some records will be deleted, there will be gaps in the autonumber field

6. Update Existing / Delete Old (using TEMP table) / Append New Return To Top

This is similar to the previous approach but uses a different method for identifying old records for deletion

First of all, UPDATE existing records as in method 5

qryUpdateNoPK

SyncData12

Next create a TEMP table to identify UNMATCHED records that are in tblData but not in tblImportNoPK

qryMakeTableUnmatchedData

SyncData15

SELECT tblData.* INTO tblDataUnmatched
FROM tblImportNoPK RIGHT JOIN tblData ON (tblImportNoPK.StartDate = tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.[NCheck] = tblData.[NCheck])
WHERE (((tblImportNoPK.StartDate) Is Null) AND ((tblImportNoPK.EndDate) Is Null) AND ((tblImportNoPK.NCheck) Is Null));

Next DELETE these unmatched records using the TEMP table created earlier

qryDeleteUnmatched

SyncData16

DELETE tblData.*
FROM tblData INNER JOIN tblDataUnmatched ON tblData.ID = tblDataUnmatched.ID;

The TEMP table can now be deleted if this is a one-off operation

DoCmd.DeleteObject acTable, "tblDataUnmatched"

NOTE:
If this method is to be used repeatedly, it is better to keep the temp table and just EMPTY it after use

Finally APPEND all new records setting Unique Values = Yes as in method 5

qryAppendNewNoPK

SyncData14

7. Update Existing / Delete Old (using NOT exists) / Append New Return To Top

This method uses the same UPDATE (qryUpdateNoPK) and APPEND queries (qryAppendNewNoPK) as in methods 5 & 6

The DELETE part uses a different approach to identify and then delete records from tblData that do NOT exist in the import table

qryDeleteNotExists

SyncData17

DELETE tblData.*,
Exists (SELECT 1 FROM tblImportNoPK WHERE (tblImportNoPK.StartDate =tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.NCheck = tblData.NCheck)) AS Unmatched
FROM tblData
WHERE (((Exists (SELECT 1 FROM tblImportNoPK WHERE (tblImportNoPK.StartDate =tblData.StartDate)
AND (tblImportNoPK.EndDate = tblData.EndDate) AND (tblImportNoPK.NCheck = tblData.NCheck)))=False));

This type of DELETE query is more difficult to design but may be MORE EFFICIENT for Access to process.
It may therefore run SLIGHTLY faster than methods 5 or 6

8. Delete All / Append All Return To Top

This is identical to method 3 in the first part of this article.
First DELETE ALL records

qryDeleteAll

SyncData6

DELETE tblData.* FROM tblData;

Now APPEND all records from the import table.

qryAppendAllNoPK

SyncData18

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

This is the simplest and most reliable method if the import table contains all the required records as you don’t need to manage issues with null values.
It will run MUCH faster than methods 5, 6 or 7.

For most situations, the significant reduction in time more than offsets the additional increase in file size caused by replacing all records

9. Make Table / Append All Return To Top

This is almost identical to method 4 in the first part of this article
First create a new tblData with autonumber PK field using code or a data definition query

qryCreateDataTable

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

If using the query designer, this can only be done in SQL view

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

qryAppendAllNoPK

SyncData18

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

Once again this is very simple to administer if all required records exist in the import table

It will again run very fast with times similar to method 8 but a larger file 'bloat' due to deletion and creation of tables

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

This method cannot be used as there is no PK field in the import table

Summary Return To Top

In cases where there is no PK field in the import table, your choices may be more limited.
Take great care to ensure the results are what you want before synchronising data
ALWAYS use a SELECT query to check the records before running a DELETE, APPEND or UPDATE query

For comparison, here are the results I obtained for all the tests in both parts of this article:

Tests 1->4 : Import table with PK
All methods are fast but the UPEND query is slightly slower

Tests 5->9 : Import table with no PK
Methods 5-7 are MUCH slower and more complex to design
Methods 8 & 9 are about as fast as the first 4 methods

SyncData19

NOTE:

a) Compacting will recover much of the space added after deleting records

b) Repeatedly creating / overwriting tables can cause instability & in some cases may lead to corruption

c) For one-off synchronisation, it may be sufficient to create the queries you need and discard them after use
However, if you will need to repeat the operation, it is sensible to create procedures to run each of the query definitions in turn

d) The example database contains all the tables / queries and procedures used in this article

Downloads Return To Top

Click to download
The sample database: Sync Data Example_v5.3 Approx 2 MB (zipped)

A PDF version of this article: Synchronise Table with External Data Part 2 Approx 0.4 MB (zipped)

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

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 2 of 2 1 2