Ms access online back end / via internet .... without affecting performance

[Ihk]

1) I want to stick with ms access as front end, because I am used to it and its ease.
2) I always have multiuser interface, login system

I want if users have their front end they should be able to login to perform tasks from any where via internet connection.
AWS:
To test I tried it as back end on AWS sql and connected via odbc to online sql instance. Performance was very slow.
Sharepoint:
I also looked into online sharepoint, i dont like this list system in sharepoint because all of my backend structure is disturbed, as I think i cant get back (share point list) into my access db like before , if i want some changes in any stage in running app.
AZURE Sql
I have not tried Azure sql, I can do that, if somebody has experience with performance and loading of data.
I am looking for cheapest possible option.
CITREX:
Citrex terminal, virtual server costs 12 euro per user, which is too much because of every user.
I know for best performance, best of all would be virtual server, but again as i know all these service provider costs per user (if musti user want to login).
Suggestion for getting online:::
Please suggest me ideas, otherwise I must have to think for website/native cloud.
POS:
Another Question from experienced users:
If i have online backend, do you think for pos (point of sale) this (ms access) will be as good as on local network? Because many transactions on each barcode scan, will it be speedy stll?
........
Dou you have any other suggestion which serve the purpose and is cost effective, not have too steep learning curve.
Thanks

 

[arnelgp]

If i have online backend, do you think for pos (point of sale) this (ms access) will be as good as on local network? Because many transactions on each barcode scan, will it be speedy stll?

use offline db. if you are keeping stocks locally, use local be db.
upload the transactions later that day to the main server.

 

[Ihk]

use offline db. if you are keeping stocks locally, use local be db.
upload the transactions later that day to the main server.

Local network is okay, this what ms access is doing.
I want that users can access it any where through internet, from home etc...
I am looking for suggestions, detail mentioned above

 

[Auntiejack56]

Azure was fine last time I used it, conversion and upload is easy. But not fast enough for POS I wouldn't think. As arnelgp has suggested, you could download stock/barcodes/prices to a local table before starting each day.
If your users via the internet need to access a local stock table at POS speeds, then you could approach it like this:
a) take a snapshot copy of the stock table each morning into a new table on Azure
b) always download the snapshot and not the 'real' table for your local POS table, and
c) ensure that remote users are forced to download the snapshot when they startup.
However, if remote users are doing low volume transactions, they could probably use the snapshot table on Azure directly.

 

[Ihk]

Thank you very much. @Auntiejack56 and @arnelgp
Actually I did not know about snapshot this way. I must look into this in detail.

 

[The_Doc_Man]

Here is the problem in a nutshell. Access does all of the work in the computer hosting the FE files UNLESS you have an SQL engine as the BE - and even then, you have to do some work to assure that you minimize the work done in the FE. As long as you are sticking with an Access FE, your only real choices are systems that support Server Message Block protocols (for native Access BE connections) or some variant of ODBC protocols (for active SQL connections). Either way, you are going to beat up your network.

If you are sticking with native Access BE files, you cannot expect success AND reasonable speed with anything other than hard-wired Internet links. Using WiFi or using Wide Area Networking (which might transparently include wireless connections) is a recipe for disaster in the form of database corruption. Native Access is NOT tolerant of network connection loss. Network loss can lead to corruption which in turn can lead to data loss.

Using ODBC over longer distances including WiFi and WAN will be less likely to destroy your DB, but you would still have potential issues with lost transactions. Speed will also be an issue. The more network hops from FE to BE, the slower it is likely to get.

If this is a POS situation, you cannot afford database corruption that leads to data loss. The uncertainty of continuous connection for WiFi and WAN would make those types of network questionable. Further, speed would be important because you don't want customers to have to stand around waiting for the POS terminal to finally confirm the transaction.

I want to stick with ms access as front end, because I am used to it and its ease.

I want if users have their front end they should be able to login to perform tasks from any where via internet connection.

I am looking for cheapest possible option.

We all want that combination but if that is the "big-I" internet, it is unlikely to be possible. Within a company intranet, maybe it could work. If you really mean "anywhere that has internet" then Access is not the tool for you. This is a case of the right tool for the right job, and Access isn't that tool. You will have to reduce your expectations or find something that is more tolerant of network glitches. That usually means web-based applications which are designed with different protocols that can withstand an occasional net dropout.

A few articles on this forum have discussed some cloud-based services that appear to do this with Access, though usually it is done via clouds and mirrors - and isn't cheap. Look through the "Similar Threads" list below this thread because some of the discussions are directly related to what you requested.

 

[Ihk]

Here is the problem in a nutshell. Access does all of the work in the computer hosting the FE files UNLESS you have an SQL engine as the BE - and even then, you have to do some work to assure that you minimize the work done in the FE. As long as you are sticking with an Access FE, your only real choices are systems that support Server Message Block protocols (for native Access BE connections) or some variant of ODBC protocols (for active SQL connections). Either way, you are going to beat up your network.

If you are sticking with native Access BE files, you cannot expect success AND reasonable speed with anything other than hard-wired Internet links. Using WiFi or using Wide Area Networking (which might transparently include wireless connections) is a recipe for disaster in the form of database corruption. Native Access is NOT tolerant of network connection loss. Network loss can lead to corruption which in turn can lead to data loss.

Using ODBC over longer distances including WiFi and WAN will be less likely to destroy your DB, but you would still have potential issues with lost transactions. Speed will also be an issue. The more network hops from FE to BE, the slower it is likely to get.

If this is a POS situation, you cannot afford database corruption that leads to data loss. The uncertainty of continuous connection for WiFi and WAN would make those types of network questionable. Further, speed would be important because you don't want customers to have to stand around waiting for the POS terminal to finally confirm the transaction.







We all want that combination but if that is the "big-I" internet, it is unlikely to be possible. Within a company intranet, maybe it could work. If you really mean "anywhere that has internet" then Access is not the tool for you. This is a case of the right tool for the right job, and Access isn't that tool. You will have to reduce your expectations or find something that is more tolerant of network glitches. That usually means web-based applications which are designed with different protocols that can withstand an occasional net dropout.

A few articles on this forum have discussed some cloud-based services that appear to do this with Access, though usually it is done via clouds and mirrors - and isn't cheap. Look through the "Similar Threads" list below this thread because some of the discussions are directly related to what you requested.

thank you very much. very helpful information.

 

[CJ_London]

sorry to say, if you want performance and you want it on the web you will have to pay. I agree with Auntie Jack - suspect a local FE and a web based BE is not a way forward for a POS application.

You say 12 euros per person is too expensive for a terminal server solution - but that is the easiest solution to implement with virtually no cost of conversion and performance very close to a user having the BE on their local machine. And in a POS environment I would have thought that would actually be 12 euros per sale point, not person. Plus can be accessed from any device, not just windows so long as it supports remote desktop.

Any other solution is going to have a higher cost of conversion - how much depends on how well your current app has been designed with performance in mind. But I would hazard a guess it could be as much as several hundred if not thousand times 12 euros.

Not available yet, but MS are beta testing an Access Dataverse Connector. Users connect to a local access BE which is replicated on the web and can be accessed by things like powerBI. Not sure whether this would be appropriate for a POS system, but again will come at a cost. Will almost certainly involve some modification to your existing app since not all datatypes are supported.

Or, if data movements are pretty much all one way - from the user to the cloud, you can look at using data macros to update a cloud db from your local BE. Did this recently for someone and performance was OK from a user perspective since these transactions occurred after the local BE was updated.

Another Question from experienced users:
If i have online backend, do you think for pos (point of sale) this (ms access) will be as good as on local network? Because many transactions on each barcode scan, will it be speedy stll?

No

 

[isladogs]

Not available yet, but MS are beta testing an Access Dataverse Connector.

It has now been completed and is available to all 365 users as of v2204/2205.

However, the latest 365 version 2205 also has two serious bugs both of which I can replicate:
1. linked SQL tables with nvarchar PK fields have the entire table shown as #Deleted (if using the old SQL Server driver)
2. Hyperlink subaddresses have stopped working

Luckily, neither of those affect any users of my production databases

 

[CJ_London]

It has now been completed and is available to all 365 users as of v2204/2205.

don't suppose you have been able to unravel the costs? Think it comes free with a Teams subscription but with some limitations, not sure what those are.

 

[GPGeorge]

It has now been completed and is available to all 365 users as of v2204/2205.

However, the latest 365 version 2205 also has two serious bugs both of which I can replicate:
1. linked SQL tables with nvarchar PK fields have the entire table shown as #Deleted (if using the old SQL Server driver)
2. Hyperlink subaddresses have stopped working

Luckily, neither of those affect any users of my production databases

The dataverse connector appeared in my Access 2019 installation this morning, albeit with the original placeholder icon.

I haven't yet been able to work out how to connect to a Dataverse environment though. And documentation is, as usual, sparse and uncooperative.

 

[isladogs]

I've not tried yet but the Dataverse videos done by Maria Barnes for Access Lunchtime and for DevCon were very good in my opinion

 

[GPGeorge]

I've not tried yet but the Dataverse videos done by Maria Barnes for Access Lunchtime and for DevCon were very good in my opinion

Great videos. Unfortunately they gloss over the point of identifying the endpoint and supplying credentials. She simply shows her already working one.

I only spent a few minutes fumbling around so far, with no success.

 

[isladogs]

I haven't even got started yet as at the moment I don't even have a PowerApps account which is a necessary precursor.

Have you seen the various official MS help files available from

Cloud connectors - Microsoft Support

support.microsoft.com

which itself has many links including

Get started: Migrate Access data to Dataverse - Microsoft Support

support.microsoft.com

and as a starting point

Microsoft Access Dev Center | Database app development

Develop desktop or mobile database apps and hybrid solutions for Access. Use Dataverse connector for Power Platform to integrate with Azure cloud-based storage.

developer.microsoft.com

BTW in 365, the Dataverse icons look like this:

 

[Pat Hartman]

As usual, the structure is clear as mud.
How much is a Power Account?
How much is Azure to host your data.

I'm guessing that Dataverse is going to be a non-starter for reasons similar to SharePoint being a non-starter.

 

[Isaac]

1) I want to stick with ms access as front end, because I am used to it and its ease.
2) I always have multiuser interface, login system

I want if users have their front end they should be able to login to perform tasks from any where via internet connection.
AWS:
To test I tried it as back end on AWS sql and connected via odbc to online sql instance. Performance was very slow.
Sharepoint:
I also looked into online sharepoint, i dont like this list system in sharepoint because all of my backend structure is disturbed, as I think i cant get back (share point list) into my access db like before , if i want some changes in any stage in running app.
AZURE Sql
I have not tried Azure sql, I can do that, if somebody has experience with performance and loading of data.
I am looking for cheapest possible option.
CITREX:
Citrex terminal, virtual server costs 12 euro per user, which is too much because of every user.
I know for best performance, best of all would be virtual server, but again as i know all these service provider costs per user (if musti user want to login).
Suggestion for getting online:::
Please suggest me ideas, otherwise I must have to think for website/native cloud.
POS:
Another Question from experienced users:
If i have online backend, do you think for pos (point of sale) this (ms access) will be as good as on local network? Because many transactions on each barcode scan, will it be speedy stll?
........
Dou you have any other suggestion which serve the purpose and is cost effective, not have too steep learning curve.
Thanks

From what I have heard I would suggest looking deeply into using azure SQL cloud as your back end accessible from anywhere. If set up correctly..
@Minty any ideas he knows a lot about azure

 

[Isaac]

As usual, the structure is clear as mud.
How much is a Power Account?
How much is Azure to host your data.

I'm guessing that Dataverse is going to be a non-starter for reasons similar to SharePoint being a non-starter.

Dataverse is actually the 'mature disciplined parent' compared to SharePoint being a 'toddler with no restrictions' when it comes to database development. Especially for "model apps" type of power apps

 

[GPGeorge]

Dataverse is actually the 'mature disciplined parent' compared to SharePoint being a 'toddler with no restrictions' when it comes to database development. Especially for "model apps" type of power apps

So far, Dataverse has not gained a lot of transaction for Access. Who knows what will happen with Power Platform applications? It seems like something of an open question there.

 

[GPGeorge]

I haven't even got started yet as at the moment I don't even have a PowerApps account which is a necessary precursor.

Have you seen the various official MS help files available from

Cloud connectors - Microsoft Support

support.microsoft.com

which itself has many links including

Get started: Migrate Access data to Dataverse - Microsoft Support

support.microsoft.com

and as a starting point

Microsoft Access Dev Center | Database app development

Develop desktop or mobile database apps and hybrid solutions for Access. Use Dataverse connector for Power Platform to integrate with Azure cloud-based storage.

developer.microsoft.com

BTW in 365, the Dataverse icons look like this:

View attachment 100858

Colin, if you have an MS 365 account with Teams, you should be able to get to the PowerApps section through Teams, IIRC.

 

[GPGeorge]

As usual, the structure is clear as mud.
How much is a Power Account?
How much is Azure to host your data.

I'm guessing that Dataverse is going to be a non-starter for reasons similar to SharePoint being a non-starter.

There's a report today of Dataverse doing something really wonky with currency data when imported from Excel. My own experience with certain datatypes has been less idiosyncratic, but not very pretty. One of these days, when I have an extra 10 or 12 hours, I will try again....