Hello all,
I have put in some time developing a way for my office to use our Access database while it is being replicated by Dropbox, and I thought this might (must) be useful for others out there as well.
The rationale is that MS Access is really the right database system for our extremely small business. I know people will say that for a shared database one must use SQL Server or similar, but we don't have the resources - it needs its own server, which then needs maintenance, etc. A split Access database is just right, since we're typically on the LAN and it supports multiple users in that situation, and, someone could take it on the road and do some work remotely so long as it is just one user at a time.
What I've done is written up a locking system in VBA that generates lock files whenever the database is open, which Dropbox promptly replicates to everyone else. The back-end data tables are linked in dynamically with VBA, and depending on the lock files, it will link in tables from the local copy or the already-open-copy, wherever it is on the LAN. If someone opens the database remotely, then of course there is no way to link in the tables, but in this case the lock system will prevent the second user from linking to any tables at all.
I've put a stripped-down demo version into a zip file here which anyone interested is welcome to look at and adapt to their own purposes. It's just a split database with a couple empty tables and forms, and a lot of VBA code. You have to put it into a folder that's being replicated by Dropbox between several machines, wait for it to replicate, and then open the front-end on each of the machines to see what it does. You of course won't be able to link tables over the network unless you have your folders shared. This demo expects that you have shared the entire C:\Users folder, i.e., \\[machine name]\Users\[username] needs to be accessible. You will have to modify the code if you have a different arrangement.
https://www.dropbox.com/scl/fi/zrtv8nkyr29jxogolcmbc/demo.zip?rlkey=eph7yrhd46mbdkk8x1yfa11f7&st=drh8g0jn&dl=0
It probably seems unnecessarily complicated, but I have made a real effort to keep it somewhat well-structured (at least as far as that is possible with VBA) and as streamlined as possible. If someone does make any real improvements, please share them back.
Note: I've just tried this out to make sure it works, and it looks like Access blocks macros on downloaded files. You'll have to right click on the db, hit properties, and unblock. Then when you open it, you also have to click "enable macros" or whatever. If you don't trust me, have a look at the code first. It's not a virus.
Good luck!
Nolan
