File Sharing
By Richard Rost
4 years ago
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.
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
Links
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
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
Subscribe to File Sharing
Get notifications when this page is updated
Intro
In this video, we will talk about the risks and technical challenges of using file sharing services like OneDrive, Google Drive, and Dropbox with your Microsoft Access database. You'll learn why running an Access database directly from a shared folder can lead to data loss and corruption, how file syncing works with Access files, and what problems can occur if multiple users open the database at the same time. I'll also explain safe ways to move Access database files between machines and discuss recommendations from Microsoft and other Access experts.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about using your Microsoft Access database with file sharing services like OneDrive, Google Drive, or Dropbox. As you can tell from the image on your screen right now, it's no bueno.
Today's question comes from Leslie in Twin Falls, Idaho, one of my platinum members. This is one of those things that comes up in my forums, and I get asked about it in emails at least once or twice a week.
Leslie says, 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?
Well, Leslie, if it works for you, great. But if you've watched any of my previous videos on sharing your Access database online, you'll know that I'm completely against it. It will work fine for you as a single user, or maybe you and one other user, if you do not use it simultaneously, but Access is simply not designed to run on a file sharing service. OneDrive does not know how to properly handle Access updates.
I'll use OneDrive for all my examples in this video, but all the file sharing services work pretty much the same way. Like I always tell all my users, OneDrive will work just fine until it doesn't. Then, your database can corrupt, you could be missing information, or it could be completely unreadable.
If you are the only user working on the database, and your database files are relatively small, and you have good internet, and you want to be able to copy the database back and forth between, say, your home and your office, that's okay. I recommend you do not run the database out of your shared folder. Copy it down to your local PC, use it there, then when you're done, copy it back up.
For a brief period of time, I actually had an office, and I wanted to work both at home and at the office without having to lug my laptop back and forth. So I kept all my files, including my Access database, in a shared Google Drive folder. When I left the office, I had a half hour drive home, and that was plenty of time for the files to sync up. But again, I was the only user, so this solution worked okay for me.
OneDrive and other file sharing services are file-based. That means when you make a change to the file and you save it, the entire file gets copied up to the server, and then those changes get disseminated back down to any other users who also have that folder synced. This works okay for small files, most Word documents, Excel workbooks, and so on, but not with big Access databases.
Here, I've got a setup with OneDrive and three users. That folder contains a Word document. Let's say it's relatively small. Most of my Word documents are one or two megabytes, not that big. When OneDrive or Google Drive syncs up, each user gets a copy of that file in their local folder. If one user makes a change to that file, OneDrive now has to copy that file up to the server. That file is now replaced on the server in the shared folder. Then it has to get copied back down to the other users, provided that no one else 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 the copies are synchronized, you end up with data collisions. OneDrive will not know what to do, and you'll get a warning that there are now two copies of the files. This behavior, of course, is catastrophic for an Access database.
If you change just one record in your database, the entire ACCDB file has to be synced up with the web server, and it doesn't matter if you've got a split database front and back end solution or it's all one database file, the whole thing has to get copied. If your database is even moderately sized, that will take a while.
Now, I've got pretty fast internet with a 10 megabit per second upload speed. That means a tiny 50 megabyte database file - and that's tiny - could take up to two 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 that file too, but she hasn't even gotten your update yet.
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, and other applications like OneDrive are not designed to work with these types of files. They will not update just what's changed. They have to update the entire ACCDB file.
When Access reads a record from disk into memory, it doesn't read the whole file, just a tiny piece of it. The same thing happens when it writes data back out. It only works with a tiny piece of that file. So OneDrive has to update the entire file every time you make even the smallest change. If you've got multiple simultaneous users working with the database, you will get conflicting copies.
Database servers like SQL Server or even SharePoint 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 one customer record that's 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, just reading, when you open a database file, there is data that Access might have to write to the database file. Sometimes even just opening and running a query - a select query, not even an update query - it might have to write temporary data to the ACCDB file to handle that query.
If you have, for example, VBA code that isn't compiled yet, it will compile when you execute it, and that's written to the file. So there is a lot going on in the background even if you're only reading data from an Access database file.
People like to argue this with me all the time, but this is just my opinion. I've only been working with Access since 1994, so about 30 years now. What do I know?
But don't take just my word for it. Here are some quotes from other Access MVPs and from the Microsoft website itself. I've included links to pages where these quotes are from, and you can see the replies of dozens of users saying, well, I set it up this way and it works for me. Yes, it will until it doesn't. Then your database corrupts, and then you complain because you didn't have a good backup.
Daniel Pineault: "Sharing an Access database in this manner is highly ill-advised."
Scott Diamond: "No, you cannot store an Access database in OneDrive. 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 the other users are blocked out, and then check it back in. If you're going to use it with other users, move it. Literally, move it from your shared drive to your desktop, work on it there. The file should relatively quickly disappear from the shared drive, and no one else will see it. Then when you're done, move it back up. In short, don't do it. The short and simple answer is, do not do that." As you can see, they're pretty emphatic about it.
"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. This means that MS Access typically will not run across a WAN."
This one's an excellent technical article. You'll find links to all these down below in the description you can click on. When you're running Access over a file sharing service, you're technically running over a WAN, a wide area network. While internet speeds have increased a lot since this article was written, they're still not fast enough for running Access.
This one comes straight from the horse's mouth. This is from Microsoft Support on their website. If you don't believe them, who will you believe? Warning: "Although you can save an Access database file to OneDrive" (and they also mention a SharePoint Document Library, which is different than using SharePoint as a server), "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." That's their polite way of saying your database is going to get horked. This recommendation applies to all types of Access files, including a single database, a split database, and all the different file formats. So yes, Microsoft themselves says, do not do it.
Finally, one more quote again from Daniel Pineault. He wrote an excellent article for Developers Hut titled 'Access Backend Location, When Online Server OneDrive Dropbox.' His final word is, "Now some people will tell you they've never run 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 simply may 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 it's considered best practices by Access MVPs." What I will also highly advise you is that if you're going to go against the advice provided above and run your database over a WAN, share 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.
What do I always say? Back up your data. Especially if you're going to try running this over OneDrive, Google Drive, or Dropbox, back up your data more than lightly. Every time you make major changes, back up your data.
If my advice and the advice of six other MVPs and Microsoft themselves still isn't enough to convince you, let me show you one problem that you'll have if you try doing this. Here, I've got a copy of my TechHelp database set up in a Google Drive sync folder. Google Drive My Drive shared database folder. As you can see, this is a relatively small database, only a little bit over a megabyte.
Here I am connected into that same folder on a different computer here in my office. I'm using a Chrome desktop so you can see both these at the same time. Same file, same exact file synced between two different Google Drive folder shares on two different machines.
Let me come back over here to my workstation. Let me open up the database. Nice small database. You've probably seen this before in a million of my videos. You open up the customer form. Let me make a change here. I'm working on this. I change this to Rick Rost, and I close this.
Meanwhile, my associate is working on the other machine. Let's call him Mr. Spock, and he opens up the database and goes to the customer form. First of all, he still sees Richard in here. Why? Because my database hasn't been saved yet. It's still open so it hasn't synced up to the cloud yet, so I don't have that update here. I still see Richard Rost or whatever other changes I made.
Let's go to a different record here. Let's change James Kirk to Jim Kirk. Now I'm done, and let's say Mr. Spock closes his database. He's done for the day.
Back over to my PC. I open up my customer form again. It's still Rick Rost. I don't see his change from James to Jim, and now I close my database. I'm going to give it a minute so Google can do its magic and sync in the background. This file is only about a meg in size, which shouldn't take that long.
Next day I come into work. I open up my database. Let's see what I got in here. There's my Rick Rost, and it's still James Kirk. That change didn't come into play. Let me close that. Let's go over to Mr. Spock's machine and open his up. Now he's got Rick Rost because mine updated last. But notice his change is completely gone. Any changes he may do, his database just got wiped.
It could go the other way too. Let's say, for example, that I open up my database and I change this guy to Dick Rost. Close it. At the same time, Mr. Spock is in here, and Mr. Spock changes, let's say, Deanna Troi to just D. Spock's still working, and while Spock's working, I close my database.
Spock's still working. He comes in here and he just changes this to J L Picard. Now he's done, so he closes his database last. Wait a few minutes, let Google sync up.
Next day I come in here, open my database up, and let's take a look. Oh, wait a minute. What happened to my change? I changed mine to Dick Rost, and I'm back to Rick. You see, Mr. Spock's changes happen last, so he overwrote my changes. That's the problem with people using this simultaneously at the same time. That's redundant, but you get it.
This is just the best case scenario. The best case scenario is you're missing updates from each other. The worst case scenario is your file gets completely corrupted. I've seen it happen, especially with larger files. This is a teeny tiny file, so Google can easily pass up and down a 1.5 meg file.
You start dealing with a moderately sized 200 or 300 megabyte file. Not only does it take a while, but there's a chance that file becomes corrupt.
I hope this video has educated you a little bit in how Access database files work and why you should not use them with file sharing services.
While it's okay to copy the file, use it locally, copy it back up. Multiple simultaneous users should not be using a file sharing service to share an Access database.
Now, what solutions are available if you do want to share your database online with multiple users? There are several different options available. I have a whole separate video you can watch just on how to share your Microsoft Access database online. You've got SharePoint, you've got SQL Server, there's Remote Desktop, and my new personal favorite, Access Database Cloud.
This video and the Patreon website will explain everything in more detail. I'll put a link to this down below that you can click on if you want more information.
While you can share a database file as long as you only use it one at a time, sometimes it's difficult to know when someone's using the database or not. You can look for the LACCDB file, the lock file. However, if everyone's not an Access expert, then they might not know to do that.
In the extended cut for the members, I will show you how to build a little mini checkout database. This is a database you run before you open your main database. It will check to see if the database is in use. If it is, it says, sorry, database is in use, can't use it right now. If it's available, it will tell you, okay, you can use it. Then when you click the button to open the database, it will mark it in use so no other users can use it while you're working with it. When you're done, you simply check it back out again. That's going to be covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. There are lots now, like 250 of them, so there are tons and tons of videos for you to watch. Gold members get access to download these files and my code vault.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
Don't worry. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.
Quiz
Q1. What is the main reason you should avoid running a Microsoft Access database directly from a file sharing service like OneDrive, Google Drive, or Dropbox?
A. File sharing services are too expensive for Access databases
B. File sharing services do not properly handle Access file updates, which can cause corruption
C. Microsoft Access does not work on the internet at all
D. Access databases cannot be copied to cloud storage
Q2. What is the recommended way to use an Access database if you want to move it between two locations using a file sharing service?
A. Open and edit the file directly in the shared folder with multiple users
B. Keep the database permanently in the cloud and edit from anywhere
C. Copy the file down to your local computer, use it there, then upload it back when done
D. Use Google Drive's online editing tools for the ACCDB file
Q3. If multiple users make changes to the same Access database file on a file sharing service at the same time, what is the likely outcome?
A. The database will synchronize perfectly and merge changes
B. Each user will see only their changes and data will not be lost
C. There will be data collisions, missing information, and potential corruption
D. The file sharing service locks the file to prevent edits
Q4. Why do file sharing services like OneDrive or Dropbox struggle with Access databases compared to smaller files like Word documents?
A. Word documents are always smaller than Access files
B. Access databases use a dense binary format requiring full file syncs on every change
C. File sharing services only work with images and videos, not databases
D. Word documents prevent multiple edits automatically
Q5. What is the difference between how Access databases and database servers like SQL Server transmit data over a network?
A. Both transmit the entire database file for every change
B. Access transmits just the records being edited, while server databases transmit the whole file
C. Server databases transmit just the records needing changes, while Access needs to transmit the whole file
D. No difference, both work exactly the same
Q6. When you open an Access database and only run SELECT (read-only) queries, what might still happen in the background?
A. Nothing is ever written to the database file
B. Temporary data or compiled code may be written to the file
C. The database prevents any changes at all
D. The file sharing service will block the database
Q7. According to Microsoft and Access MVPs, what is the best practice if you must share an Access database file using a file sharing service?
A. Allow all users to open the file at once for faster teamwork
B. Only work on the file sequentially after copying it locally, and use robust backups
C. Edit it directly in the cloud and rely on the service for backups
D. Use the file sharing service's version history to fix any issues
Q8. If two users update the same Access database via a file sharing service at the same time, what will most likely happen?
A. Their changes will be merged automatically
B. Both users will see all updates in real time
C. The last user to close the database will overwrite the previous user's changes
D. Access creates a new copy for each user so nothing is lost
Q9. What happens with larger Access database files (e.g., 200-300 MB) on file sharing services?
A. They sync faster than small files
B. There is a high risk of file corruption and slow syncing
C. File sharing services optimize the file and prevent corruption
D. Changes update instantly for all users
Q10. If you want to truly share a Microsoft Access database online with simultaneous multi-user access, what are some proper solutions recommended in the video?
A. Use Dropbox with simultaneous editing
B. Use Access Database Cloud, SQL Server, SharePoint, or Remote Desktop
C. Just increase your internet speed
D. Use Google Drive because it is free
Q11. What makes it difficult for non-expert users to avoid simultaneous access to an Access database in a shared folder?
A. They always use the LACCDB file to check database usage
B. They may not know to look for the Access lock file (.LACCDB)
C. The file sharing service always warns them
D. Access prevents simultaneous access by default
Q12. What tool did the video mention for safely handling sequential single-user access to a shared Access database?
A. An Excel spreadsheet tracker
B. A mini "checkout" database that marks when the file is in use
C. Google Drive's built-in lock feature
D. SQL Server's backup utility
Q13. What is the golden rule repeated throughout the video regarding Access database management?
A. Never backup your data because file sharing services do
B. Always backup your data, especially when using file sharing services
C. Only backup if you see corruption
D. No backup is needed for small files
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone tackles a topic that comes up frequently in questions and forum posts – whether or not you can safely use a Microsoft Access database with file sharing platforms such as OneDrive, Google Drive, or Dropbox. I understand the temptation to store your database in one of these cloud folders, especially if you want to access it from multiple locations or share it with a colleague. I frequently get asked about this, sometimes multiple times a week.
In today's example, the question comes from Leslie, who mentions that she's been using Google Drive to manage an Access database with her secretary, and hasn't experienced any significant problems. Her question is why I discourage this practice so strongly.
First, I want to acknowledge that it can work for some people, especially if you're the only one editing the database, or if two people are using it but not at the same time. In these very limited situations, such as when simply transferring the file between home and office, copying it back and forth through OneDrive or Google Drive is usually okay. My recommendation is to always copy it to your local computer before use, work on it there, and when finished, upload it back to your cloud folder. Do not run the database directly from your shared folder.
I've been in this situation myself. When I had both an office and a home setup, I wanted to synchronize my Access files back and forth. As the sole user, having my files in a shared Google Drive folder seemed convenient, and syncing overnight during my commute was not a problem for me. Again, this only worked because I was the only one using the file.
The fundamental issue is that OneDrive, Google Drive, Dropbox, and other file sharing platforms are all file-based systems. When you modify a file, the entire file gets uploaded to the cloud, and then distributed back down to anyone else sharing the folder. For small, simple documents like Word files or Excel workbooks, this approach usually works without much trouble – especially since most users aren't trying to edit them simultaneously.
When you add simultaneous access into the mix, things start to break down. If two people try to edit the same database file at once, the service generally creates conflicting copies. OneDrive for example may alert you to duplicate versions that need resolution. This situation might be merely annoying with a Word file, but with an Access database, it is potentially disastrous. Even minor changes in Access require syncing the whole file, no matter how small the modification. It doesn't matter if your Access database is split into front-end and back-end, the same rules still apply: every change risks trying to sync the entire backend database.
This can be particularly problematic if your file size is more than trivial. For instance, even with a relatively quick internet connection, uploading a 50 megabyte Access file could take minutes. If two users are changing data while the file is being synced, one user's changes might get completely overwritten, possibly leading to lost work or corrupted data.
Access uses a compact binary file format, and these cloud services are not built to work with files like this. They cannot update just the portions of the file that have changed; instead, the entire ACCDB file is sent back and forth, regardless of how small your actual edits were. Access itself only loads and saves the portions of the file that are needed, but file sharing services always copy the entire archive.
Another often overlooked risk is that Access sometimes writes data to the database even when you're not making obvious changes. Just opening and running a query might prompt Access to temporarily write to the database. If your VBA code compiles for the first time, that also triggers changes. As a result, any time someone even opens the Access file, it could modify the file in ways that require uploading the whole database again to the cloud, prompting more sync conflicts.
I hear often: "I've never had a problem doing it this way, so why not?" That may be true for months, or even longer, especially under light usage. But when things go wrong, the result is often catastrophic – database corruption or total data loss.
It's not just my own experience that leads me to advise against using Access with cloud file syncing. Access MVPs and Microsoft itself have published clear warnings. For instance, Daniel Pineault states unequivocally that sharing an Access database this way is highly ill-advised. Scott Diamond is even more blunt, saying you should never try to use Access in a shared cloud folder except by moving the file locally, editing it, and then putting it back – and making sure no one else has the file open at the same time.
Microsoft's own support documentation also cautions against opening an Access database from OneDrive or a SharePoint Document Library, warning that this can result in multiple, conflicting copies of the same database, along with other "unexpected behaviors." In other words, even Microsoft says you should avoid this practice.
If all these warnings are not enough, let me illustrate with a practical example. Suppose you have an Access database stored in a Google Drive sync folder and two different computers connected to this folder. If one person edits a customer record and saves the change, and the second person, on another computer, opens the same database without having synced the latest file, they won't see the new data. If both make changes and then close their copies, the first person's work may be entirely lost once Google Drive completes its sync. At best, users will have missing or outdated information. At worst, the file gets so out of sync or so corrupted that it becomes completely unusable.
This is even more of a concern as database files get larger. Small one-megabyte files might sync quickly, but as the database grows, transfer times increase and risks of corruption and sync failures rise dramatically.
So in summary, if you're working alone and are careful to always upload and download the latest version, using a cloud file sharing service is possible for Access. As soon as you introduce more users or simultaneous access, synchronization conflicts or data loss become almost inevitable.
If you need to share Access with multiple users, there are safer and more reliable ways to do it. There are several options, such as using SharePoint, SQL Server, Remote Desktop solutions, or dedicated Access database hosting services like Access Database Cloud. I have separate, detailed tutorials on all of these approaches, and I strongly recommend learning about them if you need multi-user remote access to your Access application.
You might be thinking you can share access to a local file sequentially – so that only one person edits it at a time. This is sometimes feasible, but it is often hard to coordinate among users. While you can check for the Access lock file (LACCDB) to see if anyone else is actively using the database, this method is error-prone if everyone is not familiar with how Access works.
Also, in today's Extended Cut, I will show you how to create a small database that acts as a checkout system. This tool will help you determine if the main database is currently in use before launching it, and will let you lock out other users while you are actively using the file. When you finish, the database becomes available again for someone else. This method will help reduce the chances of simultaneous access and conflicting edits, which is essential if you must share your database via a service like OneDrive or Google Drive.
Members at the Silver level and up get access to all of my extended cut videos, where you can learn techniques like these in more detail, along with additional tutorials and resources. Gold members can download all the sample databases and get access to even more resources, including my code vault and monthly expert classes. Platinum members get all previous perks plus access to all full beginner and developer classes, priority support, and more.
No matter your membership status, I will continue to provide free TechHelp videos so everyone can keep learning.
If you would like a complete video tutorial that walks you through everything discussed here step-by-step, you can find it on my website at the link below. Live long and prosper, my friends.
Topic List
Problems with using Access databases on OneDrive, Google Drive, Dropbox
Risks of simultaneous multi-user access with file sharing services
How file sharing services synchronize files and why this is problematic for databases
Explanation of file-based vs. record-based synchronization
Why Access reads and writes create syncing conflicts
Potential data corruption and data loss from file collisions
Microsoft Access file structure and proprietary binary format
Real-world example showing lost updates between two users
Dangers of missing or overwritten data changes during sync
Microsoft and Access MVP advice against using file sharing services
Proper method: working on a local copy, then uploading
Why even read-only usage can trigger file writes
Backup recommendations for Access databases on cloud sync
Alternative online sharing solutions: SharePoint, SQL Server, Remote Desktop, Access Database Cloud
Why WAN (wide area network) hosting is not reliable for Access
Recommended approach if sharing a single user database with file sync
How to detect if a database is in use using LACCDB files
Limitations of file locking for preventing simultaneous access
|
