Moving Access Database to OneDrive

[DatabaseTash]

I have an access database which is currently on our server and is used by about 18 people. The database is set with permissions and a auto-updating utility. Everyone except me uses a read only permission. The Database is a reference for plans and provides a link to the digital copy of each plan.

Management would like to move it to OneDrive. I have concerns with this though. Is it possible? Will it sync properly? Would I move the front and back end to OneDrive?

I also have a PowerShell script I run to insert the hyperlinks when new batches of plans are added. I was told that to run this script everyone needs to be out of the database. Will it still appear with the lock to show if someone is in the database when it is in OneDrive?

Does anyone have any experience with this? The database works like a dream now and I don't want it to be out of action.

 

[theDBguy]

Hi. I don't think One Drive is a good place for an Access database, but since you said it's read only anyway, this may be an exception. Still, I would recommend thorough testing before committing to it.

 

[AccessBlaster]

I doubt onedrive or Google drive will allow the lock file to remotely open and close.

 

[DatabaseTash]

Hi. I don't think One Drive is a good place for an Access database, but since you said it's read only anyway, this may be an exception. Still, I would recommend thorough testing before committing to it.

Thanks for the reply. I'm glad I am not alone in thinking this is a bad idea. Is it mainly the data syncing side of things that concern you about it?

 

[theDBguy]

Thanks for the reply. I'm glad I am not alone in thinking this is a bad idea. Is it mainly the data syncing side of things that concern you about it?

Well, keep in mind this. As far as I know, when you put a file on One Drive, a copy is placed on your hard drive. When you open the file, you're actually opening the local copy. One Drive then syncs the whole file to update the one in the cloud. So, if you were to share a multi-user db using One Drive, each user will actually update their own local copy of the BE, instead of the one in One Drive. During the synching process, you will likely get a lot of edit conflicts.

So, I was saying if your users won't be updating their local copy of the data because it's read only, then there probably won't be any data conflicts.

 

[DatabaseTash]

Thanks for your help theDBguy

 

[AccessBlaster]

I did see an ODBC driver for the Google Drive, but I don't see how you would keep a persistent handshake as you would with a SQL server.

 

[Deleted member 147267]

Didn't somebody here write a good treatise about 3 months ago on why you should NOT do this? I believe it covered WIFI as well. Plus, if we're talking about a split db and the back end is on OneDrive, I'd say that's about 10x worse than everyone sharing the same front end over a network.

I also have a PowerShell script I run to insert the hyperlinks when new batches of plans are added.

Why is that the case? Aren't you just appending or updating records?

 

[The_Doc_Man]

First, I would be leery of this. Access normally uses SMB (Server Message Block) protocol to talk to its component files. However, OneDrive does not normally work this way. It uses HTTP or HTTPS and talks to a synchronizer app. (See 1st link.)

https://www.netfort.com/blog/monitoring-onedrive-traffic/

I am thinking that this would lead to some serious corruption issues because of file locking conflicts. SMB lock patterns are not exactly the same as HTTP-family lock patterns because of differences in how they address the data-in-motion. However, I can only state doubts, not certainties, on the issues involved.

I know that if you are planning a true OneDrive app, you have to consider that you will have a "sync" app running in the background and you have to consider your network load is going to be very "bursty" when updates are running. (See 2nd link.)

https://docs.microsoft.com/en-us/onedrive/network-utilization-planning

I'm not going to say that this won't work, but if there is even a slight flaw in the way the sync app decides to re-sync, you would end up transmitting a huge file over your network almost continuously. You might see bottle-necked data flow. There are whole forums where folks have complained about slow syncing and getting "hung" while waiting for the files in question to unlock. Search a bit more in the one-drive forum and in the Microsoft support site for topic OneDrive (as one word).

https://support.office.com/en-us/ar...-changes-b386b813-9b66-4e47-8c4c-2b45533edccd

https://onedrive.uservoice.com/foru...le-differential-sync-only-sync-parts-of-the-f

 

[theDBguy]

Thanks for your help theDBguy

Hi. You're welcome. Good luck with your project.

 

[gstylianou]

I have an access database which is currently on our server and is used by about 18 people. The database is set with permissions and a auto-updating utility. Everyone except me uses a read only permission. The Database is a reference for plans and provides a link to the digital copy of each plan.

Management would like to move it to OneDrive. I have concerns with this though. Is it possible? Will it sync properly? Would I move the front and back end to OneDrive?

I also have a PowerShell script I run to insert the hyperlinks when new batches of plans are added. I was told that to run this script everyone needs to be out of the database. Will it still appear with the lock to show if someone is in the database when it is in OneDrive?

Does anyone have any experience with this? The database works like a dream now and I don't want it to be out of action.

HI, its is really bad idea for multi use (18 person).! Since many years i made some testing and always i have many - many problems for multi use purposes. You always have the problem "Conflicted copy" and you will loose data..

 

[CJ_London]

Curious as to why management would want to move it to onedrive. If working well now, what would be the perceived benefit?

 

[isladogs]

Didn't somebody here write a good treatise about 3 months ago on why you should NOT do this? I believe it covered WIFI as well.

Yup. This comes up repeatedly and the answers are always the same - don't do it!
For example, see https://www.access-programmers.co.uk/forums/showthread.php?t=305665&highlight=OneDrive

 

[gemma-the-husky]

I think management would be interested if it worked, because an off site user could then connect to the one-drive, rather than need to connect to an office based terminal server.

The point that has been made is that these cloud solutions (one-drive, dropbox etc) work by synching local files to the cloud, and because of that aren't suitable for multi users.

I believe Sage have a workround for this problem with their (non-access based) accounts systems, but I am not sure what limits are placed on their "multi-user" cloud version.

 

[DatabaseTash]

Curious as to why management would want to move it to onedrive. If working well now, what would be the perceived benefit?

They would like to work towards having everything cloud based and no server.

 

[DatabaseTash]

Didn't somebody here write a good treatise about 3 months ago on why you should NOT do this? I believe it covered WIFI as well. Plus, if we're talking about a split db and the back end is on OneDrive, I'd say that's about 10x worse than everyone sharing the same front end over a network.
Why is that the case? Aren't you just appending or updating records?

The plans are not stored in the database, just the a record of their main details. They are stored separately in another folder to avoid filling the database. There are about 20,000 plans. I think it is quicker and more accurate to run the script for the links. When I add plans I can add 30 to 100 plan records at a time.

 

[isladogs]

The plans are not stored in the database, just the a record of their main details. They are stored separately in another folder to avoid filling the database. There are about 20,000 plans. I think it is quicker and more accurate to run the script for the links. When I add plans I can add 30 to 100 plan records at a time.

Whilst that is as it should be, it has no effect on the earlier comments. Data corruption will happen at some point using a storage location in the cloud.

 

[DatabaseTash]

THANK YOU so much everyone!! You all have a wealth of information. So glad I checked first.

This forum is awesome!!!

Thank you

 

[theDBguy]

THANK YOU so much everyone!! You all have a wealth of information. So glad I checked first.

This forum is awesome!!!

Thank you

Hi. We're all happy to assist. Good luck with your project.

 

[Pat Hartman]

If it is possible to create a folder on OneDrive that you can update and share but that NO ONE ELSE can update, you could use OneDrive to share. As long as OneDrive ALWAYS downloads a copy and opens it locally, you are fine. If OneDrive tries to open the file in the OneDrive folder, this will not work. That is the question you need to have answered. I can't answer it and I don't think anyone else did either although Doc probably came close. I wouldn't even consider risking allowing multiple users to update the app serially. Too much could go wrong.