Converting Access DB to Web Application (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 09:13
Joined
Oct 10, 2013
Messages
586
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:13
Joined
Oct 29, 2018
Messages
21,357
Hi. Just converting the BE to SQL Server but keeping the FE in Access won't necessarily increase the speed of your database (it might actually make it slower at first). If you must provide wide area access to your database, you may have to bite the bullet and use another platform other than Access.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 28, 2001
Messages
26,999
Given the cost of a code re-write, there is another possible solution. The RDP (Remote Desktop Protocol) methods involve that you log in to a local PC (even a portable laptop works) and then establish an RDP session to a server set up for such connections. You run Access and its app on the server through RDP so that your terminal is just a relay point for a copy of Access on the server. However, since you are not transferring gobs of data across the RDP connection, response should stay reasonable. If you can RDP to the server, you can export the report to a file and download it to the laptop via FTP or SFTP as appropriate.

Technically, when doing multiple RDP sessions like this, you need a multi-user license for Access. You also need to have separate folders for each user on the server because you still need to keep the FE files separate. Note that IT guys don't always understand this requirement. However, if you do this, your remote sessions will work over a distance as though you are coming through a web site. There is also a product called CITRIX that you can get that does the same sort of thing. Look to this forum and posts by Pat Hartman, who has some experience with CITRIX. She can tell you more about it, but go ahead and search the forum for CITRIX and for RDP.

The downfall of this method is that a dropped session is still ugly, but if you have a way to have your session time itself out with the app, you can perhaps make things stable enough that you will mostly avoid BE corruption.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,553
Am I correct that you would have to recreate all queries, forms and reports and rewrite all VBA code?

Yes, but not in Access, you would need to use html and or a mixture of other web based languages

I've used Terminal Server on a number of occasions (on which Citrix is based) - find a net provider. You will pay a license fee per user. The environment is windows and as with your own machine you have a C:\Users folder with all the user profiles stored there. To save on costs, install the free Access runtime. Create a shared folder, put the BE there and a copy of the FE in each user profile (perhaps in their desktop folder). The only other thing to do is relink the FE to the BE and if using runtime, ensure your app has been modified to account for this (users won't have access to the navigation window, ribbon or right click menus) and error handling needs to be in place.

Users log in via remote desktop to gain access to the terminal server. Dropped sessions mentioned by Doc can be a problem (as with your current arrangement) but if the drop is due to loss of connection to the server, your users app will still be running on the server - next time the user connects, the app will still be there, subject to any management of user sessions such as auto logout after a period of time.

Performance wise - probably better than your current setup, almost the equivalent of the users having the BE on a local drive.

Other benefits - not limited to users with windows devices. Any OS that supports remote desktop can use your app, Certainly iOS and Linux

I would consider upgrading your BE from ACE to Sql Server or Express but this is unlikely to produce any performance benefits. See this link if this is the reason you are considering this option. There may be things you can do with your current setup
 

Steve R.

Retired
Local time
Today, 10:13
Joined
Jul 5, 2006
Messages
4,617
I converted my home Access databases to a Web based system. This only involves the users on my local home LAN, where the number of users is essentially one. :) I can access the database from any computer on the LAN. There should be no problem upscaling to serve a small office.

For the database, I am using MariaDB. MariaDB is free to use, no licensing fees. MariaDB is also MySQL compatible.
For the network connection server, Apache.
The graphical interface for managing MariaDB is phpMyAdmin.
The programming is a mixture of SQL, PHP, HTML, CSS, and JavaScript. A virtual headache and a pile for reference material.
For my browser, I am using Firefox.

Rewriting your Access code to use SQL, PHP, HTML, CSS, and JavaScript will be a chore. But one well worth it.
Using this approach also avoids having Access installed on the local computers and maintaining Access on them.
This approach also avoids having having to pay licensing fees for Access. In fact, paying licensing fees to Microsoft can be entirely avoided by installing LibreOffice on each computer. LibreOffice has word processing and spreadsheet capabilities which are perhaps the most heavily used office products.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Sep 12, 2006
Messages
15,613
@Weekleyba

Just a point

It's not critical that it's a small database. A distributed database is difficult. It doesn't matter whether you have 25 users in 5 locations, or 2500 users in 50 locations - changing your database to work in a different way is just as complicated. A database with more tables and processes will just need more work, but don't assume that it's easy to do, because I doubt very much that it is. I know I couldn't.

@Steve R. mentioned the steps he had to do, and I suspect he is understating his talents. There are a lot of skills in what he did for just a single user system, that presumably also doesn't need to be 100% robust and resilient, because it's a system just for him, and he can fix anything that goes wrong.

@CJ_London has also explained what needs to be done, and then explained a different solution allowing all users to connect to a central location. The benefit of this is that there's no real new database development needed. @The_Doc_Man discussed a similar idea.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,553
Another alternative is to look at using sql server Azure or sharepoint as a back end and recently announced there is now an access converter to the MS Dataverse which may be an option.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,970
Am I correct that you would have to recreate all queries, forms and reports and rewrite all VBA code?
Absolutely not. But that doesn't mean you might not need to make changes. If your forms are all bound to tables or queries with no criteria, they will be like straws sucking data across the network or worse, over the internet. Access will not stop sucking until every single row is downloaded to memory or you close the form. Your DBA will hate you for doing this to his network. All forms should be bound to queries and the query should include criteria to select 1 record (for edit forms) or a small number in other cases. The only way to get the server to do the heavy lifting is by using queries with criteria so you severely limit the number of rows and columns returned for each form.

Whenever I create a new app, whether it starts as ACE or SQL Server, I build the app with the ultimate goal of conversion. That means that any app I built, I can convert to SQL Server in an afternoon. And it only takes that long because the app needs to be thoroughly tested. Many people who just convert from Jet/ACE to SQL Server are flabbergasted by how slow the app is. It is slow because they were using old style Access techniques like filters on forms and Select * in their queries bringing down way too many columns and rows. Jet/ACE are incredibly fast and in a sprint will beat SQL Server.

My apps always use linked tables, bound forms/reports, and mostly querydefs with the occasional embedded SQL created by search forms. Rarely, I might need to create a view to optimize a common join. Some operations like bulk deletes will be faster using pass through queries. Some reports were are so complicated that they need stored procedures. Access naturally makes every effort to pass through all queries to the server. You can defeat it so you need to understand the process so you don't get in the way. In bulk testing, you might find that pass through queries are marginally faster than standard Access querydefs but they are not updateable so for a small speed gain, you give up bound forms/reports. Not a good tradeoff in my book.

However, Remote desktop is a viable solution. It comes in two flavors. You connect to a physical PC in the office or you run a server and a session is created for you on the server to emulate a desktop. Citrix is another solution with a little more sophistication but it is based on the RD technology.

@Steve R. Just because you can link tables to a database somewhere over the internet, doesn't mean that you can work with the app. I've done it will SQL Server and Azure and in both cases it was like watching paint dry and both times, the apps were already using SQL Server on a LAN with excellent performance.
 
Last edited:

Steve R.

Retired
Local time
Today, 10:13
Joined
Jul 5, 2006
Messages
4,617
@Steve R. Just because you can link tables to a database somewhere over the internet, doesn't mean that you can work with the app. I've done it will SQL Server and Azure and in both cases it was like watching paint dry and both times, the apps were already using SQL Server on a LAN with excellent performance.
My apologies for not being clearer. Under what I was suggesting, there would be no "... link tables to a database somewhere over the internet, ...". The database would be on a "server" and accessed directly by connecting through a browser. In my home LAN configuration, accessing the the database is exactly the same as accessing your common every-day internet webpage. Or to rephrase, I am running my own web server in my house. The reach of my "internet" is just those computers within my LAN. I also did not pick-up on the fact that: "The users are in 5 different cities, in two states.", which is clearly beyond being limited to a local LAN. That would imply the need to use an ISP and/or VPN, which is unfortunately above my pay-grade due to the lack of experience.

As a follow-up note, concerning a small office. I missed mentioning the need for security. For my home use, security was not a concern. Clearly, not good for an office application. Security would be a major concern. So @Weekleyba would have to incorporate security in the event that a "full" web-based style solution is pursued.
 
Last edited:

oleronesoftwares

Passionate Learner
Local time
Today, 07:13
Joined
Sep 22, 2014
Messages
1,159
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.
What ms access version are you on?
 

Isaac

Lifelong Learner
Local time
Today, 07:13
Joined
Mar 14, 2017
Messages
8,738
I've had good luck paying $25/mo for a VPS from IONOS (formerly 1&1) with concurrent logins
But if it were more than a small handful of users, I'd probably use AWS desktops, if I didn't despise Amazon as an entity...so probably more something else like it.

As for the FE to webapp, yes - you would have to re-create everything in whatever your platform-of-choice for the new FE was on the web enabled types of things - and in fact, I wouldn't even call it a "conversion", it wouldn't even be close.
The best you could do is "use" (if you can call it that) your existing Forms, Queries, Controls etc as a starting-point for your requirements - give it to a technically capable Business Analyst to write the req's.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,553
over the years I have written numerous apps for clients as a stop gap because their existing systems don't meet new requirements and the cost/time to implement is not acceptable, particularly if the number of users affected is relatively small. Those apps tend to have a life of 2-3 years before the corporate scale systems can catch up and my apps are often used as a model for how these systems need to be modified to be fit for purpose.
 

Sun_Force

Active member
Local time
Today, 23:13
Joined
Aug 29, 2020
Messages
396
The other day I had a chance to visit a company. They had 3 offices in 3 different cities.
But when i launched Windows Explorer - Network, All PCs in all 3 places were listed. I could access the shared folders of each PC, even those from other offices, placed in other cities.
There was an Access BE located on one of the PCs, and each user had a FE to communicate with the BE.
There were also some Excel files, thousands of drawings (saved as pdf) that was shared on a PC's shared folder and anyone could use them.

Someone told me it's a system managed by their ISP and they're paying a lot for this each month, but he wasn't one of IT member. So I'm not sure how true is it.
Is it what CITRIX does? Does anyone have any idea how such a system can be built? Or what it's called?

thank you.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,970
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. Up until then, they were using Citrix for the remote locations. They still use Citrix to allow remote access to the app. I never asked what that cost but it probably is expensive. But money is relative. If it improves productivity, most companies are happy to pay for the convenience.
 

Thales750

Formerly Jsanders
Local time
Today, 10:13
Joined
Dec 20, 2007
Messages
2,061
Another alternative is to look at using sql server Azure or sharepoint as a back end and recently announced there is now an access converter to the MS Dataverse which may be an option.
Do you know where to find out more about this? Thanks.
 

GPGeorge

Grover Park George
Local time
Today, 07:13
Joined
Nov 25, 2004
Messages
1,775
Another alternative is to look at using sql server Azure or sharepoint as a back end and recently announced there is now an access converter to the MS Dataverse which may be an option.
The new Dataverse option for MS Access is actually a "connector" not a "converter", but yes, it is an option alongside a SQL database and SharePoint. In other words, you will be able to connect your Access Front End to Dataverse tables, just as you would SQL Azure or SQL Server tables. I think the "converter" idea might have come from the Ignite presentation where they did sort of blur that point a bit.

I haven't yet done much with Dataverse, but our AUG chapter had a presentation from MS on the 2nd of December, in which Michael Aldridge explained as much as he could do in an hour. I am also aware of a presentation he did for the Denver Area Access User Group on the same topic, which is now available as a Youtube video. As soon as I finish editing the video from our meeting, it'll be on Youtube as well.
 

GPGeorge

Grover Park George
Local time
Today, 07:13
Joined
Nov 25, 2004
Messages
1,775
The new Dataverse option for MS Access is actually a "connector" not a "converter", but yes, it is an option alongside a SQL database and SharePoint. In other words, you will be able to connect your Access Front End to Dataverse tables, just as you would SQL Azure or SQL Server tables. I think the "converter" idea might have come from the Ignite presentation where they did sort of blur that point a bit.

I haven't yet done much with Dataverse, but our AUG chapter had a presentation from MS on the 2nd of December, in which Michael Aldridge explained as much as he could do in an hour. I am also aware of a presentation he did for the Denver Area Access User Group on the same topic, which is now available as a Youtube video. As soon as I finish editing the video from our meeting, it'll be on Youtube as well.
I am relatively inexperienced here at AWF. What is the policy on posting links, such as to the YouTube videos I mentioned here?
 

oleronesoftwares

Passionate Learner
Local time
Today, 07:13
Joined
Sep 22, 2014
Messages
1,159
Please check the link below, there is a video and tutorial on it, while it talks about creating a new web app, you might find some ideas on how ms access web apps operate.

 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:13
Joined
Oct 29, 2018
Messages
21,357
I am relatively inexperienced here at AWF. What is the policy on posting links, such as to the YouTube videos I mentioned here?
I don't know the policy either, but I have seen people do it here. For example:

 

GPGeorge

Grover Park George
Local time
Today, 07:13
Joined
Nov 25, 2004
Messages
1,775
Please check the link below, there is a video and tutorial on it, while it talks about creating a new web app, you might find some ideas on how ms access web apps operate.

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.
 

Users who are viewing this thread

Top Bottom