Data import and export


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

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
JakeyCakesAuthor Commented:

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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
JakeyCakesAuthor Commented:

could you give me some sample code on updating/sychronising a backend database?
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 

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.