How can I connect to an Access database hosted on Microsoft OneDrive?

Question

1. Yes, I have tried searching this via Google already.
2. I am developing a .NET web application that will use data from Microsoft Access. In order to make sure all users are using the same up-to-date data, I have to host the database on a shared location. The shared location I have available is Microsoft's OneDrive.
3. Right now the plan is to not to have the web application hosted live because of limitations not worth explaining. Yes, I realize every time the app is updated the user has to redownload the latest version. UNLESS is it possible to "host" a .NET web app on OneDrive too?
4. Is there some way to build a working connection string for my .NET web app to connect to an access file located on OneDrive?
5. Long story short, this is the way I have to go about it due to certain lockdowns and limitations of my working environment.

Thank you in advance.

Answer 1

Unfortunately, you can't use Access on one drive this way.

You can use word, or excel, since those applications load the WHOLE file, where as Access works VERY different.

Access requires the ability to update small parts of the file. Remember, when you close ms-access, note how there is no "save" prompt. That's due to access being able to modify "one" record out of say 10,000 records. So, for reasons of performance, Access needs the ability to update JUST PART of the file. However, OneDrive is not windows networking and files, but is WHOLE file based. In other words, when you say click on a file in OneDrive, the WHOLE file (such as word or excel) is download to the client side computer. But, with Access, if you do that with multiple users, when you save (which you don't have a save prompt), then it would have to write back the WHOLE file and thus over-write any changes made by other users.

And note the following:

You can open a access application with 1 row of data, or 1,000,000 rows of data. The time to launch/load access is the SAME in both cases (because Access is NOT file based, but is "record" based, and thus requires a file system that allows you to update JUST PART of the file (the part where the one record is located inside of the file).

This is also why say a database system is VERY much faster then say Excel, since access is able to pluck out of the file ONE row of data, and not have to load the WHOLE file.

So, those "shared" drive systems are limited to applications that are WHOLE file based (word, Excel etc.). In those applications, you ALWAYS load the whole file, and then when done, you save the WHOLE file.

OneDrive, SkyDrive, Dropbox etc. use a web based system, and not windows file networking. Windows file network and a value UNC or path name to the file is required, and the location of the access database MUST reside on a windows compatiable system, not some SkyDrive or OneDrive (which by the way works with Andriod or even a Mac). So, these so called web based file systems can't work with Access, since it works VAST different then the other applations that work ALWAYS with a whole file at a time.

While this is why Access can have such high perfomrance with data, it also means that such "in the sky" file share systems can't be used with Access.

The only practial solutions are:

Move the data to SQL server, as the you NOT using a "file" based system anymore, but only some type of network "socket" connection type of technology.

You could try I suppose to setup a VPN to some server in the cloud, but since such connection's are prone to interruptions, then the result is a corrupted access file. (file based technology say can't reliably be used over wi-fi for example).

So, you have to migrate the data from that access database to SQL server based technology if you want the back end database to be shared and mutli-user.

So, short answer:

You can't use an access database shared on these "in the sky" file share systems, since they are not windows networking, but only a WHOLE file transfer system, not a bits and parts of the file system which Access requries.

Answer 2

One drive is not a file share in the normal sense. a cached copy is kept on the users machine. the user updates the cached copy. when closed, the copy is sent back to one dive.

this does not work well with a a database file. the users sharing the file, would update their copy, then overwrite the shared copy.

while one drive supports file patching at the server level, this is a separate api, not implemented at the filesystem level. an application has to specially written to support shared one drive updates.

you will need use a "real" file share, a real database server, or a dedicated website