Access 2013 split database with backend on One Drive

I have been able to copy a front end to a an additional user computer and successfully link and refresh the files, however, even though both computers Access programs are set to share, the 2nd computer won't allow any editing of the data.  Read write permissions have been set on the shares and both computers are E3 2013 setups.  The 2nd computer reports synced data-how can I get it to allow read/write. The forms to add data won't open.    
 

Question Info


Last updated November 12, 2019 Views 14,128 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

You cannot use One Drive like that.  Is not a Server with which you store the backend and connect to it.

 

You might be able to use Remote Desktop to connect to the other PC and access the database that way OR you could get and Office 365 account in which case you get *Sharepoint* in so much as you can them *move* your database there.  However, since no VBA, this might cause recoding.

Gina Whipp
Microsoft MVP (Access 2010-2015)
https://www.access-diva.com/tips.html

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks for your response--I wasn't clear-- I do have Office 365--that was my reference to the E3 setup=Enterprise 3 on Office 365-- I have been able to view the data on computer 2 but unable to write to it--weirdly I had computer  2 open in and my first computer was locked out of the record until Computer 2 closed that table--It acted like computer 2 had write privileges even though it won't allow an entry from computer 2. I tried having the backend on my SP team site but seemed to get a better connection on my one Drive, despite the limit I mentioned. Both are 64 bit machines with W7 but with the recommended 32 bit Office install and both are current and updated.
Bill

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I moved my backend to my SharePoint Team Site and computer 2 was able to connect, but now I am unable to connect computer 3 & 4 which reside on another network at office #2.  Seems weird because I can see the be file but it says it has 0 bites (and not the correct last modified date)  while it shows the correct size on computer 1 & 2 when connected through network 1. 

I'm uncertain whether the one drive updates are causing some of this since they are very confusing with each of my machines updating recently--any thoughts as to why my very similarly set up network 2 is not able to connect correctly to the backend file would be very welcome.

Thanks

Bill

Bill

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Gina Whipp
Microsoft MVP (Access 2010-2015)
https://www.access-diva.com/tips.html

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

With Office 365 installed, you shoul get a One Dirve folder. When you split your DB, choose the One Drive folder as your Backend location. At this poin, you can share your DB with any user on that computer. Make sure you set up the shared option for Read and Write. To share in another computer, you must have your One Drive folder in that computer too. Then, follow the same steps as you did with the first computer. Remember, you can only share your DB if your One Drive folder is in that computer. I have Office 365 home edition and I successfuly shared my DB in five computers. With many users.

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

http://answers.microsoft.com/en-us/office/forum/office_2013_release-access/migrating-an-access-database-to-the-web/8920f7e8-9f48-4328-b89f-817ea1684e88?page=2

How about migrating to web solution for multiple users?
also refer to the post above.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Listen to Gina on this one,  although there are way to twist Access' arm to work over the net/OneDrive, but this is NOT a good idea.  Access should not be run over a WAN (this includes OneDrive amongst others).  Sharing an Access db in this manner over a WAN will lead to corruption.

Instead, you need to use the appropriate technology for sharing with people remotely, such as:

  • Create an Access Web App (AWA - accessible through the internet and a web browser)
  • Use Terminal Services, CITRIX, Remote Desktop

You may also wish to read my brief article on the subject which can be found at: http://www.devhut.net/2012/08/14/ms-access-wans-wireless-networks-and-the-internet/

--
Daniel Pineault
Microsoft MVP 2010-2019

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

While an Access database file can be shared over OneDrive, it's very important that only one person have it open at a time to prevent risk of corruption and data loss.

Whenever you open a database file in access, a separate small lock file is created with the same name as the database, but with a different file extension.  That lets other copies of Access know the file is in use elsewhere and whether it can be shared.  If you open the database in "exclusive" mode, the lock file will tell the other copies of Access that the file is being held for exclusive access and they won't be able to open it until the database is closed on the other PC and the lock file is deleted.  It's important that every copy of Access open the file in exclusive mode when using OneDrive to prevent simultaneous use and risking file corruption or data loss.

The reasons why you can't use OneDrive for simultaneous file access is a bit more technical, but I'll do my best to make it understandable...

When on a local office LAN, a server or one of the workstations can expose a file share to the network that lets other users on different machines see and interact with files on the server's hard drive directly.  When multiple users open an Access database file over one of these local file shares, they are all interacting with the same copy of the file.  Access doesn't even need to copy the entire file over the network as it can reach in and only grab the pieces of the file it needs at that moment.  This is because local windows file shares are based on what is called Block Storage where a file is broken up into many different blocks that can all be accessed independently.  Access can also modify parts of the file in real-time so other users who read that block see the changes right away.

In contrast, OneDrive is based on what is called Object Storage which typically only deals in whole files.  It works a lot like dropbox where files on the server are all copied down to your local PC's hard drive and a program running in the background keeps your local copy synced.  When you interact with those files, you're only interacting with your local copy.  If you make any change to the file, the entire file needs to get uploaded to the server so that it can get re-downloaded and synced with the other PCs that share the OneDrive.  This is why you can still use files in your OneDrive while you are offline or on a slow connection.

So while it's possible to allow multiple users to open the same Access file on a one drive at the same time, things can go wrong very quickly if two people make changes in the file at the same time, or one person makes a change after someone else before the new copy of the file has synced.  To be safe, this scenario should be avoided entirely.  If multiple people outside of a single LAN need to access the same database simultaneously, move to SharePoint where only the web server can modify the main file and the users simply interact with web forms.

22 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Cory for the detailed reply. Makes sense as I have had corrupt files like that in the past even in a LAN environment with a few users! I am in the initial process of moving my Access 365 Tables to Sharepoint Lists. But as a simple test, I uploaded (Right Click table export Sharepoint List) and converted a small table into SP list called Suppliers. I was able to access the table within Access 365 from Computer 1 but not Computer 2. I can read/write within a browser of both either computer even at same time. Here is weird part: I bought a second subscription of Sharepoint Online (P1) and using a new "Owner" user, I was able to connect within Access 365 from Computer 2. Now from computer 1, it just hangs even if I just want to open the Suppliers table in the shared area.

I thought that would be a big selling point of SP: that we can put author-able tables from an existing Access desktop app, which has a lot of forms/vba code, into sharepoint. I opened a ticket with Office 365 Support and she pointed to a link that says maybe Access "list" cannot be co-authored:   https://support.office.com/en-gb/article/Document-collaboration-and-co-authoring-ee1509b4-1f6e-401e-b04a-782d26f564a4?ui=en-US&rs=en-GB&ad=GB

I think it seems silly that we cannot co-author in real time a Sharepoint List in a shared area with all of the authorizations. What good is it when only one computer can do so? I realize you would be able to do so if you created an Access Web app from scratch but we have about 10 years of Access front end development with VBA, forms, etc. I realize not all VBA commands might work, but as long as we can see the Supplier table, shouldn't we still embed that table in a form and view data on it programmatically? Any guidance would be appreciated. Thanks!


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Why not go the route of a Hybrid Web App?

What about the solutions proposed in http://www.devhut.net/2016/09/24/access-back-end-location-wan-online-server-onedrive-dropbox/ CITRIX, RDP, ...?

--
Daniel Pineault
Microsoft MVP 2010-2019

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.