Synching two tables in seperate Access databases- Repost

[Victoria Bolton]

I posted this a while back but the only reply I got misunderstood my
problem.

I need to sync the data in these two tables, not the structure. The
structure is just two columns. Its a simple look up table.

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.

Regards

Victoria

**Original post:**
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

 

[Sahil Malik]

Victoria,

I have written this synch process for a client. We had both SQL Server and
Access databases, so we had to work with the lowest common denominator of
Access. To tell you the truth - the best way I could come up with was
basically to extract the changed rows based upon the last modified date.
Extract those to datasets, zip them up, exchange across a remoting
connection (so the SynchClass.SynchingWith was type SynchClass .. that way
both sides remained synchronized in their actions), these streams would then
be compressed, swapped, uncompressed and applied as last in wins. (barring
special rules of course).

Obviously the above was complicated - so I ended up calling it the STINK
process instead of the SYNCH process.

My longterm solution which I ended up implementing was to get rid of Access,
replace it with MSDE, which now gives you the same file based convinience as
Access does, but with Sql Server power - and then used background sql server
specific process to do the synch.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik