Data import and export

Posted on 2006-07-10
Last Modified: 2010-07-27

I have a friend who has asked me to urgently help/ code a new update process on his business database:

He wants the database to quickly and efficiently automatically update when users log on and export data when they logoff. The database is not split (and he doesn't want it to be), so each user has their own database and as they will sometimes be in their clients houses they cannot always have access to broadband.

I am thinking of  a replication farm on a VPN, but this really is outside my knowledge.

can anyone help with suggestions and code examples(if neccessary)?

Question by:JakeyCakes
  • 3
  • 3
  • 2
  • +1
LVL 65

Expert Comment

ID: 17072220
Well I recently worked on a Synchronisation thread

Now you can synchronise over the web, so what you are asking should be possible.

If this route is taken and u want to synchronise without any user intervention, u may have to store the location of the master in a config table somewhere then use that. Be it on a network or web, it should work

That thread has a MS link but sample code Ive uploaded on ee-stuff
LVL 65

Expert Comment

ID: 17072262
LVL 84
ID: 17072610
A pretty big task ... how many remote users? Access allows several different types of replication, including indirect replication (see the links rocki has provided) as well as Internet replication. One link provides a link to the TSI Synchronizer, which is very useful for indirect replication. Replication is a huge topic, and one that takes some time to implement correctly (and to master) ... also, replicating Form and Report objects is not a recommended procedure (even MS acknowledges this on the newsgroups, although their documentation states otherwise), since this almost always leads to corruption at some point.

Your friend would be in a much, much, much better position if they split the db. This way, you could simply copy the new datafile over when the remote user logged in, and then export the entire data file when they logged out, and automated routines on the in-house system could then perform the synchronization locally. And, the files transferred would be smaller, resulting in better performance.

Be aware, also, that attempting replication over a dialup connection is pretty much asking for corruption - it's not a matter of If but When it will happen. Broadband is better, of course, but can still be very slow at times. I regularly use a VPN connection over a broadband connection to admin several remote applications, and moving a 4 mb file can take anywhere from 45 seconds to 8+ minutes, with typical transfer times around 3 minutes. I'd hate to think how long it would take over a dialup connection ...
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 17089907

My friend is not keen on splitting the database as they have no routines (and because they have no IT staff, would not remember or know how to) for backing up the computer that would house the split database and because they might not be able to access the server from peoples houses...can you suggest a way of overcoming those obstacles?
LVL 84
ID: 17091422
If your friend isn't currently backing up their databases then they're pretty much playing roulette with the data <g> ... as I'm sure you're aware, trying to run a data-centric business without some form of backup plan is not a great idea, to put it mildly. There are a slew of backup products out there which are very simple to setup and run, and once they're setup you can pretty much walk away and forget them ... many will even email you to let you know when a backup has taken place, or when a problem occurs, etc etc ... I use and recommend Genie-Soft products, they've saved by bacon more than once.

I'd recommend that your friend split the database and use replication on the Backend ONLY. This is a common scenario for "travelling salesman" type offices ... you split the database into Frontend (forms, reports, queries, modules, macros) and Backend (data only) ... the Backend is then replicated, and replicants are distributed to each remote user by some method. Each user also gets their own copy of the Frontend. When the remote user fires up the application, they're actually adding data to their local replica, on their laptop, so there's no need to dialup the server. I don't know how often things change, but if you can stand 24 hour old concurrency of data, then the remote users could simply synchronize each morning, go do their sales calls (or whatever) and then re-synchronize at the end of the day ... or anytime in between.

Also, if your friend builds their directory structure the same on all machines, then there is no relinking involved ... in other words, if ALL machines store the data tables at C:\Program Files\<ProgramName>, then Access will "recognize" when a new datafile is provided and will simply use that file (assuming the files are identically named, that is).

If you can't handle data that stale, then the only thing I could think of left to do would be to either (a) move this to a web application, (b) build your own synchronization routines (been there, done that - TONS of work) or (c) rebuild the app to use a centralize, web-based database (like MySQL or MSSQL).

Author Comment

ID: 17099058

could you give me some sample code on updating/sychronising a backend database?
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 17099527
Do you mean with replication? If you use replication, you can do this pretty easily, and there is a lot of info out there about just that. YOu can automate replication, but that's a pretty advanced topic and one that isn't used very often (use the Jet Replication Objects library to do this - google on that term and you'll find plenty of info on it).

If you mean a "roll your own" solution, the coding is, as I mention earlier, very complex and time-consuming, and is HIGHLY specific to the individual database structure ... in other words there's really no "generic" code you can use, other than standard recordset and looping techniques, array or temp table management, audit tracking etc etc (and, not to be blunt, but if you aren't comfortable using those basic techniques I'd strongly advise against writing your own sync code).

Basically you loop through the remote database and gather any "new" or "changed" data (this is assuming you'll implement audit tracking on the databases - an entirely different problem<g>). Once your gather this information, you would then connect to the "master" db (I use ADO to do this, but you may want to use DAO) and then (a) check to see if data in the Master database has been changed (again, through some from of audit logging), (b) compare the data in both to see if you have any conflicts, (c) handle those conflicts, (d) write the changes to the Master database and then (d) write the changes to the remote database. All this is performed in a transaction on a single ADO Connection object, so that we can examine any errors which occur and then take appropriate action (i.e. simply continue, or abort the transaction and rollback, etc etc).

You also must determine how to handle new records and deleted records. Do you automatically allow ANY new record to be added to the database? Or do you que them so that a manager can review and approve them? What about deleted records? What happens when RemoteUser1 deletes a record, but RemoteUser2 updates that same record - which one "wins"? You can't actually delete records in the remote database (since there'd be no way to propagate those changes back to the "master" database) so you must instead initiate your own form of "state" management ... i.e. an Integer field, perhaps, that tracks the current "state" of a particular record (for example, 0=NoChange, 1=NewRecord, 2=UpdatedRecord, 3=DeletedRecord, 4=ConflictedRecord, etc etc) ... your sync code would then have to take appropriate action based on the state of the record.

Your "master" database would also need to keep track of users who may be using multiple copies of the same database, information about when each database was last synced, etc etc ... in my solution I assinged a unique value to each remote database/user combination, and used a table in the master db to store that info ... then when a replicant connected to the db, we could determine which records to manipulate (based on audit tracking data stored in each table). Thus, if a user connected from multiple machines, we could always determine which records they needed to sync.

Unless each user has their own "set" of records that NOONE else ever touches (and you don't allow multiple copies for one user) you'll also have to implement some form of conflict resolution that occurs when a record has been changed and saved in the "master" database, and then another user comes along and changes that record again ... this may not be an issue to you (in most cases I wouldn't think so, but a few spring to mind) but I don't really know your data well enough to comment on that.

And again, the weak link in the chain is going to be the connection ... updating and synching is a very time consuming operation, and is heavily data intensive ... if your connection drops, and the sync code is running (and making changes to either database, or even inspecting records in either database) corruption is all but guaranteed.

I'm out of town for the weekend, so I'll not be able to reply until Sunday/Monday ...
LVL 65

Assisted Solution

rockiroads earned 250 total points
ID: 17111516
that first post I gave a link from MS and sample code

taken from that link

here is a good link talking about replication and synchronisation from ms

Ive uploaded an example for you, have a look here 

LVL 27

Expert Comment

ID: 17469168
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Split: LSMConsulting {http:#17099527} & rockiroads {http:#17111516}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question