Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Our workflow requires two users to work on the same Access database. The problem is that users are geographically separated and can't work on same .accdb file simultaneously.

So, the only way i see to solve this problem is to periodically merge each user's DB's into one. If there is another way, please share you thoughts.

I've googled for how to do this in Access, but haven't found easy solution. I mean, this operation seems to me quite natural and i've been expecting that Access can do merging "out of the box". But what i found are advices to write special queries for each table being merged.

I'm fine with this, since my tables are simple, but i wanted to know if there's better way and how such problem should be properly solved.

share|improve this question

2 Answers

up vote 1 down vote accepted

First things first: MS Access was not designed for multi-user access. Every version of Access I've used had a disturbing habit of corrupting tables at a vastly increased frequency if there were >1 users using it.

If the two users are connected to the Internet all the time, I'd recommend shifting your table storage to SQL Server and having the users connect to that (use a VPN or some other form of security! If they're on a company LAN it's even better, you shouldn't need a VPN then). It's a fairly straightforward process to convert to SQL Server.

The users will still use the Access front end, but instead of having the tables stored inside the .accdb file and having to merge them, the Access tables are converted to linked tables to the SQL Server tables.

This is possibly a bit more up-front work, but it'll save you hassle down the road (how often do you need to merge? who's going to do the merging?). Also, if the application ever gets more widely used, you can easily build another front end (in C#, Java, ASP.NET, whatever) and connect it to your SQL Server back end.

share|improve this answer
3  
MS Access is designed for multi-user access. I have used it for many years, with a suitable number of users and the recommended set-up and only once had problems with corruption, and that was in a single-user environment, furthermore, all data was recovered. However, I agree with you that Access is not the right choice for a WAN. – Remou May 12 '12 at 15:44
3  
I agree with Remou. Everytime someone says that MS Access wasn't designed to do (some simple thing) I hear "I don't know how to do (some simple thing) properly in Access therefore the problem is Access" – JohnFx May 12 '12 at 18:53
1  
If you think implementing bidirectional replication (or merging) in Access is 'simple', more power to you (please post an answer, I'm honestly curious as to how hard it is). – Simon Righarts May 12 '12 at 21:10
1  
@Remou - Access needs to make lock files for multi-user access, so all users must have write access to a shared directory, which would require an appropriately configured WAN or VPN. – ConcernedOfTunbridgeWells May 12 '12 at 21:19
If I understand correctly Access is sufficiently different from SQL Server (despite the existence of a migration tool) that there is no particular reason to favour SS over any other RDBMS as migration target - eg postgres or whatever. – Jack Douglas♦ May 12 '12 at 21:53
show 3 more comments
up vote 1 down vote

I've built systems on Access before in the exact same scenario where some merging was required. Let me warn you upfront that this is not something you should take on lightly. Two-way synchronization in most platforms is not for the faint of heart.

I would investigate any other option possible before trying to keep two actively modified databases in synch. For example, have you considered using a cloud-based database like azure as a back-end for both users so you don't need synchronzation?
Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.