Sync databases ? Access with SQL server ?

[Guest]

Hi Guys
I'm writing an application which will operate in the following way
I have a database with ~12 tables which resides on an SQL server
Each end user will have an Access *.mdb file on his CE which will function as a "local copy" of the Server's database
The SQL server database will be updated periodically, while end users will be able click the "Database Sync" button in their local application to sync their copy of the database to the server's database and recieve any updates made to the server's database while considering the Server as the up-to-date copy (allways)
I'm looking for the most efficient way to do that
My first (and premitive) thought was to just load a datatable for each table from the SQL server and issue an oledbadapter.update on the local database with the datatable retrieved from the server. doing that for each table requires me to define and use many dataadapters/oledbCommands and seems "stupid" to me

Any thoughts of how can I do it more professionally and efficiently

Thanks !
Ori

 

[Z D]

Put a date/time stamp column on every table. You can then get the latest,
most upto date version of everything based on time stamps. Note: Assumption
is that all devices have synched time.

-ZD

Hi Guys,
I'm writing an application which will operate in the following way:
I have a database with ~12 tables which resides on an SQL server.
Each end user will have an Access *.mdb file on his CE which will function

as a "local copy" of the Server's database.

The SQL server database will be updated periodically, while end users will

be able click the "Database Sync" button in their local application to sync
their copy of the database to the server's database and recieve any updates
made to the server's database while considering the Server as the up-to-date
copy (allways).

I'm looking for the most efficient way to do that.
My first (and premitive) thought was to just load a datatable for each

table from the SQL server and issue an oledbadapter.update on the local
database with the datatable retrieved from the server. doing that for each
table requires me to define and use many dataadapters/oledbCommands and
seems "stupid" to me.

 

[Guest]

Thanks ZD, I will make sure of that
But as for architecture, is that the best way to handle the sync between two databases ? or is there a method that can make things easier (someting that will load a table from both databases, compare and update ?)

 

[Z D]

- I would try getting the client device to track all changes they make
locally.
-Then you can write a dynamic query that will check the date/time stamps on
the mobile device vs the central server and if the mobile date is later than
the central server it will do an update. If the row doesnt exist on the
central server then do an insert.

I think it can get trickey due to complicated FK/PKrelationships and data
integrity. You would have to come up with a fairly advance mechanism,
wouldn't be too simple if you wanted to include all cases.

Sybase has a product that automates this whole process. I tested it once
about 8 months ago, seemed to work pretty well. Maybe consider checking it
out. It seemed pretty robust. It was made specifically to synch
mobile/remote/disconnected databases with a central one... perfect for your
situaiton.

-ZD

Thanks ZD, I will make sure of that.
But as for architecture, is that the best way to handle the sync between

two databases ? or is there a method that can make things easier (someting
that will load a table from both databases, compare and update ?)

 

[Guest]

Thanks ZD ! will check