Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-14-2019, 11:09 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,625
Thanks: 102
Thanked 2,275 Times in 2,097 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Synchronise data with external source

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...d.php?t=303167 and https://www.access-programmers.co.uk...d.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/s...a-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...7&postcount=69
MajP's solution is intended for situations where there are many fields containing nulls in one or both tables involved in synchronisation
Attached Images
File Type: png SyncTestForm.PNG (44.1 KB, 75 views)
File Type: png SyncData19.PNG (25.0 KB, 30 views)
Attached Files
File Type: zip SyncDataExample_v5.3.zip (381.2 KB, 9 views)
File Type: zip Synchronise a table with external data - v5.3 - Part 1.zip (256.2 KB, 5 views)
File Type: zip Synchronise a table with external data - v5.3 - Part2.zip (314.4 KB, 5 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 03-18-2019 at 03:39 PM. Reason: Added summary results screenshot
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
Scott_Hall (Yesterday), usm01 (03-16-2019)
Old 03-20-2019, 07:30 AM   #2
Scott_Hall
Newly Registered User
 
Join Date: Mar 2017
Location: Vancouver, WA USA
Posts: 31
Thanks: 7
Thanked 1 Time in 1 Post
Scott_Hall is on a distinguished road
Re: Synchronise data with external source

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
Scott_Hall is offline   Reply With Quote
Old 03-20-2019, 08:23 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,625
Thanks: 102
Thanked 2,275 Times in 2,097 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Synchronise data with external source

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Scott_Hall (Yesterday)
Reply

Tags
synchronise data

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Problem using access query as external data source for excel pivot table frankt68 General 2 03-15-2018 05:53 AM
Filling unbound Listbox from external data source dweben Forms 4 02-15-2013 03:33 PM
Link to Microsoft Access Query as External Data Source natep26 Queries 2 10-14-2008 08:16 AM
Error when creating PivotTable from External Data Source ianward Excel 2 09-24-2008 12:42 AM
External Data Source Password help Required Kempes General 3 06-10-2005 04:52 AM




All times are GMT -8. The time now is 06:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World