Behavior of Access VBA with Azure SQL

[KACJR]

I come again to the Well of Knowledge...

Just a question, actually. Since moving our SQL backup from an on-prem SQL Server to Azure SQL, the performance of our Access front-ends has slowed dramatically. We were warned and expected this to be an issue. However, I was not expecting VBA processing to create completely wacko results randomly.

As a example, I have queries that gets run during the generation of a report. When I view the query manually, I see the results I was expecting. However, when that same query is executed as a part of a VBA Sub (DoCmd.OpenQuery), I get no records. I have also replaced the same query within the Sub as a DB.Execute statement; still no love. I can't explain this.

I have had similar issues with other queries, whether executed using DB.Execute or as a DoCmd.OpenQuery, that produce different results, sometimes correct results, some completely out of left field (no results, partial results, etc).

What I have learned is that by putting a one-second delay after the query, most queries produce correct results. Some still do not, though.

Is it possible that queries in Access are executed asychronously? Meaning a query is launched and executed while the VBA code continues without waiting for the query to complete. Because this is what it all looks like.

The complete randomness (sometimes it works, sometimes it does not) makes this one difficult to diagnose.

Ken

 

[DHookom]

I doubt I have an answer but do have questions. Are these action queries? Do they need to allow edits? Can they be pass-through queries?

Why are you opening queries? Is this part of your user interface?

I would attempt to move as much work as possible to the server.

 

[Minty]

We do lots of Azure SQL back end work.
Can you post up the actual code used to open the reports, and if possible the SQL of the Access source queries?

 

[KACJR]

I doubt I have an answer but do have questions. Are these action queries? Do they need to allow edits? Can they be pass-through queries?

Why are you opening queries? Is this part of your user interface?

I would attempt to move as much work as possible to the server.

Yes. Data is massaged. Several queries do inner joins to pull in data from multiple tables.

 

[GPGeorge]

In order for anyone to offer specific suggestions about the impact of your VBA on performance with a SQL Azure back end, we need to see that actual VBA so we can try to identify the bottlenecks in it.

Thank you for helping us help you.

 

[KACJR]

In order for anyone to offer specific suggestions about the impact of your VBA on performance with a SQL Azure back end, we need to see that actual VBA so we can try to identify the bottlenecks in it.

Thank you for helping us help you.

Understood, pulling this together now. Also, understand that this code works sometimes and sometimes not.

 

[KACJR]

I doubt I have an answer but do have questions. Are these action queries? Do they need to allow edits? Can they be pass-through queries?

Why are you opening queries? Is this part of your user interface?

I would attempt to move as much work as possible to the server.

Looks like I may have to bone up on stored procedures, then.

 

[tvanstiphout]

Looks like I may have to bone up on stored procedures, then.

If your current interface has no sprocs or views, and SQL Server is only used to store the tables, and everything else is in Access with linked tables, then yes, this is not a successful configuration with the BE on Azure.
Access is very chatty, and pulling all that data back and forth is killing your performance.

Mind you, that should NOT result in incorrect records being returned. That may have a different root cause. For example, do all your tables have a PK and a Timestamp?

 

[KACJR]

If your current interface has no sprocs or views, and SQL Server is only used to store the tables, and everything else is in Access with linked tables, then yes, this is not a successful configuration with the BE on Azure.
Access is very chatty, and pulling all that data back and forth is killing your performance.

Mind you, that should NOT result in incorrect records being returned. That may have a different root cause. For example, do all your tables have a PK and a Timestamp?

Yes, every table has a PK and each record timestamp.

 

[Minty]

@tvanstiphout bear in mind this was already a SQL server backend, so some of the usual issues with moving to a SQL backend should have already been overcome.

However, the difference in connectivity performance between an on-prem SQL server and an Azure one is pretty significant, hence wanting to see the process (code) and Queries in full.

 

[sonic8]

Also, understand that this code works sometimes and sometimes not.

When using DB.Execute, did you use the dbFailOnError option? Otherwise you might not get a catchable VBA error when the query fails. DoCmd.OpenQuery with SetWarnings(False) is even worse in that regard.

 

[KACJR]

After reading the posts here as well as in other forums it seems that Stored Procedures is the way to go. Our database consultant also agrees that Stored Procedures will resolve most performance issues. So that's the direction I'm gonig to take.