Access on the cloud

[Pat Hartman]

They will update sales and stock on hand form other locations

I'm assuming they are working with their local stock. In that case, it is not critical for the updates to be real time for the other loculations. As long as each location keeps their stock up to date, they can communicate in batch witht he mother ship Seeing what the other locations had as of yesterday would hopefully be good enough. If not, you can use the phone to get an update.

 

[561414]

I'm not sure I get the full picture, so please tell me if I'm getting it right:
You have a front end in access.
Your back end is a spreadsheet.
The users are in different geographical locations, so there is no local network.
Users must ensure they're working with the latest version of the spreadsheet for things to make sense.

The suggestion: REST API.
I do not know what kind of device you guys are using. So, assuming you're all using laptops or PCs, convert your spreadsheets into a JSON format and upload that JSON data to a Firebase Realtime Database, its free quotas are huge. Firebase Realtime Database can be manipulated using its REST API, that means you can make your Access front end send HTTP requests to it. The server will return JSON responses. There's this awesome json converter module available for free, you can drag it into your VBA window to parse the JSON responses in a very easy way. This is the fastest way in my opinion, the other method would be to create your very own REST API, but that requires a ton of extra knowledge because you'll be getting into the realm of .NET, Javascript or others. Doing it like this will help you stick with VBA and it's also going to be helpful in the future if you want to migrate everything to the web, since you'll have a fully working database that does not need database maintenance, not as much as a private server or VPS would anyway.

My programming journey started as a VBA developer and all those Azure cloud services were just too complex for me. Then I found Appsheets, and it looked promising, but it quickly became a hassle. I did not like working with Google Sheets either, a lot of the easy Excel stuff just didn't translate well into Google Sheets so I quickly abandoned that ship too. Then I tried some .NET stuff and the amount of code scaffolding necessary to get things working was way outside the scope of my understanding back in the day.

The suggestion I'm giving is what gave me the quickest results and the greatest feeling of control, so I really recommend it as someone who started with VBA. You could PM me if you need a working example.

 

[rakhuuok]

I'm sure, after talking to their representatives, that there is a pretty good understanding of the bigger picture. The presenter was very explicit about migrating only tables and not being able to migrate the interface.

 

[MsAccessNL]

I'm not sure I get the full picture, so please tell me if I'm getting it right:
You have a front end in access.
Your back end is a spreadsheet.
The users are in different geographical locations, so there is no local network.
Users must ensure they're working with the latest version of the spreadsheet for things to make sense.

The suggestion: REST API.
I do not know what kind of device you guys are using. So, assuming you're all using laptops or PCs, convert your spreadsheets into a JSON format and upload that JSON data to a Firebase Realtime Database, its free quotas are huge. Firebase Realtime Database can be manipulated using its REST API, that means you can make your Access front end send HTTP requests to it. The server will return JSON responses. There's this awesome json converter module available for free, you can drag it into your VBA window to parse the JSON responses in a very easy way. This is the fastest way in my opinion, the other method would be to create your very own REST API, but that requires a ton of extra knowledge because you'll be getting into the realm of .NET, Javascript or others. Doing it like this will help you stick with VBA and it's also going to be helpful in the future if you want to migrate everything to the web, since you'll have a fully working database that does not need database maintenance, not as much as a private server or VPS would anyway.

My programming journey started as a VBA developer and all those Azure cloud services were just too complex for me. Then I found Appsheets, and it looked promising, but it quickly became a hassle. I did not like working with Google Sheets either, a lot of the easy Excel stuff just didn't translate well into Google Sheets so I quickly abandoned that ship too. Then I tried some .NET stuff and the amount of code scaffolding necessary to get things working was way outside the scope of my understanding back in the day.

The suggestion I'm giving is what gave me the quickest results and the greatest feeling of control, so I really recommend it as someone who started with VBA. You could PM me if you need a working example.

I was experimenting with this concept to. I was thinking about standalone databases wich sync by sending changes with rest api. I got a test version working by using one drive with small text files. Does Firebase has some kind of Hook which can trigger a notification that a record has been changed?

 

[Pat Hartman]

How necessary is it for this to happen in real time? If it doesn't need to happen live, then you would create a batch process that is significantly simpler than trying to sync the live spreadsheets.

If the local copies are not the Access application but are spreadsheets, that is a little more complicated. I would create a user form to control updates. That way you can ensure that each updated row is marked and validated. Then only the updated rows on the sheet need to be processed during the batch process.

 

[561414]

I was experimenting with this concept to. I was thinking about standalone databases wich sync by sending changes with rest api. I got a test version working by using one drive with small text files. Does Firebase has some kind of Hook which can trigger a notification that a record has been changed?

It has its cloud messaging service or "FCM", for short.

It depends on your business logic, of course, but I guess you can configure a server to handle these push notifications. This server would have to be running on each computer to handle what to do when the message is received. Either that or you can also query the realtime database right before making a change to validate whether your new data is valid and handle what to do when it's not valid. Something like:

Sub SubmitMyData()
    Dim latestData as Something: Set latestData = getLatestData()
    Dim currentData as Something: Set currentData = getCurrentData()
    Dim isValidData as boolean: isValidData = CompareData(latestData, currentData)
    If isValidData Then
        ' POST, PUT, PATCH, etc
    Else
        ' Show modifications
    End If
End Sub

I'll post a working example with a test database.

 

[Pat Hartman]

This won't work well with a flaky internet connection which is what the OP said is his problem. Otherwise, everyone would simply link to Access using RDP or something like that or an Azure BE.

We still don't know whether the requirement for real time is necessary. If it is, this should probably be a web app rather than an Access app. If overnight is sufficient, then the inputs can be batched and updated off hours or even during the day and then replacement BE's can be distributed at the end of processing so every site starts out with a fresh, updated copy of the BE.

Some processes require real time updates, others work fine with batch updates. This difference is part of what dictates the platform of choice. Access may just not be the right platform.

 

[561414]

Here's a test database using Access as front end and Firebase as back end.

At the time of posting this, you can test it temporarily with my database, but if you go to the module mod_General, there's a baseURL constant there, you can create a realtime database and paste the url there to see what's happening.

To create the Firebase realtime database:
1. go to firebase and log in with a google account
2. create a project
3. accept defaults, but when it asks for the security rules choose start in test mode
4. on the left menu, find the realtime database and create one
5. once the realtime database ui appears, click on the clip icon to copy the url