Apr 27 2020 10:28 AM
Apr 27 2020 10:28 AM
Our Access ODBC to SQL server used to take 15 minutes to do a re-query (query button allows for 4 date ranges to be entered). It seems after the recent updates in both Windows and MS Office, the re-query to the server now takes 3-4 hours. Our Access developer doesn't think it's and Access issue, but a SQL Server issue. Can anybody offer suggestions as to why things changed all of a sudden? Do we need to update anything in the SQL Server? I believe we are running SSMS 2016. Thanks in advance!
Apr 28 2020 06:08 PM
Are you calling Open? Or are you calling Requery with the changing parameter values? The query speed is significantly faster with the changing parameter values.
Apr 29 2020 08:08 AM
Thanks for the reply. We re-query Access with 4 different date parameters, each with a Start and End date field, so 8 fields in total. There are approx 14,000 records and 230 columns. The re-query always took about 15 minutes (to/from SQL server). Did a re-query yesterday at end of day, it took 2.5 hours to finish. I installed the latest ODBC Driver (17.5) - released in March 2020 on the client side, and my re-queries seem to run a bit faster than everyone else in the office (who are still running with previous driver, dated 2019). I don't know much about SQL, but I'm wondering if SQL needs the same ODBC driver installed on that computer? Thanks!!
Apr 29 2020 05:48 PM
@lyeomans2160 So based off what you told me, I found two areas that could possibly help you out. Yes, absolutely try the new ODBC driver that you downloaded on a different client computer, which yielded great results.
Two: I'm assuming you're operating on Windows 10? I've read all sorts of performance issues expanding across many different pieces of software with the latest March 2020 update. If the ODBC driver does not increase the performance, I'd try uninstalling the latest updates of Windows. Not sure if that's the case but it's worth a mention.
Apr 30 2020 09:45 AM
Thank you for the suggestions. I went over and had a look at the SQL server. I discovered that Windows update attempt for SQL Server 2016 Service Pack 2 (kb4532097) failed to update.
I also notice in the ODBC window that the driver version is SQL Server 17. I know that 17.5 was released in March 2020. So I'm thinking if I get the service update Pack 2 to successfully install, update the ODBC Driver to 17.5 and update all the Access client ODBC drivers to 17.5 version, maybe things will work like they are supposed to.
Do you think it's worth a shot?