Synchronising two tables in seperate Access databases

[Victoria Bolton]

I am writing an application that is going to be sent to my clients clients.

My client has asked that each client have their own seperate Access
database. The structure at the moment is that he has a folder for each
client on his server, he wants the MDB to be in each clients folder.

There are a few tables that are common to every single client. Eg a
manufacturers table, it holds a unique id and the name. This table needs to
be global, ie if client ABC updates it with a new manufacturer then client
XYZ should be able to see that.

My answer to this initially was to create a 'Common' access database that is
in the root of the client folders but I'm hitting a few snags when working
with two distinct databases

So what I want to do is keep local copies of the common tables and write a
routine that sychronises the local and global copy of the common tables at
each startup and shutdown.

What is the quickest and easiest way to perform this syncronisation bearing
in mind that it needs to synch both ways?

Thanks

Victoria

 

[WQ]

I am assuming your question is regarding how to sync up the structure of the
databases, not the data itself.

For a requirement like yours, anything other than Access (like SQL Server
Oracle) would be ideal, where DDL (Data Dictinery) maintenance is done using
scripts. However, that may not be the option to you. Access does accept
DDL scripts execution using ADO.

In other words the following construct is valid:
conn.execute "create table test (col1 long)" Where conn is the connection
object.

To sense the changes in the structure of one database, again ADO provides
method to read column collections, which can easily be compared against the
2nd database for any differences.

W.

 

[Victoria Bolton]

I am assuming your question is regarding how to sync up the structure of
the
databases, not the data itself.

Umm, no. I want to synch the data. The structure of the table will never
change, it's just a lookup table with an id and a name. If someone adds a
new manufacturer whilst looking at ABC's data I want that manufacturer to be
available when they log into XYZ's data.

I'm basically looking for a better way than getting each row, checking if
its in the other table and if not inserting it. It might be that simple but
I'm not sure.

Victoria