Converting Access DB to Web Application

[oleronesoftwares]

Unfortunately, that article is out-of-date, but still available. Access Web Apps were deprecated. That means they are no longer viable. They are not supported on an MS hosted O365 option. I understand, however, that if an organization has its own internally hosted and run SharePoint site, they can, optionally, still turn on support for Access Web Apps. I know of one or two such organizations, one in the Netherlands I believe. Otherwise, AWAs are not an option in 2021 and beyond.

Thanks for the update

 

[Auntiejack56]

I have a small database that is split and used by 25 users. The users are in 5 different cities, in two states. Currently the BE is on a server and each user has a copy of the FE on there own PC.
I've been looking into converting the Access DB to a web application.
My reason for possibly converting is greater accessibility for the users are they travel and hoping for some increase in speed of the database.

Converting the tables to SQL Server does not seem to be too difficult but, converting the FE to a web application appears to be a huge undertaking.
Am I correct that you would have to recreate all queries, forms and reports and rewrite all VBA code?
If so, is it common to do this or do most stick with an Access database?

Would it be worth just converting the tables to SQL Server and leaving the FE on each users PC?

Again the goal would be to increase the speed of the database and increase the accessibility.

Thoughts? Pro & Cons?

FYI, we (and presumably other people) can take your Access app and implement in the cloud with little change (we introduce a startup form). You can continue developing and pass code to us for release (we have Access team developer version management inhouse), see www.bluebridge.com.au. As others have suggested, in a solution like ours, RDP is the main part of the answer, but the other part is the any-device client which allows your users to use phones and iPads as well as PC. Our cloud provider has servers in the US.
Jack

 

[CJ_London]

Do you know where to find out more about this? Thanks.

Best to just google/bing 'sharepoint' or 'sql azure'

Personally I would go with azure rather than sharepoint, tho much simpler is to go with RDP. Dataverse (@GPGeorge sorry, yes I meant connector) is new and looks interesting, cost to users all look to be around the same amount. Important thing is to know you have a way back if things don't work as expected (i.e. you can retrieve all your data from the cloud in a meaningful way, not just reports) and a way forward in the event you need to expand capacity in some way (such as data storage/number of users/connectivity)

 

[The_Doc_Man]

@Auntiejack56 - "we (and presumably other people) can take your Access app and implement in the cloud with little change"

This statement, because it obviously glosses something over, is misleading as written. I certainly don't doubt that you can help people spread out their app using RDP as the communications backbone. But Access doesn't work directly to cloud servers because such servers, so far as I have ever found, don't usually allow Server Message Block protocols, which are the sine qua non for Access operations. Access to the "traditional" file-server cloud is a good way to corrupt databases.

I do not wish to discredit anything else you said, either. I'm quite sure you take care of your customers. But using RDP isn't using "the cloud" because RDP was around before cloud servers existed. And Access doesn't work well (if at all) in "the cloud."

 

[Auntiejack56]

Well, I don't agree that it's misleading, because we take the Access app, and implement it in the cloud. That's what we do. The app is in the cloud. The data is in the cloud. You get a login and password and bob's your uncle. You have 25 users, then you get 25 logins.

I guess it does gloss something over - the fact that the front end is in the cloud too. And we always migrate data to SQL Server, which all our customers require (we don't retain data backends in Access). We send the database and the new app version back to you for continued development (unless we do the app build ourselves, which for NFPs is usually free).

But I did try to make clear that we manage the front end if it's a client build, and work with the client as though we were the Release Manager on the team. We check all releases, and spurious changes are the subject of alerts which we feed back to the client for approval. For example, the CR comes to us with the new FE - if we find that the FE changes don't match the CR we alert the client. This can happen when a developer encounters a bug during testing, fixes it to enable the test to complete, and forgets to alert the team leader that another object has been changed. In which case, the CR comes to us, and that's when we find a change in an unrelated object. (In fact, we always alert the client to the result of our check, with a report that says "Your CR said [blah blah], and your changes are [blah blah to objects blah blah], and they do (or do not) correspond with CR requirements").

So, yes, we do take care of our customers, thanks for that. And RDP is underneath our partners any-device thin client connection apps. I think what I said was Ok, if a little brief.

Jack

 

[Auntiejack56]

All that said, if we are not describing our offering correctly, then your comments are appreciated and we'll be taking them onboard - we don't pretend to have a monopoly on clarity, but we try.

Jack

 

[Pat Hartman]

What are you converting the FE to?

 

[Auntiejack56]

We use the existing Access FE or build one for the client. If existing, we bypass any relinking routines in the startup, and use our own.

 

[Pat Hartman]

So, if you are using an Access FE, are you ensuring that each user gets his own personal copy?
What is the point of converting the BE to SQL Server if your service is using RDP? Just a straight conversion to SQL Server frequently slows an app down and can require lots of modifications to forms and reports to regain the lost speed.

Why not be honest about what you are doing rather than telling people you are converting Access to a web app?

 

[Steve R.]

I am probably on a different tangent concerning: "Converting Access DB to Web Application".
As several posters expressed, what is your end game (ultimate product)? Likewise, I am confused.
Note the comment from @Pat Hartman below where multiple office locations can be incorporated into the same LAN. That seemingly would be a solution.

I have a client with 6 offices from Virginia to Connecticut and a couple of years ago, they added them all to the same LAN. It sure is convenient and doesn't seem to be slow when using the Access app

Should you wish for something more, consider fully diving into developing a 100% web based application. That would mean dispensing with Access and re-writing all your code. A time consuming effort, with a substantial learning curve. As a test, you could establish a simple web-based app (based on your existing structure) to evaluate whether pursuing that approach would be worthwhile in meeting your objectives.

Why that approach? It would, in theory, avoid having to deal with a variety and layers of software applications to "force" your Access application to communicate with your database. Simply put, the more interfaces between the client and the server, the greater potential for failure. A "direct" connection between the client and server should be efficient and eliminate those potential failure points..

 

[The_Doc_Man]

We use the existing Access FE or build one for the client. If existing, we bypass any relinking routines in the startup, and use our own.

OK, if your procedure includes some type of RDP login to a remote FE, you are part of the way there. If you are dealing with a shared FE/BE situation, and if you don't have separate FE's per user, you still risk massive corruption due to destructive interference based on file locking contention. However, if the FE is (a) in the cloud and (b) its BE is at least linked over the same LAN as the FE, then you DO have a safer pathway, because that local LAN doesn't depend on cloud protocols. I understand your configuration better now, thanks for the clarification.

However, to avoid corruption, there is still the question of what you do if your customer's path to the cloud drops momentarily. Because at that moment, you have a potentially locked disk block or two that cannot be updated until the locks are resolved. This is how corruption starts, even on a purely local in-house network, a "true" LAN.

 

[Auntiejack56]

So, if you are using an Access FE, are you ensuring that each user gets his own personal copy?
What is the point of converting the BE to SQL Server if your service is using RDP? Just a straight conversion to SQL Server frequently slows an app down and can require lots of modifications to forms and reports to regain the lost speed.

Why not be honest about what you are doing rather than telling people you are converting Access to a web app?

At no time did I say or imply that we convert Access to a web app.

I'm not trying to hide the fact that we develop in Access - we are proud of it. The app is in the cloud, it's an Access application which we take from the client, or build for the client, and implement in the cloud on a virtual machine.
This method keeps the costs to a minimum (zero dollars to build apps for some clients as I noted), and dev time to a matter of weeks, with a pilot up and running in days.

Some of our clients who have money to spend on family therapy, child and adolescent support etc have 12 month grants to see if the evidence supports an ongoing govt spend - they have to have an app running in days rather than months, and then in 12 months, if there is no more money, they have to discard it. A $40k spend on an app that might take 2 months to build and then be dumped is not going to cut it. The usual workaround is to have therapists filling in paper forms, sometimes from memory, and consolidating data into temporary spreadsheets in which they struggle to create formula and report accurately.
We are trying to assist, and we don't do so by being dishonest.

 

[Auntiejack56]

OK, if your procedure includes some type of RDP login to a remote FE, you are part of the way there. If you are dealing with a shared FE/BE situation, and if you don't have separate FE's per user, you still risk massive corruption due to destructive interference based on file locking contention. However, if the FE is (a) in the cloud and (b) its BE is at least linked over the same LAN as the FE, then you DO have a safer pathway, because that local LAN doesn't depend on cloud protocols. I understand your configuration better now, thanks for the clarification.

However, to avoid corruption, there is still the question of what you do if your customer's path to the cloud drops momentarily. Because at that moment, you have a potentially locked disk block or two that cannot be updated until the locks are resolved. This is how corruption starts, even on a purely local in-house network, a "true" LAN.

If the customer's path to the cloud drops momentarily (one customer is in remote central Australia, and this happens frequently), then the Access app isn't aware of it. The cloud connection is resumed, or worst case the customer logs in again, and the app appears to them exactly as it was when they left.
The RDP is managed thru a 3rd party citrix-like client, the licence for which is included in our monthly fee, and it's a cracker - very pleased with it. Versatile and powerful, we can completely lock clients out of the machine unless they are using our published app, we can change apps depending on device, do auto logouts, monitor usage in real time etc etc. Citrix licences are charged on a minimum number of users, but the 3rd party we use is wholesaled through our provider so we can charge a client a few dollars for 1 licence, multiplied by any number of users.

 

[Auntiejack56]

So, if you are using an Access FE, are you ensuring that each user gets his own personal copy?
What is the point of converting the BE to SQL Server if your service is using RDP? Just a straight conversion to SQL Server frequently slows an app down and can require lots of modifications to forms and reports to regain the lost speed.

Why not be honest about what you are doing rather than telling people you are converting Access to a web app?

To your other points, yes, of course each user gets their own copy of the FE.

The point of converting the BE to SQL Server is security.

Yes, converting straight to SQL Server can slow an app down, but the apps I have worked with (quite a few!) have rarely suffered this way - I think 'frequently' is an overstatement. In any case, if there are mods necessary, then we isolate them and fix using fairly standard methods. As another example, multi-select fields in Access won't work in SQL, but we have a standard fix we can plug in quite easily; it effectively simulates the multi-select.
That said, customers are attracted to us because we build the app so quickly and inexpensively, and offer high levels of data security. Putting on inhouse builds, which might involve modifications to the existing app (and hence setup costs > 0), is certainly well within our scope, but not our primary focus.

 

[The_Doc_Man]

@Auntiejack56 - At least in theory, a "reconnect" is possible for some connection-oriented interactive protocols, which would negate the issue of a dangling session after a drop. So I will not give you too much grief over that configuration. Forgive me if I had a knee-jerk reaction to a sentence that contained the words "Access" and "Cloud" together, since most of the cases where that occurs tend to not work worth a darn. Yours may be an exception.

 

[Auntiejack56]

@Auntiejack56 - At least in theory, a "reconnect" is possible for some connection-oriented interactive protocols, which would negate the issue of a dangling session after a drop. So I will not give you too much grief over that configuration. Forgive me if I had a knee-jerk reaction to a sentence that contained the words "Access" and "Cloud" together, since most of the cases where that occurs tend to not work worth a darn. Yours may be an exception.

 

[Pat Hartman]

Yours may be an exception.

It seems to be the typical RDP solution except they convert the BE to SQL Server whether it needs to be or not and they may modify the FE in the process which now means that THEIR FE is different from YOUR FE.

My problem is selling the product as a "cloud" solution is somewhat disingenuous.

You could probably get the same consumer base with an honest sales pitch and only have to do the BE conversion once when the app is first uploaded. If you give the client back the converted BE and FE, the client can make future modifications to the FE by himself.

How long to you take to turn around a FE update? Do you offer a service level agreement that covers that? What about BE modifications?

 

[Auntiejack56]

It seems to be the typical RDP solution except they convert the BE to SQL Server whether it needs to be or not and they may modify the FE in the process which now means that THEIR FE is different from YOUR FE.

My problem is selling the product as a "cloud" solution is somewhat disingenuous.

You could probably get the same consumer base with an honest sales pitch and only have to do the BE conversion once when the app is first uploaded. If you give the client back the converted BE and FE, the client can make future modifications to the FE by himself.

How long to you take to turn around a FE update? Do you offer a service level agreement that covers that? What about BE modifications?

Sorry Pat, I'm having trouble following your thinking, and I feel that you are using the word 'honest' a little too freely.
Your questions are good ones, but we are straying away from Access and I think we should conclude our discussion in the forum. Our website has a contact for further questions, which I'd love to answer. In brief, about 30 mins, yes, and BE mods are by us or client as appropriate.

 

[Pat Hartman]

Sorry, I was looking at the title of the thread and thought you were the one who started it.

Converting Access DB to Web Application​

 

[strive4peace]

hi @Weekleyba,

MetaMicro has services to convert Access databases to the cloud, which they've done successfully for others. Access Web Apps are still alive, on servers such as theirs. There are other possibilities such as PowerApps, simple webbrowser forms, and remote desktop.

They’ve also developed an impressive way to run VBA from the cloud, so its possible that a lot of your logic can stay in VBA.

If you’re interested in exploring their possibilities in a free consultation, contact Info@metamicro.nl

Here's their website. It's in Dutch -- there's an English link at top to translate.
https://metamicro.nl/