We help IT Professionals succeed at work.

Access Database hosted on Azure or similar to help with present speed issues over the internet.

105 Views
Last Modified: 2019-04-23
Hello everyone! I am wondering if anyone has any guidance, feedback or a good tutorial on taking an existing Access Database and hosting it on an Azure instance, or another short term solution to help speed issues over the internet? We have approval to attempt it ourselves because the speed of the present database, being hosted on a laptop is SUPER slow and basically destroying productivity!  Its my 1st attempt at this type of Azure instance so any start to finish feedback is welcome.
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Access with ACE as the database engine was never designed to run over the internet.   With that said, using SQL server, you can do that.

 But your success relies on how the app was developed.   If not with client/server in mind, then you won't be happy.  Azure is basically SQL Server in the cloud, so the tips here:

https://www.jstreettech.com/downloads.aspx

In "Best of both worlds" applies.   That's a start anyway.

Also, you'll want to re-factor the app to push as much work server side as you can.  That means using Stored Procedures, Views, and Pass-through queries as much as possible.

You want to be minimizing the data going over the wire as much as possible, which requires a different mindset and development over that of a typical Access app.   not that it can't be done and done well, but developing for client server use is different.

Jim.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
By using an Azure db with an Access frontend you are creating a Hybrid database.  As Jim stated it is the same idea as using SQL Server for a backend.  One critical thing is never bind objects to a table, but rather only pull individual records at a time.  You want to minimize the amount of data being pushed/pulled through your internet connection.  I open my form with a recordsource that always returns nothing, then allow the user to make a selection and then return that specific record.

Jack Leach has a few quick articles that may help https://dymeng.com/azure-series-01-intro-to-azure/

Switching to SQL Server or Azure by no way garantees any performance improvement.  In many cases, things actually slow down because application weren't design with such a back-end over a WAN in mind.

Whenever someone talk to me about an Internet database, I always tell them to use proper web technologies: PHP, .Net, MySQL, ... I just finished a major conversion for a client from an Access db to PHP/MySQL because of too many issues with O365/Win10 bugs and limitations with Access and a hybrid database could never come close to the performance that I get with PHP/MySQL.  Added bonus, no software required beyond a web browser!
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Generally your options are:
1. You host your application on Azure instance...you are utilising it via RDP(RemotApp)..you get as good performance as good as  your underlying instance specs... negative ..you need RDS infrastructure (everything is on a single instance)..and of course costly Terminal client licenses..
2. You move your BE to Azure but on a 3rd party engine like Sql server, MySql..etc...the performance now can boost even more as SQL makes far greater resources handling... negative .. complexity and requires quite a bit of coding/optimization..etc..big plus..NO licenses..and if the migration is "good" ...you will get stellar performance.
3. other options like VPN and drive mapping are ...slow....
Armen Stein - Microsoft Access MVP since 2006President, J Street Technology
CERTIFIED EXPERT

Commented:
Jim, thanks for the link to my slide deck.

Daniel, we bind Access forms to SQL Azure tables and views all the time.  It works fine if you keep the number of records (using the Where clause) and fields (not specifying Select *) to a minimum, and let SQL Azure do as much of the processing as possible.

Many businesses don't have the budget to rebuild the entire thing as a web application, when moving the back-end to the cloud will give them an acceptable system for much less money.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
@Jeff,

 
feedback or a good tutorial on taking an existing Access Database and hosting it on an Azure instance,

 Just to be clear on this, is it that you just want to host the database in the cloud, or host the entire app in the cloud?

 John's comment relates to the latter, while everyone else is talking about the former.

Jim.

Author

Commented:
HI guys,

@JimDettman - My hopeful scenario would be to host as much as possible on the Azure side and run Access locally on laptops.  My friend is presently building the web application and its going great but... My client just needs a mandatory quick short term solution to speed things up because its digging into productivity like you read about.  The database is taking minutes at times to propagate because they are running it on a laptop and then all of them try to update in real time. Its a mess! I learned more today about their setup so, the quickest and easiest solution (which I know is subjective) is my goal. by the way forgive me... Access is not my wheelhouse and this is the 1st time I'm attempting anything like it, why I reached out to EE experts!  Sorry for my late response its a day of meetings. Thanks Jim and everyone!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Like Armen, I have several clients who are using Access FE with Azure BE.  I'm rewriting parts of one of those to take advantage of the server side issues mentioned by others here, the other one was designed from the get-go with SQL Server in mind so it did not need much updating.

You might want to look at your customers internet connection as that is probably point which is causing the greatest delay.  If they don't have at least a 1GB pipe between their servers and their Internet Service Provider, that is where they need to start.

Dale
Armen Stein - Microsoft Access MVP since 2006President, J Street Technology
CERTIFIED EXPERT

Commented:
Hi Dale, I disagree on needing that much speed if the Access FE is optimized correctly.  The whole point is to not have that much data moving across the wire.  We have applications that work just fine on slower connections.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Armen,

I don't disagree, but it doesn't sound like the OPs Access db is optimized at the moment, so the quickest thing to work with would be band-width.  and going from 100Mb to 1Gb on an internet connection might not be overly expensive.  with Verizon FIOS, the difference is only about $30/month.

Dale
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
If its done correctly you can have very good output with slow -slow connections..
Take a look at my demo ...(14-15 Mpbs simple ADSL line with 50ms latency...the good old days when my connection was at least decent...now its only 10+mbps as Greece likes to go back in time ) ...about 100+colums/1000 rows (not my design)..not to forget single core VPS with 1 Gb Ram running MySQL

Author

Commented:
Thank a ton everyone for your feedback! The client decided to go with a web app and bypass Azure all together. I learned a whole lot on during this ordeal. . Although Azure seemed to be one of the quickest solutions, for long term this big chain corporate juggarnaut wanted a more scalable solution. Azure also required outside resources and more hands in the pot werent a good thing here.  Thanks again everyone!
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
*Is there a way to award everyone for their feedback here? The solution in my opinion was combination of everyone's comments to come to the conclusion that we alleviate Azure for the process. :D *  Have a great day everyone!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
@Jeff,

Each one of the posts should have options for "This is the solution", "This is helpful", and "This is not helpful" at the bottom of each post.  You should be able to mark each post with one of those three (although I generally don't use the "not helpful" option.  Additionally, you should be able to select multiple posts as "This is helpful".

Dale
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.