Using an Access database from SharePoint Online

Question

Hi all,

I have a few questions I hope someone will be able to help me with.

I created an Access database at work. There are only a few users. We use Microsoft 365 and I'm using Windows 10.

I put it on our SharePoint Online site. We created folders on our desktops to be able to get to it, because obviously you can't access it from a browser.

I was reading today in Access' help file that it's not a good idea to put the database on SharePoint. Why is it worse than a local network?

Another question I have is, the person not using the database was able see a working copy in the SharePoint folder where the database is located. It's no longer showing a working copy. That was helpful in knowing when it was being used by someone else. What are my options for making sure 2 people won't try to access the database at the same time?

Also, I don't want to split the database. I'm still working on it and I read that splitting decreases the functionality in ways I don't want it to. I think I tried it once and something stopped working too. I have created quite a bit of SQL/VBA code, mainly for the forms.

I hope someone can help me understand all of this better.

Thank you,

Rebecca

Answer 1

If you want to protect the integrity of the data, you will not try to use the accdb from SharePoint.

Split the accdb into a Front End with forms, reports, code and queries and a Back End with only the tables.

Link the Front End to the tables in the Back End.

Put a copy of the front end on each users computer.

Put the Back End accdb in a shared folder on your network, not in SharePoint.

If you have no network, use one of the other options I outlined.

Good luck with the project.

Answer 2

"What are my options for making sure 2 people won't try to access the database at the same time?"

That's not a viable strategy for a shared relational database application. Not only is it counter-productive to prevent two or more people from working at the same, it is risky. Data can easily be lost in that scenario.

ALL production relational database applications need to be split before deployment to users. ALL of them. Once it goes into use, it has to be split to be reliable and dependable. This is especially important when you are engaged in ongoing development and have to make regular, frequent changes to the interface.

BTW, I can't imagine any case where splitting could decrease functionality. Could you be more specific on that point? What, exactly, did you read which suggested that?

The reason Access can't be reliably run from SharePoint is the differing ways SharePoint operates from the Windows file system. You cannot share an accdb from SharePoint because it can't be opened with sharing enabled in that environment. As you suggest, that means only one person at a time could use it. Highly unproductive. To use it from SharePoint, the accdb must be downloaded to a local Windows Computer, where it can be then opened and updated. All changes are in that copy. That accdb would be uploaded back to the SharePoint site. Potentially, that process would overwrite any work done by someone else who had also downloaded it to work on it during the same time. Oops, last saved wins. And that doesn't include the risk of corruption from conflicting attempts to save the accdb.

There are other, better options to consider.

First, put the back end accdb, containing only tables, in a shared folder on your network. Give each user a copy of the front end accdb, containing forms, reports, VBA and queries.

As you continue to develop it, by the way, this approach makes it much easier to simply replace the accdbs on users' computers with the updated one when you are ready to deploy the changes. The data remains safe in its own accdb, linked to the various front end accdb.

Another option is to export the tables into SharePoint lists. This works, but is not a great solution for larger relational database applications. Again, the front end accdb is distributed to the computers of all users. NO sharing of FE accdbs.

And a third option is to consider a remote-- or cloud -- back end, using SQL Azure, for example. Again, each user has their own copy of the Access FE which is linked to the tables in the SQL Azure or SQL Server database.

Answer 3

Dear Rebecca1859,

Thanks for posting in Microsoft Answers Community.

Although you can save an Access database file to OneDrive or a SharePoint document library, 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 to SharePoint. 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. For more information on deploying Access, see Deploy an Access application.

For detailed information, see Ways to share an Access desktop database (microsoft.com)

Here’s a thread where MVPs have discussed the same scenario: Can I share an Access database online with SharePoint or Access - Microsoft Community

Regarding your second question “What are my options for making sure 2 people won't try to access the database at the same time?” as the support article says it is not a good idea to share Access data on a SharePoint site if more than one person opens the Access database from SharePoint, multiple copies of the database may get created and some unexpected behaviors may occur but if you wish to store and access Access data in the  SharePoint library and want to prevent multiple users from opening the data at the same time, then you may consider setting up your library to require check out.

When you setup your library to require checkout, a user can check out a file from a SharePoint document library, and only that user can edit it, others can’t see your changes until the user checks in the file.

Reference: Check out or check in files in a document library (microsoft.com)

If you need any help, please let me know.

Best Regards,

Neha Singh | Microsoft Community Moderator

Answer 4

My point was that a person trying to get a basic Access relational database application properly deployed is not highly likely to be in a position to undertake a whole new development project. Even the accdb would have to be replaced if the data is moved behind a web application.

It's always an option to be considered, but this situation doesn't seem to be a great candidate for it.

Answer 5

Well, I don't think the other users will want to download and upload. That seems like a lot of extra work. Also, checking out requires going to the browser and we don't open the Access file from the browser, we added the folders to our directory so it's easy to open.

But thank you for the suggestions.