Access w/SQL Linked tables Slow/not responding started today (1 Viewer)

[vxr]

Hello, I am having an issue in 2 different offices that use complelty different access databases and SQL server. Both of them are experience slowness/not responding when performing certain things.

I am pretty sure this is Access related because I have one PC that is downgraded to Novembers version and that is the only PC not having this issue. I have that PC downgraded because the December version broke the edgebrowser.

Anyone else experiencing this?

Thanks!
~V

 

[isladogs]

Which version & build do you have for the slow machines?

 

[vxr]

Sorry, I meant to include it but got distracted:

Version 2312 (Build 17126.20132) - Slow/hangs
Version 2310 (Build 16924.20124) - No issues

One way to replicate this issue is to open a large table and try to go to the last record or create a new record.

 

[The_Doc_Man]

Normally we see these kinds of problems on Wednesdays since MS publishes patches late on Tuesdays. However, if you have a really paranoid IT staff, they can stage the patches to a local server and make them happen on a different day. So it still COULD be patch-related.

If you have a precise date for when this problem was first noted and you check on that machine using Start >> Settings >> Update and Security >> Update History, see if this particular problem immediately followed either a Windows or Office patch. Windows offers you a chance to "roll back" patches, which should let you test whether a recent patch is your culprit.

 

[vxr]

As far as I know, someone in the office said it started occurring yesterday.

I checked "Start >> Settings >> Update and Security >> Update History" and i doesn't show office updates only Windows. I read somewhere to check the date modified for WinWord to determine when update occurred and it shows modified yesterday.

"C:\Program Files (x86)\Microsoft Office\root\Office16\Windword.exe"

I am sure it's version related as rolling back to Version 2310 (Build 16924.20124) has no issues runs perfectly.

Thanks
~V

 

[Pat Hartman]

If rolling back solves the problem, then that is what you have to do. You should also use the reporting feature within Access to specify the two versions and when the issue first started.

MS acts on reports like this but the fix is rarely immediate. It really depends on how many people complain and how critical the problem is.

 

[cheekybuddha]

Likely related to this (now fixed apparently) bug

 

[vxr]

@cheekybuddha

Resolved:
That link helped me although I might not have the exact issue.

For years I have been using the SQL Server driver. I Re-linked using "ODBC Driver 17 for SQL" and the issue has been resolved. (previous MS Access build didn't care)

Previously the SQL Connection String:
"DRIVER=SQL Server;"

Now:
"DRIVER=ODBC Driver 17 for SQL Server;"

I will do some testing to make sure I don't have any issues before pushing this live.
Thanks everyone for your help.
~V

 

[isladogs]

The latest Current Channel build was released earlier today and we have had at least one other report of slow linked SQL tables in the latest build.

I'm currently testing in Beta Channel Version 2402, Build 17304.20000 and am also experiencing extremely slow response using the same test as you suggested in post #3 using the old generic SQL Server driver. I then repeated using the newer ODBC driver 17

Records	Load Time (SQL Server driver)	Load Time (ODBC Driver 17 for SQL Server
41K	38	<1
120K	76	2
1.33M	335	9

That's seems pretty conclusive to me.

 

[vxr]

The latest Current Channel build was released earlier today and we have had at least one other report of slow linked SQL tables in the latest build.

I'm currently testing in Beta Channel Version 2402, Build 17304.20000 and am also experiencing extremely slow response using the same test as you suggested in post #3 using the old generic SQL Server driver. I then repeated using the newer ODBC driver 17

Records	Load Time (SQL Server driver)	Load Time (ODBC Driver 17 for SQL Server
41K	38	<1
120K	76	2
1.33M	335	9

That's seems pretty conclusive to me.

That's awesome, I appreciate the test. Just one thing I noticed.
The "SQL Server Driver" is dated as 12/13/2023
The "ODBC Driver 17 for SQL Server" is dated as 12/20/2022

Meaning the "Driver 17" is older

 

[Pat Hartman]

I wonder if they broke the old driver.

 

[vxr]

I wonder if they broke the old driver.

Yes, I had that suspicion too, but I am not sure that is the case. On the PC that I rolled back to Version 2310 (Build 16924.20124), I only have one SQL connection type available and that's the "SQL Server". But what's interesting is it is dated as 1/8/2024. I'll update that PC back to the current build and see if that changes anything to the SQL Drivers available. I'll post the result.

Edit: I just updated back to current build and the only SQL version available on that PC is still "SQL server" Dated 1/8/2024. Performance is bad again. So, I don't think it's the actual driver. It seems to me the issue is with how access is using the driver.

 

[isladogs]

I've just reported this issue to the Access team with a link back to this thread

 

[isladogs]

A fix has been identified & already released to the beta channel. I can confirm it solves the problem

The fix should be released to the current channel tomorrow evening UK time.
This isn’t a version update. You will just need to restart Access for the fix to take effect.

Alternatively, if you change to a newer driver such as ODBC Driver 17 for SQL Server, this will not only fix the slowdown but also provide far greater security. However, the newer driver will need to be installed on each machine

 

[cheekybuddha]

but also provide far greater security

What is your source for this statement, @Colin ?

 

[vxr]

I appreciate the update. Personally pushed out ODBC Driver 17 for SQL Server. Unfortunately I couldn't Assign it by GPO because the MSI requires iacceptmsodbcsqllicenseterms=yes. I tried to get Orca to create an MST file, but I couldnt find anywhere I could download the MS SDK. I figured it would be resolved soon, but I couldn't wait. still waiting on the Edgebrowser fix and that issue is about a month old now.

 

[cheekybuddha]

Be aware that the latest MSODBC driver is now version 18.

No point installing obsolete software!

 

[vxr]

Thanks, I 100% agree! I saw that 18 was out but for some reason it wouldn't work. I didn't try to troubleshoot it as I just wanted to resolve the issue ASAP. When I have some time I will work on it. Hopefully I can figure out how to push it via GPO.

 

[cheekybuddha]

Not really familiar with GPO, but can you use the /passive switch with msiexec ?

 

[isladogs]

What is your source for this statement, @Colin ?

Many comments from current & former MVPs, DBAs and various articles on the subject.
I'm sure you can find links discussing the different versions

I believe the generic driver was written for SQL Server 2000 and lacks some security features available in recent versions
Having said that I have been happy to use the old generic driver for years both for convenience (as it is ubiquitous) and because I don't use the latest SQL Server features. However this bug, although quickly fixed, is a wake-up call at least for me for me to update