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.
Best Answer
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.