SQL azure incompatible with Microsoft access

Question

Hi everyone.

I'm new to azure and microsoft access has asked me to migrate back from access to sql server. I have made the migration on my computer with SSMA to a sql server 2019 installed on my computer and after some corrections everything has gone perfect (this was just a test). Now my problem is that after making the same corrections Microsoft access is extremely slow and depending on which forms it never loads them.

I explain more in detail:

This is an example I am making a very simple query:

SELECT view. [AGENT-ID], view.AGENTE, view.CIUDAD, view.PAIS, view.CIF, view.ORGANIZA, view. [Omni-code]
FROM [view]
WHERE (((view. [AGENT]) Like "" & [NAME] & ""))
ORDER BY view.AGENTE;

So much very little to do it only has 2666 results but then I have a textbox that counts the result number and keeps calculating it for almost 5 minutes. instead this same pointing to the server in local sql server does not happen to me, can you tell me how to solve this?

I also have a form with a much more complex query this does not load I have been reading that it may be the VB code, eliminating all the functions I have managed to load me after 10 minutes and you only have 10,000 records with three inner joins, it does not seem like one query so complete, on the other hand if access points to my local sql server it only takes a few seconds to do it.
Is there anything i can do other than refactor all access code?

Thanks in advance.

Answer 1

Hello, after many tests to solve it I had to create sql views for each query that was slow.

Answer 2

So what if you run this query directly from SSMS?

I don't know much about Access, or rather nothing at all. However, when you work with the cloud it is very important that your application is not chatty, that is it must send a lot of small commands to SQL Server. When your database is in the cloud, there is a gross penalty for this for two reasons:

1. Longer distance, and you are limited by the speed of light.
2. Complex gateways in Azure to reach your actual server.

Also check your connection string so that you don't have Multiple Active Result Sets active,

Answer 3

Hello, thanks for answering.

When I run the query in SSMS it only takes a few milliseconds.

I have been reviewing what it says in the following post but I do not finish understanding it since my query is very simple

https://support.microsoft.com/en-us/topic/acc-you-may-encounter-slow-performance-or-hangs-when-designing-executing-queries-that-include-pass-through-queries- 01e1d72e-d214-6daa-db52-89e2dd837177