Advice? Data synchronisation between multiple copies of a database

[fraser_lindsay]

Hi,

Woudl anyone be able to point in the right direction please.

Is there an easy way to automate information copying from one database into another database (both with the same structure) using Access or do I need an import tool?

I have developed a database but I'm starting to requests for remote copies or laptop copies, a shared loaction on a server isn't always possible to allow just one master copy.

So I'd like to be able to copy the data from a satellite copy to the master copy on the network but without doing it the hard way.

Is this something I can attempt myself or am I better just Googling for a utility to do this?


(I'm an amateur, using 2007)

 

[Banana]

There's three ways I can think of, but which is ultimately the correct way will depend on your specific needs & requirement.

1) You could write a bunch of append queries, perhaps using unmatched template, and update queries, then write a VBA routine to execute each them in a given sequence. Put up a button "Synchronize" to run that routine.

2) If your company is willing/has it/has the money to buy it, use Terminal Service or Citrix to provide your users remote access to Access application.

3) If you're using .MDB file, you could look into replication. Note that this is not supported in .ACCDB file, but Access 2007 can provide all capabilities you need to replicate.

I hope this helps.

 

[fraser_lindsay]

Banana,

Thanks for the pointers. I think I will investigate option 1, using append queries. Any general tips for Append queries?

Citrix might be an option, I think they do use Citrix. I'll check that out too.

Thanks again.

Fraser

 

[Rabbie]

Your choice will need to consider whether the laptop users will be adding records to their copies of the DB before updating the main copy. If so you need to consider how you will handle clashing in updates from different sources. David Fenton has some helpful advice on Replication on his website.

 

[fraser_lindsay]

Good point and thank you for the further information. I have quite a bit of reading to do now, thanks to you both for the pointers.

 

[dfenton]

It's not clear to me why in the long run Jet replication might not be your best solution. From what you've written it seems that multiple copies of the database already exist with different data. The problem there is pretty significant, since with identical structures, you'll definitely have primary key collisions and that's pretty complicated to resolve.

Coding it is really a difficult task. I've done it a bazillion times already and it's not easy.

I would suggest, though, that before going the replication route, you consider Windows Terminal Server (no reason to go with Citrix unless the added expense gets you features that are indispensible), which ships with every version of Windows Server from 2000 on (all you need to do is buy the client access licenses to make it work). If your users are going to be using your app only when connected to the network (even if remotely, via VPN), then that's the obvious way to go, and makes everything remarkably simple.

If remote users need to edit data without a network connection, then Jet replication can be an easy way to do it, particularly if the users frequently are able to connect to the main office's wired LAN. In that scenario, you can use the simplest form of Jet replication, Direct Replication, which takes about 5 lines of code to implement behind a command button.

If you don't have that scenario, though, Jet replication quickly becomes quite complex. That is, once you have to go beyond direct replication (to indirect or Internet replication), the dependencies and configuration issues multiply. It's quite doable, but not worth it if there are better alternatives.

 

[Simon_MT]

I agree with David, copying data even via update queries or entire databases creates a "rod for your own back". If clients what remote users then a Terminal Server is ideal. With data updates get written to one database if does not mattert whether or not a user is local or remote.

A step further what about there emails or company documents they may want to work on as well. Using VPN (secure) remote users could either use web mail or server access to mail exchange and be able to see all the documents they require.

TS is a good solution to users who are in and out of a office.

Simon

 

[HiTechCoach]

Hi,

Woudl anyone be able to point in the right direction please.

Is there an easy way to automate information copying from one database into another database (both with the same structure) using Access or do I need an import tool?

I have developed a database but I'm starting to requests for remote copies or laptop copies, a shared loaction on a server isn't always possible to allow just one master copy.

So I'd like to be able to copy the data from a satellite copy to the master copy on the network but without doing it the hard way.

Is this something I can attempt myself or am I better just Googling for a utility to do this?


(I'm an amateur, using 2007)

Another option that has not been mentioned is to use a SQL server and have the remote front ends connect to the SQL server via the internet.


Since you are leaning towards to writing your own replication, see:
Tables: Synchronization without Replication

 

[brickelldb]

I am having the same dilemma as the original topic for this post. My company has 2 stores which use identical copies of database.

Terminal service is not an option because if the internet goes down, the satellite store will not be able to function, as this is 'point of sale' software. Nor is anything related to live internet access.

Therefore, I'm stuck with the route of each store running their own copy and then with a stored routine having the data synchronize every so often - Because both stores MUST have access to both sets of data at all times (obviously if the internet were down for 30 minutes then they would be unsynchronized for 30 min).

I have explored the option of setting a VPN and doing append queries but wow does that get complicated as mentioned earlier in this thread with primary keys.

I am running access 2003.....would one of these "synchronization without replication" be best bet?

 

[tormodnt]

The same with me. I have a lot of user around Norway that uses my app and there is no way in h*** that is possible for them to be online every time the need using the app. (most of the use it in the cars..) this have been a real pain in the ass for over a year no, but since the app is working flawless on it's own everyone is happy but me that get a lot of manual job every week..

 

[Simon_MT]

The success of a Terminal Server deployment is finding a solid ISP with low contention. I'm currently on a service that the contention is 1:1. Domestic broadband can be up to 50:1.

I have tried copying information between sites and in the end the Terminal Server was the better solution. Downtime was minimal, Session timeouts are generous so that disruptions were handled by the resumption of the Session.

No problems with VPN or any database issues. You need to remember that the database is operating locally to the Terminal Server and it is only the screen dumps that are being transmitted remotely.

Simon

 

[Banana]

If you're using .mdb files and you need to use files without internet connectivity (or at least not having to require it to use the file), look at DFenton's replication wiki.

 

[Simon_MT]

You can use both mdb and accdb files with internet connectivity either on a Terminal Server or as the backend of a web site. You can already see with Access 2007 the precursory features on html appearing on the on each control properties so we know where the next version of access is heading - to the web.

Accdb file no longer support replication as it is likely Access become web aware.

Simon