Synchronise data with external source

[isladogs]

The attached items were written some time ago in response to various questions from forum members including several posts by AWF member jaracek such as:
https://www.access-programmers.co.uk/forums/showthread.php?t=303167 and https://www.access-programmers.co.uk/forums/showthread.php?t=303677

I'm posting the information here to make it easier to find it in future
=====================================================

As with most processes in Access, there are several ways of synchronising data.
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

It will also depend on whether or not the import table has a primary key field or not.

The example application compares 9 different approaches to synchronising data.
Two PDFs explain each process in detail
a) Part 1 - Four approaches suitable for use when the import table has a primary key field e.g. another Access or SQL table
b) Part 2 - Another 5 methods are provided for use where there is no primary key in the import table. Typically this would be when importing from an Excel or CSV file.

Each method 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.
In addition, the code measures the time taken and the increase in file size for each method.

Here are the average results of both sets of sets:

For the purpose of this example, it is assumed that:
• the records in tblData are being updated from tblImport
• tblImport contains ALL records that should be imported to tblData

The latest version of this article & example app can also be found at: http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001

I also recommend an interesting approach suggested by MajP in response to another question by jaracek - see https://www.access-programmers.co.uk/forums/showpost.php?p=1613697&postcount=69
MajP's solution is intended for situations where there are many fields containing nulls in one or both tables involved in synchronisation

 

[Scott_Hall]

Thanks for your continued efforts, as always they are very illuminating.

I'm working on CSV imports, but as part of the process some of the data points have to be reformatted. Despite typecasting efforts, some values will occasionally pop in wrong.

Should the converting be done within the query (method 8 or 9) or as a supplemental update?

Curious if you have any thoughts at all on this? This may be too far out there for you to answer, but I'm wondering if you have tried something like that.

Thanks,
Scott

 

[isladogs]

Hi Scott

Firstly a reminder to report your own post to moderated areas. See sticky thread above for the reasons why. Luckily this time I happened to see your post fairly quickly

Anyway its a good question and possibly one worthy of a tutorial in its own right at some point.

Yes I regularly have to deal with that situation. For example, with my schools apps, 30 CSV files are imported each night with all fields as text. However these need further work before they can be used to synch with the main tables.

The solution I use is to link the CSV files then import the data into what I call 'buffer tables' (AKA staging tables or 'temporary tables'). I do this in an update utility called SDA Link. Next those tables are processed as necessary e.g. to change the data type from text to date or to filter out unwanted records so that the actual synchronisation can be implemented to the final data tables in another program (School Data Analyser). After that the 'buffer tables' are emptied (until the following night). This all happens automatically as a scheduled task with no user intervention

The attached Word doc is the first page from the SDALink help file and has a flow diagram that may help explain the process

Hope that helps
Any questions, I suggest you PM or email me