Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
I need Access/Azure advice
Hello All
I was hoping to get some advice. As many of you know I have an Access database which I have split with the backend going onto Azure and everyone getting a front end on their computer. We got a third party to convert all the tables into Azure SQL and all the queries into stored procedures. They also did a bunch of other things that are way above my pay grade. I am ok with Access VBA but not with SQL and stored procedures.
I took my original version and put the tables only onto another Azure server while leaving everything else in Access (so all my queries are in the Access frontend). It runs painfully slow. Correct me if I am wrong but even though none of the forms are directly attached to any table I think all the data has to come over before the queries restrict things (which would explain the slowness I think)? The users want to be able to run the Access front end from any computer which is why I figured I had to put the backend on Azure.
Here are my questions:
1. Is it possible to have just the tables up on Azure with the frontend (including the queries) on each person's computer but get it to run faster?
2. Is there another way to provide access to the program from any computer but maybe use a different connection method? For example could I put everyone's front end on the Azure server along with the backend data and give them some sort of remote access? I figure I could beef up the Azure server to handle the extra traffic?
So it comes down to the fact that I need to speed the program up, keep the program available to users from any computer, all without using stored procedures.
Thank you all!
I was hoping to get some advice. As many of you know I have an Access database which I have split with the backend going onto Azure and everyone getting a front end on their computer. We got a third party to convert all the tables into Azure SQL and all the queries into stored procedures. They also did a bunch of other things that are way above my pay grade. I am ok with Access VBA but not with SQL and stored procedures.
I took my original version and put the tables only onto another Azure server while leaving everything else in Access (so all my queries are in the Access frontend). It runs painfully slow. Correct me if I am wrong but even though none of the forms are directly attached to any table I think all the data has to come over before the queries restrict things (which would explain the slowness I think)? The users want to be able to run the Access front end from any computer which is why I figured I had to put the backend on Azure.
Here are my questions:
1. Is it possible to have just the tables up on Azure with the frontend (including the queries) on each person's computer but get it to run faster?
2. Is there another way to provide access to the program from any computer but maybe use a different connection method? For example could I put everyone's front end on the Azure server along with the backend data and give them some sort of remote access? I figure I could beef up the Azure server to handle the extra traffic?
So it comes down to the fact that I need to speed the program up, keep the program available to users from any computer, all without using stored procedures.
Thank you all!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't know much about how access queries data i.e. where the selection/filtering is done. With a SQL solution one typically creates your command and sends the command to be executed on the server and the server returns the results of the command. What I probably would do in your shoes is get visual studio community edition and build a Lightswitch application.
On re-reading your question I see that you have stored procedures already, so you simply have to within access or a web application, set up the parameters for the query, invoke the stored procedure, collect and view the results.
On re-reading your question I see that you have stored procedures already, so you simply have to within access or a web application, set up the parameters for the query, invoke the stored procedure, collect and view the results.
"all the data has to come over before the queries restrict things"
You are quite correct, M$Access.Classic only supports a Client side cursor - no way around it.
M$ is heavily committed to upgrading all its database functionality to SQL Server in the long run. As a consequence, my most humble recommendation is you bite the bullet and get up to speed with SQL Server Stored Procedures as best you can.
There is a wealth of FREE courses available on youtube / the internet as well as expert assistance. If you have the money, buy a video course. Or, if you have the time enroll into a teacher led course at your local community college or accelerated bootcamp.
Transact SQL is a bit intimidating at first. But, with due diligence and patience you will have it down quickly enough.
Cheers!
You are quite correct, M$Access.Classic only supports a Client side cursor - no way around it.
M$ is heavily committed to upgrading all its database functionality to SQL Server in the long run. As a consequence, my most humble recommendation is you bite the bullet and get up to speed with SQL Server Stored Procedures as best you can.
There is a wealth of FREE courses available on youtube / the internet as well as expert assistance. If you have the money, buy a video course. Or, if you have the time enroll into a teacher led course at your local community college or accelerated bootcamp.
Transact SQL is a bit intimidating at first. But, with due diligence and patience you will have it down quickly enough.
Cheers!
"all the data has to come over before the queries restrict things"It depends what you mean by "all" the data. Access attempts to make EVERY query a pass-through query. That means the query along with its selection criteria is sent to the server and Access only gets back the selected data. So if your query selects a single record, that is all that SQL server returns. If your query has no selection criteria then you will get the entire table or the full joined results so you have control over how much data is actually retrieved. There are things you can do to defeat Access and prevent it from sending the query to the server for processing. For example, joining a SQL Server table to a local Jet/ACE table will result in Access requesting that all rows from the server side table be returned and Access will perform the join locally. The other big problem is using User Defined Functions or VBA functions which have no SQL Server equivalent. Since SQL Server doesn't know anything about VBA function, Access again has to request all data from the server and perform the query locally where it can use VBA to run the functions.
You are quite correct, M$Access.Classic only supports a Client side cursor - no way around it.
Even though Access attempts to make all your queries "pass through", it chops them up and returns chunks of data at a time. The process can be slow so the fewer records you return at one time the better. All of my update forms have selection criteria to limit the main form records to only one record. Reports should be bound to local "pass-through" queries or stored procedures. Use views whenever possible to force joins to be done on the server.
Using good client/server techniques, Access apps will be fine against databases stored on your LAN. Once the database is in the cloud, all bets are off because the inherent speed of the internet is at best about 10% of the speed of a good LAN. Microsoft wasted a lot of time developing web apps when they would have done us a better service by optimizing the Access interaction with SQL Server/Azure in the cloud.
People who take an Access app designed in the old school methodology of filtering and convert it to SQL server are always surprised by how slow the app becomes. Access is tightly integrated with Jet/ACE but not with other RDBMS so you need to change your techniques for creating forms and large batch processes. But, apps developed using good techniques will work well whether the BE is SQL Server or Jet/ACS as long as the BE is on the same LAN as the FE.
The only viable solution to share Access apps across a WAN is to use Citrix or RDP. You can host these yourself or use a third-party service. It gets expensive fast though and I don't know the current prices but once you get more than about 10 users, you probably are better off hosting Citrix yourself.
This is very good advice everyone! I really appreciate it. Pat you mentioned Citrix. I am going to look into that, but would that speed up everything and would that make the program available to the users from any computer (sorry for the questions but this is not my forte at all)
Access applications run by Citrix frequently out perform Access apps run the "normal" way. The reason is that the application actually runs on the server so the FE and BE are on the same computer (put the BE on the Citrix server) so no network latency is involved. Citrix runs in a browser and all the local computer sees is "pictures" of what the app screen is showing on the server. The only data transfer is keystrokes from the browser client sent to the app running on the Citrix server. I have had apps where the Citrix app was hosted in Connecticut and we had users from California to France.
One thing to watch out for is that Citrix administrators don't understand Access and have a tendency to set up the environment to share the FE. The FE MUST NOT BE SHARED. Make it clear so they understand it. Every user MUST have his own copy of the FE. I use a batch file in the Citrix environment that the users run from a shortcut. The batch file copies the "master" copy of the FE to each user's local Citrix home directory and then opens it. I run similar batch files for my LAN users. The users click a shortcut. The shortcut runs the batch file and the batch file copies the "master" copy of the FE to the specified directory and opens it. That provides a seamless installation and the users get a fresh copy of the FE each time they open the app which completely eliminates any bloating problem you might have created by using poor techniques.
One thing to watch out for is that Citrix administrators don't understand Access and have a tendency to set up the environment to share the FE. The FE MUST NOT BE SHARED. Make it clear so they understand it. Every user MUST have his own copy of the FE. I use a batch file in the Citrix environment that the users run from a shortcut. The batch file copies the "master" copy of the FE to each user's local Citrix home directory and then opens it. I run similar batch files for my LAN users. The users click a shortcut. The shortcut runs the batch file and the batch file copies the "master" copy of the FE to the specified directory and opens it. That provides a seamless installation and the users get a fresh copy of the FE each time they open the app which completely eliminates any bloating problem you might have created by using poor techniques.
That is very interesting about Citrix I am going to talk to the people here to see if that is something they may want to invest in. I also was curious with Azure what if I used Remote App? Would that be similar to what Citrix provides?
I started reading up and watching some videos and it seems to be similar to me.
THank you!
I started reading up and watching some videos and it seems to be similar to me.
THank you!
Citrix is based on RDP so yes, it would work and might be less expensive. I'm not sure what Citrix brings to the table above and beyond RDP but it might be the number of concurrent users it supports.
I know I'm late to the party and the question is closed, but I wanted to add that we actually do have a complex Access app that runs locally with a SQL Azure BE. It's definitely slower than LAN of course, but acceptable. It uses bound forms, but with very small recordsets for updatable forms, and passthroughs for read-only forms and reports. As Pat says, ODBC optimizes Access queries quite well. The techniques we use are described in my presentation Best of Both Worlds on our free downloads page: www.jstreettech.com/downloads.
Cheers,
Armen
Cheers,
Armen
Premium Content
You need an Expert Office subscription to comment.Start Free Trial