Access File Sharing: Dropbox, OneDrive, Google, etc.

In this video, I will explain why you should not use file sharing services such as OneDrive, Google Drive, or DropBox to run your Microsoft Access databases with multiple simultaneous users. You will end up with missing data at best, and a corrupted database at worst.

Lesly from Twin Falls, Idaho (a Platinum Member) asks: In your previous videos, you say not to run Microsoft Access on any file sharing services. I've been using Google Drive to host my Access database for years without any problems. Granted, there's only my secretary and I using the database, but it's a good solution for us and we haven't run into any problems. Why are you so against it?

Members

Members will see how to build a solution that will allow one user at a time to "check out" the Microsoft Access database, much like you would check a book out of a library. This will allow you to host your database on a file sharing service, provided only one user at a time is working with it.

•  Extended Cut Video - Silver
•  Database Download - Gold

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Suggested Course

• Access Expert 21: https://599cd.com/ACX21

Links

• Access Online: https://599cd.com/Online
• SharePoint: https://599cd.com/SharePoint
• SQL Server: https://599cd.com/SQLServer
• Access Database Cloud: https://599cd.com/ADC

Addendums

I mentioned that you can look for the LACCDB file to see if someone else has the file open. This may not always work. Google Drive, for example, doesn't update that file on the server if the database is open.

Quotes from MVPs & Microsoft

I've only been working with Microsoft Access since 1994, almost 30 years now. Don't take only my word for it. Here are some quotes from other Access MVPs and from the Microsoft website itself. I've also included links to pages from where these quotes were taken, and you'll see the replies of dozens of users saying, "well, I set it up this way and it works." Yeah. It will work... until your database corrupts!

"Sharing an Access database in this manner is highly ill advised!" Daniel Pineault - Access MVP 2010. https://599cd.com/FileSharing1

"No you cannot store an Access database in One Drive. Don't even try. The only way something like that will work is if you can check out the file, work on it locally, while all other users are blocked out, then check it back in." Scott Diamond (Scottgem) - Access MVP 2007. https://599cd.com/FileSharing2

"In short: don't do it." Tom van Stiphout - Access MVP 2008. https://599cd.com/FileSharing3

"The short and simple answer is: Do. not. do. that!" Philipp Stiefel - Access MVP 2002. https://599cd.com/FileSharing4

"You can store the file on Dropbox, yes... However, there IS a risk if you all intend to work with that same relational database application sequentially." George R. Hepworth (GroverParkGeorge) - Access MVP 2007. https://599cd.com/FileSharing5

"This means that ms-access typically will not run across a WAN." Albert D. Kallal - Access MVP 2003. https://599cd.com/FileSharing6

"Warning: Although you can save an Access database file to OneDrive... we recommend that you avoid opening an Access database from these locations. The file may be downloaded locally for editing and then uploaded again once you save your changes... If more than one person opens the Access database... multiple copies of the database may get created and some unexpected behaviors may occur. This recommendation applies to all types of Access files including a single database, a split database, and the .accdb, .accdc, .accde, and .accdr file formats." Microsoft Support. https://599cd.com/FileSharing7

"Now, some people will tell you they've never ran into any problems running their Access database [with OneDrive]...  It may very well work for months without issue and then suddenly completely corrupt your database beyond repair. It may simply not work at all. I cannot tell you as each setup is different.  What I will tell you is the above information is based on years of experience and is considered 'best practices' by Access MVPs. What I will also highly advise you is that if you are going to go against the advice provided above and run your database over a WAN, shared drive, online server, etc. then please be sure to have a very robust and frequent backup system in place to be able to recover from corruption easily!" Daniel Pineault - Access MVP since 2010. https://599cd.com/FileSharing8

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Diamond Sponsors - Information on our Sponsors Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

More Info

If you've watched any of my previous videos on sharing your Access database online, you'll know that I am completely against using a file sharing service like Google Drive, OneDrive, or DropBox to run your Microsoft Access database with multiple, simultaneous users. Access is simply not designed for that. OneDrive does not know how to properly handle updates to an Access database file. (I'll use OneDrive for my examples, but all of these file sharing services work pretty much the same way).

And yes, Lesly, OneDrive will work... Until it doesn't. Then your database corrupts. It will be missing information at best, and be completely unreadable at worst. If you are the ONLY user working on the database AND it's relatively small AND you have good Internet, and you want to be able to copy the database back and forth between your home and your office, that's OK. But don't run the database out of your shared folder. Copy it to a local drive, use it, then copy it back up.

OneDrive is file based. That means when you make a change to the file and save it, the entire file gets copied up to the server, and then changes get disseminated back down to the other users' folders. This solution works OK for smaller files like Word documents or Excel workbooks, but not with Access databases.

Let's say you have a OneDrive folder shared between three users. In that folder is a Word document. Each of those users also has a copy of the file in their local folder. If one user makes a change to that file, OneDrive has to now copy that file up to the server, replace the file that's in the shared folder, and then copy that file back down to the other users. Provided nobody is also working on that file, everyone is now synchronized. 

However, if one user tries to make a change to that file, and another user also tries to make a change to the same file before all of the copies are synchronized, you will end up with data collisions. OneDrive won't know what to do, and you'll get a warning that there are now two copies of the file. 

This behavior, of course, is catastrophic for an Access database. If you change just one record in your Access database, the entire ACCDB file has to be synched up with the web server. If your database is moderately sized, that will take a while. I personally have pretty fast Internet with a 10 Mbps upload speed. That means a tiny 50 MB database file will take up to 2 minutes to upload to the server. If another user changes a record while that file is transferring, now her system is going to need to sync up the file - and she hasn't gotten your update yet. See the problem here?

Additionally, Access uses a proprietary file format to store as much information as possible in as small a space as possible. It's a dense binary file that other applications like OneDrive aren't designed to parse. So they won't just update what's changed. They have to update the entire file. When Access reads a record into memory from disk, it doesn't read the whole file. Just a piece of it. Likewise the same thing happens when it writes data back out. Only a piece is updated. OneDrive doesn't just update pieces. It has to update the whole file. You can end up with multiple conflicting copies of your database.

Database servers like SQL Server are able to transmit just the records that need to be edited over the wire. Access doesn't work like that. Plus, it's not just "that customer record" that you're changing. Many other parts of the database file may have to be updated too, including indexes. 

Even if you're only reading records, not editing or adding, when you open an Access database there is "stuff" that Access might write to the database file. Even opening a simple query might cause temporary data to be written to the ACCDB file. If you have VBA code that isn't compiled yet, that will get compiled when you execute it, and it's written to the file. So there's a lot going on in the background even if you're only reading data from an Access database.

Keywords

microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Can I share an Access database using Google Drive, Can I share an Access database using Dropbox, Can I share an Access database using OneDrive, Does Microsoft Access work with OneDrive, Does Microsoft Access work with Dropbox, Does Microsoft Access work with Google Drive, How do I use Microsoft Access in the cloud, Can you use an Access database on Google Drive, Can you use an Access database on OneDrive, Can you use an Access database on Dropbox