Moving Access Database to OneDrive (1 Viewer)

DatabaseTash

Registered User.
Local time
Today, 21:02
Joined
Jul 23, 2018
Messages
149
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

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 03:02
Joined
May 22, 2010
Messages
5,823
I doubt onedrive or Google drive will allow the lock file to remotely open and close.
 

DatabaseTash

Registered User.
Local time
Today, 21:02
Joined
Jul 23, 2018
Messages
149
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

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
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.
 

AccessBlaster

Registered User.
Local time
Today, 03:02
Joined
May 22, 2010
Messages
5,823
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.
 

Micron

AWF VIP
Local time
Today, 06:02
Joined
Oct 20, 2018
Messages
3,476
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

Immoderate Moderator
Staff member
Local time
Today, 05:02
Joined
Feb 28, 2001
Messages
26,996
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
 

gstylianou

Registered User.
Local time
Today, 12:02
Joined
Dec 16, 2013
Messages
357
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

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Feb 19, 2013
Messages
16,553
Curious as to why management would want to move it to onedrive. If working well now, what would be the perceived benefit?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:02
Joined
Sep 12, 2006
Messages
15,613
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

Registered User.
Local time
Today, 21:02
Joined
Jul 23, 2018
Messages
149
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

Registered User.
Local time
Today, 21:02
Joined
Jul 23, 2018
Messages
149
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

MVP / VIP
Local time
Today, 10:02
Joined
Jan 14, 2017
Messages
18,186
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.
 
Last edited:

DatabaseTash

Registered User.
Local time
Today, 21:02
Joined
Jul 23, 2018
Messages
149
THANK YOU so much everyone!! :D You all have a wealth of information. So glad I checked first.

:)This forum is awesome!!!:)

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:02
Joined
Oct 29, 2018
Messages
21,357
THANK YOU so much everyone!! :D 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

Super Moderator
Staff member
Local time
Today, 06:02
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom