Solved

I need Access/Azure advice

Posted on 2016-07-24
11
67 Views
Last Modified: 2016-07-27
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!
0
Comment
Question by:alevin16
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 125 total points
ID: 41727120
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.
0
 
LVL 14

Assisted Solution

by:wsh2
wsh2 earned 125 total points
ID: 41727415
"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!
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41727958
"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.
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.

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.
0
 

Author Comment

by:alevin16
ID: 41728062
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)
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41728111
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.
1
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:alevin16
ID: 41728222
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!
0
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 125 total points
ID: 41728247
Yes remote app would work..
1
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41728302
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.
0
 

Author Closing Comment

by:alevin16
ID: 41728488
Excellent advice!
0
 
LVL 9
ID: 41732109
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
1
 
LVL 14

Expert Comment

by:wsh2
ID: 41732322
@armen

Your link is great!

Thank you for your scholarship.

www.jstreettech.com/downloads

Bill.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now