Synchronize Tables

[victoryhighway2]

Hello,
I have a database that has components that reside on two different servers (in two geographic locations.) I have modified my code in my Access front-end to use a "table prefix" to identify which backend should be accessed, the one in Location A or the one in location B. To clarify, I have a TBLA-Customers and a TBLB-Customers table linked in my front-end. The code will automatically determine whether it needs to access the TBLA or TBLB prefixed tables.

This arrangement works great for most cases, however, this means that location a and location b have totally separate tables.

There are two tables, an Employees and a Departments table which needs to be shared by Location A and Location B. Up to this point, all users have been using the one shared set of Employees/Departments tables. This morning turns out that server that hosts the Employees/Departments tables (as well as location A's entire database) went down. Location B's server was not affected, but the users at Location B could not access the database because Location A's server with the shared Employees and Departments tables was down.

What I'd would like to know is if there was a way that we could have two separate sets of Employees and Departments tables that could be synchronized between Location A and Location B?

Thank you for any help you could offer.

Regards,
Geoffrey

 

[LittleSmudge]

Complicated - I think.

I have a similar issues with a MySQL back end and a Client's SQL Server back end that must both contain the same data in two specific tables

During READS I rely on the MySQL database
During Writes I write to BOTH.
( Each table has a LastWrite Date/Time field )

If I ever detect a failure in the Write process to SQL server I assume that the link is down between the sites and I set a flag in the tblIni table.

If tblIni 'Disconnected' flag is set I only write to MySQL.

On Database powerup I try to connect to SQL Server.
If successful and Disconnected flag is set I then search for records with a missmatching LastWrite date/time and use the latest Date/Time to update the other table. ( This includes NEW records at either location )

I then reset the Disconnected flag if all went well.




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[victoryhighway2]

Unfortunately, that doesn't really help as I'm using just the Jet database engine and not using a SQL server.

 

[BobRodes]

Study up on replication strategies. This is exactly the situation that replication addresses.

For lots of info, use this string in google:
site:msdn.microsoft.com access replication

HTH

Bob

 

[LittleSmudge]

The approach I defined above will work just as well with two Access Back End databases. - Use ADO recordsets to read/write to them and it will work fine.

The problem with Replication is that the two databases are only ever the same just after a resynchronisation cycle. As soon as a user writes to one of the databases the two are out of sync with each other until the next resync.

If you can live with the two databases being "nearly the same" for most of the time and playing catchup on an occasional basis then Replication is the way to go.

If not, then you will have to do simultanious writes.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[victoryhighway2]

LittleSmudge,
Thanks for the information. I think that using Replication will probably be the easiest solution for me, and it should work fine as the data that I will be synchronizing is relatively static (a list of employees).

I think I'll do little more reading on replication.

Thanks,
Geoffrey