The question of using an Access Backend on a file-sync service such Dropbox or OneDrive has been coming up more and more often both in the communities and through client calls. This isn’t much of a surprise, given the ease in which just about any type of file can be shared, and the relative complexities of making data available across locations makes it ever so much more appealing. It certainly sounds easy: plop the BE onto the shared folder, hook up your FE and you’re good to go, right?
Unfortunately, the general consensus among the experts is no, that’s not good to go. In fact, we call it a flat out bad idea.
While technically possible, there’s significant reasons why this should not be done. With an understanding technical implementations of Dropbox and OneDrive and a look at the history of Access backends, it becomes quite clear that while it seems like an easy-out, this isn’t a recommended practice at all.
First, let’s see how Dropbox works (we’ll disregard OneDrive for the duration of the article, being that the implementation basics are pretty much the same). Let’s say we have two computers that share files via Dropbox: each computer has a designated folder in which all contained files are kept the same between the two computers. Add a file on Comp#1, it shows up in Comp#2’s Dropbox, etc.
Of course, in order for this to work, there’s the Dropbox service itself, which is responsible for monitoring the folders on both machines, picking up on added, deleted and modified files, keeping track of what belongs where, and finally, performing the transfer operations that ultimately keep the two or more computers in sync.
As you may have noticed, we as consumers don’t have access to the details of this service. In fact, we have very, very little options with Dropbox (almost none, for what we developers usually like), and certainly none that fall under such an umbrella as “how often to sync, how much time should a file be updated before it starts to sync, be sure not to sync this file while I have it open, please”. For general use cases, these types of settings that are governed by Dropbox without our input tend to be perfectly fine. The problems with using an Access backend in such a scenario can be brought to light by asking a couple of basic, age-old questions concerning Access BE management:
- Should I try to copy the BE while it’s open by another user? No.
- Should I subject my Access BE to WAN network transfer? No.
These are two rather fundamental practices that really must be followed in order to ensure the integrity of the backend, and incidentally, these are the two exact things that Dropbox is going to do. We have two very significant, long-time “avoid me!” cases that Dropbox must employ for its service, and we have absolutely no control over details such as whether it might try to copy chunks of the BE to its server while we’re trying to write data.
That sounds like a recipe for corruption to me.
Furthermore, let’s consider what might happen in single-use vs. multi-use cases.
For single use, I’m inclined to say that you might be able to get away with it, provided you’re willing to run the risks as outlined above. Personally, I’m a big fan of risk management (it keeps me alive while I’m flying, and tends to make my development career less stressful), and as such I wouldn’t recommend it even in a single-use scenario.
How about multiple users connecting at once? What can we expect there? Dropbox has no concept of merging the data within the file… it’s not a database service at all, let alone one with replication/sync features, so probably what we could expect is that it would take the file that it determines to be the latest modified one and attempt to sync it to the other computer (I believe the typical case there is an alert of some sort saying that the file has been changed by another user).
While I did test the single-use case stuff, I didn’t even bother trying to test a concurrent connection to a shared BE over Dropbox. While technically possible for single use, I don’t see any way that it would work for concurrent connections.
In conclusion, the idea of using Dropbox or OneDrive or the like to share an Access BE? Not the best route… maybe look into an Azure database instead, which isn’t a far stretch at all if you have any experience with SQL Server as a backend.