Two people work on the same MS Access database.
They also use this as a basis for generating serial letters through Word.
Both have the Seafile client on their computers and use the same path to the database. There’s a copy of the database on each computer, plus they sync it with the client to the Seafile server.
What do you think about this setup? Is it safe? Is there a chance of database corruption? If so, what could be done in order to mitigate this?
Bonus question: How could they convert this into an open source solution that runs under Linux? Currently they’re on Windows.
That’s not safe if the database can be added to. Consider what happens when data is added to this database. The user enters data and clicks save, the program writes the data to the file, and then tells the user the save is done. Within a few seconds the seafile client figures out that the file has changed, it figures out what parts have changed and sends those parts to the server. Then the server tells the other clients about the change, and they download the changes and update their copy of the file. Eventually they are both in sync, but it can take 10s of seconds (if both are online, hours or days if one one client is disconnected from the server for some reason).
So what happens if User A adds data to the database, and then User B adds data before User A’s data gets synced to them? You end up with 2 copies of the database. User B’s client sees changes happened both locally and remotely, and it doesn’t want to lose data by having one overwrite the other, so one copy gets renamed with extra text in the name identifying it as a conflict file. See File conflicts - Seafile User Manual . Then it is up to the users to manually merge the data to return to a single copy.
Access might have a mechanism for managing multiple simultaneous access to the file. For example, user A’s machine might create a lock file to signal to everyone else that it is about to add data to the file, and so they aren’t allowed to. This doesn’t work for “eventually in sync” systems because the lock file isn’t instantaneously synced to the other machine. What you might need to do is instead create a shared network drive where the database file lives, and the user’s access it from there. This way changes are immediately available to the other users because they are all working on the same copy of the file. You could still have the seafile agent on that server syncing copies of the database to the seafile server for backups if you want, but edits need to always come in from the share to ensure there is never a conflict.
And as for converting to use this under linux that probably depends a lot on how this access database is structured and used. The only one I have personally been involved in moving was somewhat complicated with custom UI stuff for searching and adding data. The customer paid someone to basically rebuild it with a web interface in front of a real SQL database server. My job was just to get the server running, so I don’t know how hard that was to do. I do know the users liked the new version. It was faster and they took the opportunity to fix some bugs in the database while doing the conversion.
1 Like
Thank you so much for taking time to answer this in such a profound and instructive way!