SQL Server Timeout On ODBC Connected Tables

[richlocus]

Hello:

I'm on a large project where I'm connecting my Access application to a remote Microsoft SQL Server using an ODBC connection to the server's tables. The first part of my application loads numerous local Access tables from the remote location using Access Insert queries. This is done once a day and used to take hours when using MySQL. Apparently MySQL was configured with no timeouts.

The client switched from MySQL to SQL Server. What is happening is that when the application is ODBC connected to the server's tables and does very large downloads to local tables, the process always terminates at 40 minutes of downloads, regardless of which table is being downloaded. After some research I located a setting in SQL Server which sets a time limit on ODBC connected download times, as follows:

https://learn.microsoft.com/en-us/sql/d ... rver-ver16

My client is hesitant to accept this as a solution, and points to other factors such as network errors or Comcast timeouts (No, I checked with Comcast and they never use timeouts).

The default value according to the article link above is 10 minutes. My guess is someone changed it to 40 minutes in the past and left the company without training a System Engineer replacement.

Does it sound like I'm going the right direction with this issue?

Thanks,
Rich Locus

[HansV]

That sounds plausible, but I'm not an expert on this subject.

[CData]

am wondering what is meant, technically speaking, as 'downloads' .... to local tables ?

you can link to the dbo_ tables (or views - since you are not writing to the dbo tbl - a view could be more efficient than the whole table...)
...and then trigger an Append query in Access that writes records from the linked dataset to the local table....

I wouldn't think that would time out....

[richlocus]

CData:
Thanks for the reply. I could have used a better choice of words... My ODBC connected table is just another table in a query. But because it is remote Access drags every record across the network into the query. I solved the issue using Hans Pass-Through query method, but it still is bothersome that before I used Pass-Through, it timed out after 40 minutes. The server provider pushes the timeout blame on everyone else but his company. So, at least I have a workaround. Thanks for responding!!