Avatar of alevin16
alevin16
Flag for United States of America asked on

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!
Microsoft AccessRemote AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
wsh2

8/22/2022 - Mon
SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
wsh2

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alevin16

ASKER
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)
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alevin16

ASKER
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!
SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
alevin16

ASKER
Excellent advice!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Armen Stein - Microsoft Access MVP since 2006

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
wsh2

@armen

Your link is great!

Thank you for your scholarship.

www.jstreettech.com/downloads

Bill.