Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Updated 14/03/2019  

Synchronising a table with external data - Part 2

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.

 

 

Colin Riddington      Mendip Data Systems                                 LAST UPDATED  14/03/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

Click any image to view a larger version

Difficulty level :   Moderate

The following code assumes that:

 

•    the records in tblData are being updated from tblImportNoPK

•    both tables have the same fields apart from there being no autonumber PK field in the import table.

•    tblImport contains ALL records that should be imported to tblData

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

 

     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 pointless!

 

 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));

 

 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));

 

  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));

10. Combined Upend (AKA Upsert) / Delete Old

 

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

SUMMARY

 

     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

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

 

    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

 

   

    UPDATED: 14/03/2019

    Fixed error populating data tables in the previous version (5.2) of the example database

DO NOT USE THIS QUERY

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

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

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

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

SyncData10 SyncData11 SyncData12

qryUpdateNoPK

SyncData13

qryDeleteOldNoPK

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)    

SyncData14

qryAppendNewNoPK

 

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

 

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

 

     First of all, UPDATE existing records as in method 5

SyncData12

 

   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));

qryUpdateNoPK

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

SyncData15

qryMakeTableUnmatchedData

 

  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

SyncData16

qryDeleteUnmatched

 

  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  

SyncData14

qryAppendNewNoPK

 

 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));

 

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

 

    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)

 

 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

SyncData17

qryDeleteNotExists

 

8.  Delete All / Append All

 

     This is identical to method 3 in the first part of this article.

    First DELETE ALL records

SyncData6

qryDeleteAll

 

 DELETE tblData.*  FROM tblData;

 

Now APPEND all records from the import table

SyncData18

qryAppendAllNoPK

 

 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

 

     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 

 

CREATE TABLE tblData (ID AUTOINCREMENT NOT NULL PRIMARY KEY,

   StartDate DATETIME, EndDate DATETIME, NCheck INT);

qryCreateDataTable

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

SyncData18

qryAppendAllNoPK

 

 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

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