ODBC access very slow when form is open (1 Viewer)

Kowalski

Registered User.
Local time
Today, 09:53
Joined
Jul 5, 2007
Messages
121
I've got a weird issue that I desperately need help on.
I've got an Access 365 database with a number of tables linked to SQL Server (2016) via an ODBC connection.

In short the problem is that reading data from SQL is very very slow when I have a form open.

When I open the database in Access and run a lookup in the immediate window, it is nice and fast.
E.g.
?Dlookup("TaskID", "Tasks") //This runs in a few milliseconds.

Now I've created a standard form with 1 field on. The form does not read from table Tasks. In my case it reads from table Companies.
If I open the form in Form View and I run the DLookup as above in the Immediate Window it is slow. Takes about 8 seconds for every execution. Even having the form open in Design view makes the lookup slow.
If I close my form, then the DLookup is quick again. This is really hampering my work as I have other forms that does a lot of database access, and all access is slow.

I've also switched on the ODBC tracing and the sqlout.txt only get created after about 7 seconds, and then 1 second later I have the results. Why does it take so long?
What can it be?
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:53
Joined
Jul 9, 2003
Messages
16,245
I noticed your question has not yet received a reply, hence I am bumping it up the list, hoping that you will get some more views.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:53
Joined
Feb 28, 2001
Messages
27,001
I saw this but I have no expertise in SQL server.

The things I look for in performance problems are memory, virtual memory, and active competition for the CPU. I don't see this as a disk space issue. How much RAM do you have on your front-end system? How much virtual memory do you have allocated on your FE system? If you run Task Manager, click the Processes tab, then click the CPU header on the display, that will make the top CPU consumer be the top of the display. If TM is running with this display, and you then attempt this experiment, what is running in that approximately 7 second interval?
 

Kowalski

Registered User.
Local time
Today, 09:53
Joined
Jul 5, 2007
Messages
121
Not hardware resources. Interestingly, if I change from SQL2016 to 2019 the problem disappears.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:53
Joined
Feb 28, 2001
Messages
27,001
If you can figure out the vintage of your ODBC drivers and compare them to the vintage of your SQL server, you might find that the ODBC was newer than the SQL you had started with. Software version mismatches will do strange things.
 

Kowalski

Registered User.
Local time
Today, 09:53
Joined
Jul 5, 2007
Messages
121
Thanks. Have not considered that. Might well be.
 

Minty

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
I also suspect ODBC drivers, SQL server 2019 > 2016 shouldn't really make any difference though.

Where is the Server and how are you physically connected to it?
 

Kowalski

Registered User.
Local time
Today, 09:53
Joined
Jul 5, 2007
Messages
121
I also suspect ODBC drivers, SQL server 2019 > 2016 shouldn't really make any difference though.

Where is the Server and how are you physically connected to it?
Hi. The SQL Server is on my local machine as it's in the dev environment. Connecting to it via odbc linked tables.
 

Minty

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
You have both 2019 and 2016 sql servers on your dev machine? or when you installed 2019 the problem went away?

If the latter then I suspect the install will have updated your SQL ODBC Drivers.
 

Kowalski

Registered User.
Local time
Today, 09:53
Joined
Jul 5, 2007
Messages
121
You have both 2019 and 2016 sql servers on your dev machine? or when you installed 2019 the problem went away?

If the latter then I suspect the install will have updated your SQL ODBC Drivers.
I have both. I just moved the DB from 2016 to 2019.
 

Minty

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
Interesting. What version of the SQL ODBC driver are you using?
 

Minty

AWF VIP
Local time
Today, 07:53
Joined
Jul 26, 2013
Messages
10,355
Okay that is the SQL Server Native Client 11 .
1611058336863.png

I would consider upgrading to a later version - ODBC version 17, and see if that helps.
Also when you moved it from 2016 to 2019 where the table indexes etc updated or changed in anyway?
 

lenincahuasqui

New member
Local time
Today, 02:53
Joined
Sep 8, 2021
Messages
1
all "D" (Dlookup, Dsum, etc) have problems with ODBC, is better make view in database and "D" use with this view
 

sportsguy

Finance wiz, Access hack
Local time
Today, 03:53
Joined
Dec 28, 2004
Messages
358
I have the same slow issue, though not related to a Dlookup with the same ODBC driver to SQL Server 2016 in the company data center.
THinking we need to update the server virtual machine to SQL 2019 in the data center

Thanks
 

Users who are viewing this thread

Top Bottom