• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5990
  • Last Modified:

Synchronize Access database

I have a Access db that is used by multiple users, some of the users have laptops and make modifications to the db when they are on the road.
What is the best way to synchronize the database, when there is a chance that one record was modified at the source and also on the mobile laptop database?

How is synchronization handled in Access, is it beyond its scope, without a sqlserver?

Or for partial credit, what is the best method of designing the system for mobile users that need to synchronize?
0
ongbak
Asked:
ongbak
  • 3
  • 3
2 Solutions
 
shanesuebsahakarnCommented:
You can use replication in this case, if the users are disconnected from your desktop. Have a read of this:
http://support.microsoft.com/kb/q282977/
0
 
nico5038Commented:
You can use replication, but when you read the FAQ's you can already guess what trouble you can run into.

Personally I would prefer to handle this myself as it will give me full control.
In general only limited data will be editable when "disconnected" and when recording UserID and DateTime on every row that can be added/deleted/updated, then the data for problem resolving of conflicts is available.
Also by recording the UserID in a table like "tblOrder" wil allow to avoid conflicts when importing them from multiple users.

Nic;o)
0
 
shanesuebsahakarnCommented:
I take a slightly different view in that replication can work very well in situations like this, since Access already provides the tools to handle conflicts. If you did it manually, you'd also have to record that fact that a record has been changed since the last update, in case it has been changed in two different copies, so that you know to flag it as an update conflict. In general, if it was avoidable, I wouldn't bother with replication but this sounds like an ideal situation.

Replication does have some problems if you are relying on autonumbers as IDs (which you shouldn't be!) or situations when you have to rely on the latest copy of data to generate, for example, sequential order numbers, but any kind of off-line database will have that problem. Certain Access functions will also not work on replicas, such as design changes (or code that relies on changing persistent properties), so you may have some issues if you use that kind of coding.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nico5038Commented:
I know I'm a control freak, but searching in this topic area for "replication error" gives 475 questions...

Nic;o)
0
 
ongbakAuthor Commented:
Thanks for the quick responses.  
Does replication just make a copy of the database and place limitations to the db?
What are the steps?
Is there a wizard that takes you through the conflicting modified records that need to be addressed?
 
0
 
nico5038Commented:
I guess the PAQ link from Shane will provide all information on that :-)
Tools/Replication/Create.. can be used, but I would first make a backup as the database tables will be changed !

Nic;o)
0
 
shanesuebsahakarnCommented:
Yes, there is a conflict resolution screen - have a read of this, it should tell you everything you need to know:
http://www.microsoft.com/accessdev/articles/bapp97/toc.htm#Chapter20
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now