Hi everyone, here at our company, we have several shared Access databases. At the same time, we are migrating all our files and folders to Onedrive. I have a few questions for you: What will change?
Will they still function properly?
Are there any precautions we need to take?
Thank you in advance for any suggestions you may have.
Georg B.
There are many protocols used for transferring files. Some of them work well for anything. Some, ... not so much. Access is centered around the protocol known as Server Message Block or SMB. When using SMB, you can read and write parts of a file while others read and write different parts of the same file - the database back-end file, for example. Without the ability to do partial file updates, Access only works as a single-user application. Multiple users cannot share it at all without SMB being available.
Cloud drives typically do not support this activity. They use either FTP or HTTP variant protocols that are whole-file or file-stream oriented. Cloud systems make whole-file copies of the cloud-resident files and let you "diddle" with the COPY. Then when you close the local copy the cloud drivers make a whole-file copy of the local file and send it to the cloud again to OVERLAY the previous version. The problem here is that, depending on how it is set up, it is possible under this system to have multiple independent copies out at the same time, leading to the problem of "destructive interference" - also known as "left hand not knowing what the right hand is doing." This often leads to data loss because only the last update of the file actually gets stored long-term. Pat Hartman's comments about a "reservation system" are the practical approach to prevent having too many hands in the file at the same time.
If you attempt to use Access on the cloud-resident version of the file, if you aren't lucky and careful and technically astute (probably need all three of those at once), you will instead use Access on the shadow copy - not the real file - because Windows and cloud servers hide the file from you through fancy device driver tricks. The cloud servers will not allow SMB connections because they are protecting the files by synchronizing with the fancy device drivers to know when they need to make updates. SMB doesn't play that game, or more specifically, the fancy drivers don't think "partial file update" and don't support SMB requests. And before you ask - SMB has been around when Windows was a pup. It is their standard protocol for file sharing & printer sharing. It's not like SMB is new. But cloud services ARE new and for better or worse, a lot of them will not support SMB interactions.
Bottom line: Direct use of cloud services do not work for Access databases. As Pat Hartman suggests, you need either Citrix or some other similar Remote Desktop Protocol solution. AND you STILL have to be careful there, because a badly configured Citrix or RDP will leave you with users improperly sharing the front-end file anyway.
Search this forum for "Cloud server" to find a myriad of questions and answers regarding this topic.
