Access on the cloud

[Gismo]

Hi all,

I know that Access does not play along very well when saved on the cloud for sharing

I am in a bit of a predicament, as I don't know how to do a fully functional program in Excel and as Google sheet is a awesome platform for sharing, I don't know how to do a fully functional program in Google sheet either.

Does anyone have a suggestion?
I would like to apply Appsheet as well
How would Access handle all transaction tables to be exported or rather linked to a spreadsheet for the next user to have the latest version of the transition table

I am running out of ideas

Please could you advise? will be highly appreciated

 

[CJ_London]

google sheets has limited programming capabilities certainly compared with excel so depends what you mean by 'a fully functional program' as to whether google sheets will actually do what you want

To learn how to program in google sheets or just see what could be done, google something like 'programming in google sheets'. Plenty of options/examples are returned

If you want access on the cloud, consider using a sql azure back end. It's not free. There is also a new development from MS called the 'access connector for dataverse' where your access tables are replicated on the web. Also not free and I gather quite a steep learning curve. Both can be linked to powerapps for use on smart phones.

It is also possible for access to connect to other rdbms's on the web such a mySQL

What matters is how good your broadband connection is in terms of speed and stability

Another alternative is terminal server or citrix - both FE and BE are on the server so performance is good even for a slow broadband - only network traffic is mouse and keyboard events one way and screen refreshes the other. Again, not free. But easy to set up - at it's simplest just copy your FE and BE across and relink. Also has the benefit of being able to run from any device that supports remote desktop and in the event of network interruption, the app remains open, user just has to reconnect to carry on from where they left off.

 

[Gismo]

google sheets has limited programming capabilities certainly compared with excel so depends what you mean by 'a fully functional program' as to whether google sheets will actually do what you want

To learn how to program in google sheets or just see what could be done, google something like 'programming in google sheets'. Plenty of options/examples are returned

If you want access on the cloud, consider using a sql azure back end. It's not free. There is also a new development from MS called the 'access connector for dataverse' where your access tables are replicated on the web. Also not free and I gather quite a steep learning curve. Both can be linked to powerapps for use on smart phones.

It is also possible for access to connect to other rdbms's on the web such a mySQL

What matters is how good your broadband connection is in terms of speed and stability

Another alternative is terminal server or citrix - both FE and BE are on the server so performance is good even for a slow broadband - only network traffic is mouse and keyboard events one way and screen refreshes the other. Again, not free. But easy to set up - at it's simplest just copy your FE and BE across and relink. Also has the benefit of being able to run from any device that supports remote desktop and in the event of network interruption, the app remains open, user just has to reconnect to carry on from where they left off.

Thank you CJ for the info

So there is now free option on having you access DB saved on the cloud available for sharing?

At this point in time we are a 3 members in our new company, so having a dedicated server an all to assess via a network is a option but the issue comes in when i need to have appsheet connect to the DB and having mobile phones connected to the DB

I suppose after every transaction you could export to a excel or google sheet but then you are not running in real time and could run into mismatch of updated information.

If each user has the front end on his own computer linking the back end only to a folder with different excel or google sheets could this actually work? I suppose you would sill be running lag in the latest update

I would really like o use access as the back bone as I am more proficient in access that creating a program as you would in access in anther application such as google sheets

There just seems to be no real, cheap option here and no integrated simple free platforms

I also looked into Filemaker but you pay per user and very expensive

 

[Minty]

You can host your data in Azure SQL, which if you don't need much processing power can be pretty inexpensive.
There is a free trial you could sign up for to see if that works for you.

I don't think you can expect to get good cloud services that are reliable and backed up for nothing.
If it's a commercial application then it's worth putting your hands in your pockets.

Mobile phones could be used with power apps to your data stored in Azure SQL as well.
But there is a cost, and a learning curve to making it all work.

Edit: Sorry I missed CJ's post essentially saying the same...

 

[Gasman]

If you struggle with VBA, then Google Javascript is going to be tougher I believe?

 

[Gismo]

I googled Azure SQL, yes it is a bit pricy for a small business, at least $300 per month, which is quite expensive
A this point in time it will be working only from the office but I need to give agents on the road access to the DB in the near future

So I am really stuck here between a rock and a hard place, no idea what to do
Access will give me the best approach in terms of what I know I want out of the program apposed to excel or google sheet applications

 

[CJ_London]

So there is now free option on having you access DB saved on the cloud available for sharing?

As Minty says, other than free trials, there is nothing on the web for free.

If you are going to do it, accept there is some cost involved. Perhaps around £20/user/month. Trying to save this by having a poor performing and potentially difficult to use system is not the way to go - for 3 of you, around £700/year.

In the short term you could look at using apps like LogMeIn or Teamviewer (I believe they have free versions for personal use and there are other competitors out there), a personal form of terminal server where an individual can connect to their own computer from most devices. But only one user can connect at a time and the computer needs to be set to allow the connection which means others cannot use it at the same time, either via the app or directly on the keyboard.

If you have already developed your app, I would recommend the terminal server/citrix route. You can install access runtime so you don't have the licencing cost, I don't know how it would work with 365 - but if that is what you are using you should have a license that enables you to install 365 on several devices.

 

[Gismo]

Thank you all for the advise

For now, I will continue to develop the app in Access and have google sheets updated after each transaction

This will not be true live data but will be as close as I will get at this point in time

Will look into the other options as advise later on

 

[Minty]

Look at the DTU pricing model not a dedicated vCore - it's significantly cheaper:

£15 a month for a massive amount of storage and powerful enough for most smaller business applications.
I think we run 20 + databases on a 100 DTU server basis and it's great.

 

[ebs17]

Another alternative is terminal server or citrix

There are also Managed Provider as
www.accesshosting.com
www.apponfly.com
www.fra.me

 

[The_Doc_Man]

Look in the "Similar Threads" section below this thread and check the articles there.

The short summary is that without something special, the answer is a hard "NO" to Access + Cloud. Access requires protocols not supported by cloud servers, which only allow/support whole-file transfers. Access requires use of Server Message Block (SMB), which is a partial-file sharing protocol used by Windows itself for remote file sharing and printer sharing.

There are many strategies for remote file handling that would allow Access to be supported through "something else" - like RDP (remote desktop protocol), which requires either a Windows Server O/S or a CITRIX package. GPGeorge's post will talk about another commercial solution. The key takeaway is that it ain't cheap and it ain't easy.

Your comment about using Google Sheets or some other spreadsheet solution isn't terribly bad, using a spreadsheet as a "transaction log" for the remote work. Heck, you could even use Excel for that if you are ambitious enough. Just remember though... Access and WAN don't get along so well if you have even the slightest bit of flakiness in your net connections. Which is par for the course for WAN connections.

 

[Pat Hartman]

What EXACTLY do the other users need on line access for? Are they doing data entry? Are they looking up prices? What??

 

[GPGeorge]

I googled Azure SQL, yes it is a bit pricy for a small business, at least $300 per month, which is quite expensive
A this point in time it will be working only from the office but I need to give agents on the road access to the DB in the near future

So I am really stuck here between a rock and a hard place, no idea what to do
Access will give me the best approach in terms of what I know I want out of the program apposed to excel or google sheet applications

That's a bit higher than my experience would suggest. What tier are you looking at. I had one client (before I retired) supporting ~20 users on an Azure database with Azure data storage for less than $50 a month, all in. We could have scaled up and paid that amount or more. It depends on service level and performance, etc.

 

[GPGeorge]

Look in the "Similar Threads" section below this thread and check the articles there.

The short summary is that without something special, the answer is a hard "NO" to Access + Cloud. Access requires protocols not supported by cloud servers, which only allow/support whole-file transfers. Access requires use of Server Message Block (SMB), which is a partial-file sharing protocol used by Windows itself for remote file sharing and printer sharing.

There are many strategies for remote file handling that would allow Access to be supported through "something else" - like RDP (remote desktop protocol), which requires either a Windows Server O/S or a CITRIX package. GPGeorge's post will talk about another commercial solution. The key takeaway is that it ain't cheap and it ain't easy.

Your comment about using Google Sheets or some other spreadsheet solution isn't terribly bad, using a spreadsheet as a "transaction log" for the remote work. Heck, you could even use Excel for that if you are ambitious enough. Just remember though... Access and WAN don't get along so well if you have even the slightest bit of flakiness in your net connections. Which is par for the course for WAN connections.

Caspio is a bit different from what I anticipated. I won't get the video processed for upload until early next week. But long story short, it's a migration strategy that puts the data into a Caspio database and requires you to create a new, web-based interface for that data. Synching between the local accdb and the Caspio cloud db is possible via an ETL process, which can be scheduled. The video will cover costs, but the basic level if free. The first commercial level is $150/month.

 

[Pat Hartman]

Caspio is marketed as a "conversion" tool for "Access" but that's because they don't actually know what Access is. They think "Access" is Jet and ACE.

 

[Gismo]

What EXACTLY do the other users need on line access for? Are they doing data entry? Are they looking up prices? What??

Hi,

They will update sales and stock on hand form other locations

 

[GPGeorge]

Caspio is marketed as a "conversion" tool for "Access" but that's because they don't actually know what Access is. They think "Access" is Jet and ACE.

Not really, Pat. I think it's more a matter of using imprecise terminology. A lot of us do tend to refer to "Access databases" without the clarity of noting that that means the tables, code and interface objects which are all part of it.

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.

Besides, everyone knows that Marketing is a whole other kind of life-style detached from reality.

 

[GPGeorge]

Hi,

They will update sales and stock on hand form other locations

The problem is that, once you migrate your data to Caspio, it is then ON their servers. Data migration is a one-way street, from the Access tables to their server. You could easily create a mobile application to update stock on hand. However, synching those updates back to Access tables is a matter of what I think we could call ETL. You can export that data, in bulk, to an accdb on OneDrive or DropBox, for example, and from there pull it back into the original tables in the local relational database application. This can be done on a schedule, but it is definitely not a live synching process. We discussed this in the last 10 minutes or so of the presentation.

 

[GPGeorge]

The problem is that, once you migrate your data to Caspio, it is then ON their servers. Data migration is a one-way street, from the Access tables to their server. You could easily create a mobile application to update stock on hand. However, synching those updates back to Access tables is a matter of what I think we could call ETL. You can export that data, in bulk, to an accdb on OneDrive or DropBox, for example, and from there pull it back into the original tables in the local relational database application. This can be done on a schedule, but it is definitely not a live synching process. We discussed this in the last 10 minutes or so of the presentation.

I should also mention that you can synch data from the Access tables to the Caspio tables at regular intervals, again "synching" in less than real time. If your data is highly sensitive to volatility, this would be a tough way to implement remote access to it.

 

[gemma-the-husky]

You can get a free trial at AWS (Amazon) and upsize the backend to a SQL server database in the cloud.
Then your front end connects from anywhere, but performance may depend on internet speed. Also needs a whole new load of skills