Keeping Access files on a SharePoint site??

[JamesPower4]

Hello: I work as a research scientist and I keep a substantial amount of data in Access databases. I use both a virtual machine and a laptop to work with those databases. Using the VM is helpful because I can disconnect my laptop from my employer’s network without disrupting a long-running job. Because I use two machines I would like to keep them in one network location so that both machines are able to access (no pun) my databases. Keeping them in one spot will save me from wondering which file is the most current. I learned the hard way to never store Access databases in a location that synchronizes (such as Offline Files or OneDrive), because the database will be damaged if synchronization occurs while I have the file open. I have seen “hints” that it might be OK to store an Access database on a SharePoint site without the database becoming corrupt. Is this correct, and do I need to “check out” the database so that the other machine cannot open it? My network is slow, so I likely will have to download the Access file from SharePoint to work with it. Access Web Services are not available, and I do not want to use SharePoint lists because (as I understand it) SharePoint lists do not: support unique composite keys; the difficulty transferring Access tables to lists; the learning curve. Any suggestions or thoughts? Thanks in advance.

[#2]

@JamesPower4 Thanks for posting your question in the Access community.  Yes, you can in theory store your Access database in SharePoint but I don't recommend it, it will be slow and require syncing, and honestly I don't think it's worthwhile.

Have you tried splitting the database using the database splitter? It would allow you to share just the data file on your network on both machines, for more information on this feature check out:

https://support.office.com/en-us/article/split-an-access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498...

Let us know if that worked for you!

Juan

[#3]

Thanks Juan. I do have some databases that are split, and I'll likely follow your suggestion and split them all.

I was hoping I could have a "local" copy of the database that I could work with when I'm not in my office, both for speed considerations and also so I wouldn't need to connect to my employer's VPN.  In a nutshell I want to avoid any synching of the files. I had hoped SharePoint would allow me to "lock" the file (by checking it out?), and then when I'm done it would upload the file without synching when I check it in. I guess I can't have it all.

Jim

@JamesPower4