Backend Access database on OneDrive - Can this be done

[Snowflake68]

I am currently building a small application to create quotes for customers. I have place all of the data in a separate linked backend Access database containing several tables with the sales data, customer data and supplier data as well as some very small lookup tables. There will be a maximum of 3 users running a copy of the front end on their own computers.

They want to be able to use the application when they are working from home but they don't currently have a server that they can access so I was thinking of putting the backend in OneDrive and linking the application to that.

Can this be done and if not are there any other options that would work which would allow them to use the application both in the work place as well as at home?

 

[isladogs]

Whilst it can be done, it is definitely not recommended to use OneDrive or other cloud storage such as GoogleDrive or Dropbox as a BE location.
Access is very intolerant of even brief network disconnects and it is likely you will experience data corruption if records are being edited when dropouts occur.

Recommend the use of Remote Desktop Connection or similar.

 

[sonic8]

Can this be done and if not are there any other options that would work which would allow them to use the application both in the work place as well as at home?

No, you cannot put an Access backend on OneDrive for concurrent access of multiple users.

Instead you could investigate Sharepoint Lists or Dataverse as options for your backend.

 

[The_Doc_Man]

It can only be done indirectly; for example, as isladogs has suggested, using a remote desktop solution. Access does not work directly with most cloud-like solutions due to protocol issues. Cloud storage usually does not support Server Message Block (SMB) protocol in any of its versions - 1, 2, or 3 - and thus will run into serious risks that could lead to file corruption.

 

[isladogs]

That's an excellent article Philipp. I've bookmarked it for future use when this question arises next.

 

[sonic8]

@The_Doc_Man and @isladogs: While your comments are generally correct, the problem with OneDrive and its ilk is not the network connectivity, but the fact that these systems keep local copies of the stored files. Any write operation to a file is done to the local copy which is synchronized with the online file storage later. - Of course, this wreaks havoc on Access files if multiple users edit (a copy of) the same file simultaneously.

 

[GPGeorge]

I also like that article better than the one to which I usually direct such questions.

 

[The_Doc_Man]

@The_Doc_Man and @isladogs: While your comments are generally correct, the problem with OneDrive and its ilk is not the network connectivity, but the fact that these systems keep local copies of the stored files. Any write operation to a file is done to the local copy which is synchronized with the online file storage later. - Of course, this wreaks havoc on Access files if multiple users edit (a copy of) the same file simultaneously.

You are correct, but my main complaint wasn't remote connectivity; it was the implications of using SMB protocols - which are useless in the circumstances you describe. It is a case of "left hand not knowing what the right hand is doing" because file locking actually cannot interfere with this internal synchronization that you mentioned, and therefore SMB doesn't know which copy of the file to update. Because of SMB v2 and v3 having the "reservation" feature, the disk blocks you eventually DO write might have been deferred long enough that when they DO get written, they would actually overwrite the DB file with old (obsolete) data. Can you say "corruption?"

 

[isladogs]

Hi Philipp
Somehow I'd never seen your article until today even though it was written in 2017.
You really did cover all bases in the article

As it also often comes up, I was also glad to see you make it clear that opening an Access file exclusively on e.g. OneDrive is still not guaranteed to be safe.

 

[Pat Hartman]

@Snowflake68 Both Citrix and Remote Desktop can be hosted by third party providers and both will provide a far superior solution to Dataverse and SharePoint since they allow the sharing of files as well. If all you need to share is Access, the solutions can be reduced in cost by using the Access Runtime and not installing Office 365. You have have the hosting company simply install the Access runtime which doesn't have any monthly costs. Only the developer needs to have a full version of Access and it doesn't need to be hosted. He just needs to be able to up/download files so he can transfer updated files to replace the current versions.

 

[561414]

@Snowflake68
I recommend Firebase.
You can setup a project here: https://console.firebase.google.com/u/0/
It costs zero money, it's by google, therefore you use your google account, and it's managed by their staff so it never goes down, you also don't need to pay hosting, it's blazing fast, it gives you a lot of stats about the traffic, you don't need to maintain it, you don't need to give credit card info to use it, it's secure, scalable, you can start right away, you will work with its easy REST API, so you will basically become a Google Cloud Developer with that, and I have a sample you can test now to get ideas. If you wanna try, do this:
1. Go to the site above and Login, it's by Google, it's safe
2. Click "Create Project"
3. Give it a name
4. When prompted for security rules, choose Test Mode
5. Create a realtime database when you're in, it's only one click for that
6. You'll see a blank canvas when it's done, copy the url at the top of the canvas (data will appear there when you interact with it)
7. Download my sample attached, open it, you'll get an error because you need to do the next step
8. Paste the url you copied before at the top of the general module inside my file
9. Test the form
10. Send the frontend to your peers, since you already setup the database, they will just open it and use it and you'll see what they do.

It's a NoSQL database, which means it works with JSON objects. Luckily, VBA can work with that through arrays, collections and dictionaries without issue. Check the sample to see what I mean. It's a work in progress, so if you have issues just shoot.

 

[Pat Hartman]

It costs zero money, it's by google, therefore you use your google account,

I suppose if it is your own personal data, you won't mind if Google uses it to make money. Keep in mind that nothing is actually free. If it is "free", YOU and your data are the product.

I doubt a company would put up with relinquishing all their data to Google.

 

[amorosik]

@Snowflake68
I recommend Firebase.
You can setup a project here: https://console.firebase.google.com/u/0/
It costs zero money, it's by google, therefore you use your google account, and it's managed by their staff so it never goes down, you also don't need to pay hosting, it's blazing fast, it gives you a lot of stats about the traffic, you don't need to maintain it, you don't need to give credit card info to use it, it's secure, scalable, you can start right away, you will work with its easy REST API, so you will basically become a Google Cloud Developer with that, and I have a sample you can test now to get ideas. If you wanna try, do this:
1. Go to the site above and Login, it's by Google, it's safe
2. Click "Create Project"
3. Give it a name
4. When prompted for security rules, choose Test Mode
5. Create a realtime database when you're in, it's only one click for that
6. You'll see a blank canvas when it's done, copy the url at the top of the canvas (data will appear there when you interact with it)
7. Download my sample attached, open it, you'll get an error because you need to do the next step
8. Paste the url you copied before at the top of the general module inside my file
9. Test the form
10. Send the frontend to your peers, since you already setup the database, they will just open it and use it and you'll see what they do.

It's a NoSQL database, which means it works with JSON objects. Luckily, VBA can work with that through arrays, collections and dictionaries without issue. Check the sample to see what I mean. It's a work in progress, so if you have issues just shoot.

Very interesting example
Do you know how to make Access 'see' changes as they happen in the Firebase db, without using periodic query?

 

[561414]

I suppose if it is your own personal data, you won't mind if Google uses it to make money. Keep in mind that nothing is actually free. If it is "free", YOU and your data are the product.

I doubt a company would put up with relinquishing all their data to Google.

@Pat Hartman If you use a mobile phone, regardless of the brand, a ton of its apps are using Firebase. In fact, if you've ever signed up to anything that lets you sign up with a Google/Apple/Facebook/Twitter/Github account, that's most likely Firebase. So, that's a LOT of companies relinquishing all their data to Google. But what about hosting spaces? Are they really not keeping a copy of your data? Should you set up your own server? I mean, it's your computer, but who's operating system? Should you also code your own operating system? With who's language though? Should you create your own language? To work with who's hardware?

I mean, they're not stopping you from ciphering your data before sending it. But then again, what do you have to say about quantum computers ready to crack most ciphering algorithms right now? What can you say about smart phones listening to you 24/7 for ad customization? And what can you say about your ISP knowing what you do online? Or any military technology that listens to all phone signals? X-Ray in airports? But let's not go too far, what can you say about credit card shopping online? Should you just not embrace it? Can we live without being spied on by satellites?

By the way, it's 1GB of free storage and 10 GB of free downloads. That's a lot for most app needs, if your product becomes successful enough and you need bigger limits, that's where you pay. You are a potential paying client by using the service. Why are we even talking about this, the OP wanted something where he could store data available world wide.

 

[561414]

Very interesting example
Do you know how to make Access 'see' changes as they happen in the Firebase db, without using periodic query?

@amorosik
No, I still haven't found a way to tickle access' ribs and make it jump from outside of a form. You can set up a server that notifies you of that, but Access will be blind to it without a periodic query, the server can notify you though, easily. But it's a separate thing.

I have seen claims of people that programmed a listener with winsockets but I have no idea or necessity to do it from Access so far. The one time we had this conversation months ago, I thought we could create a solution to that as a community, but the proposal wasn't welcome, so I desisted. We can figure something out though, maybe a .NET addin that works as a server from within access.

 

[Pat Hartman]

If you use a mobile phone, regardless of the brand, a ton of its apps are using Firebase. In fact, if you've ever signed up to anything that lets you sign up with a Google/Apple/Facebook/Twitter/Github account, that's most likely Firebase. So, that's a LOT of companies relinquishing all their data to Google.

Which is exactly why I don't use any of those services. As my mother frequently told me as a child, just because your friends are all jumping off the bridge, doesn't mean that you need to follow.

With a paid hosting service, YOU are not the product. You sign a contract and it specifically prohibits them from doing anything with YOUR data. If you bother to read the agreement when you download a free app, you will see that you are giving them permission to access your data. Why should a game need to access your contacts????? Why do they need location information?????

 

[561414]

How do you make sure that, if you sign up to a service via email, they're not using Firebase as well? Firebase provides email and phone authentication as well. You can agree to any terms of service, all privacy and security you want, including contracts and that won't stop an employee of the hosting service to "have a quick look" at your data to "personalize your user experience" or to know what product to offer next.

We are at a point where we no longer have full control. It's not that our friends are all jumping off the bridge, is that we've already jumped and didn't notice, by simply using the same network that we call internet. By simply living on earth.

By the way, those agreements when you download a free app, they're forced by the app store's owner. Which is Google in the case of Android.

There's really not much that we can do. But if you setup your own server, I believe there's a much higher risk of it being hacked, it takes but a few seconds before you start logging automated requests looking for your credentials, given that the server is just an IP address, all they need to do is choose one from the 4billion and see if it's attack-able. You need to do several things before making it live. Whereas with my suggestion, it's secure from the get go. Firebase also has its privacy agreement, they would get in so much more trouble than your typical hosting company if something they said was secure is not.

 

[GPGeorge]

How do you make sure that, if you sign up to a service via email, they're not using Firebase as well? Firebase provides email and phone authentication as well. You can agree to any terms of service, all privacy and security you want, including contracts and that won't stop an employee of the hosting service to "have a quick look" at your data to "personalize your user experience" or to know what product to offer next.

You are sure that hosting service employees can, and probably do, commit malfeasance? But Firebase employees do not?

We are at a point where we no longer have full control. It's not that our friends are all jumping off the bridge, is that we've already jumped and didn't notice, by simply using the same network that we call internet. By simply living on earth.

By the way, those agreements when you download a free app, they're forced by the app store's owner. Which is Google in the case of Android.

And how does that make a difference? The agreement states they have the right access your data. Who cares if it's a Google requirement or a requirement of the app producer? This falls under the heading of a distinction without a difference.

There's really not much that we can do.

There is one thing we can do: don't sign up.

But if you setup your own server, I believe there's a much higher risk of it being hacked, it takes but a few seconds before you start logging automated requests looking for your credentials, given that the server is just an IP address, all they need to do is choose one from the 4billion and see if it's attack-able. You need to do several things before making it live. Whereas with my suggestion, it's secure from the get go. Firebase also has its privacy agreement, they would get in so much more trouble than your typical hosting company if something they said was secure is not.

Are the legal contracts different in some way that makes this position defensible? You've read and compared such agreements, right?

 

[561414]

@GPGeorge Do not add words I didn't say.

You are sure that hosting service employees can, and probably do, commit malfeasance? But Firebase employees do not?

Look at the context of the thread, the point is that we can never be 100% sure as long as we're using someone else stuff. It's pretty clear from post #14.

And how does that make a difference? The agreement states they have the right access your data. Who cares if it's a Google requirement or a requirement of the app producer? This falls under the heading of a distinction without a difference.

The point is that not all apps show you an agreement, in order to put your app there, you need to add one. That is the point, that Google at least forces app developers to add it. You keep twisting my words, dude.

There is one thing we can do: don't sign up.

Not signing up? read post #14

Are the legal contracts different in some way that makes this position defensible? You've read and compared such agreements, right?

Google is a tech giant, typical hosting providers would not have the same amount of complaints should a security problem occur.

Do not twist my words.

 

[GPGeorge]

You made the statements. For example, "the point is that we can never be 100% sure as long as we're using someone else stuff." I'm simply pointing out that the claim of potential malfeasance does not go one way. It's not an argument one way or the other in favor of either option.

Google forces developers to reveal the terms of the agreement, right? Does that mean they are any more or less important? Again, not an argument in favor of either option.

And the point of suggesting that not signing up is an option is that if you want to avoid the whole mess, just don't sign up for one of the services. That invalidates the claim that "There's really not much that we can do."