Timeout executing query including ODBC-linked SQL Servertables

[Sogge]

I receive the "Timeout expired" message after 60 seconds, when the
dateinterval that is a part of the query is to large. Therefore I'd
like to increase the timeout value to say 600 seconds.

I've read the threads that explains how to solve the timeoutproblem
when calling stored procedures by changing the value of
"commandtimeout", but this is not usable for me since I'm just running
a query consisting of 4 linked tables.

The connection string used during link is:

ODBC;DRIVER=SQL
Server;SERVER=<servername>;UID=<userid>;PWD=<password>;APP=Microsoft®
Access;DATABASE=<databasename>;Network=DBMSSOCN;

Any ideas?

Thanks in advance

Best regards

Søren Horney

 

[Douglas J Steele]

Are you saying that you've got 4 tables linked to tables in SQL Server, and
you're trying to join them in your front-end?

Create a view on the server!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I receive the "Timeout expired" message after 60 seconds, when the
dateinterval that is a part of the query is to large. Therefore I'd
like to increase the timeout value to say 600 seconds.

I've read the threads that explains how to solve the timeoutproblem
when calling stored procedures by changing the value of
"commandtimeout", but this is not usable for me since I'm just running
a query consisting of 4 linked tables.

The connection string used during link is:

ODBC;DRIVER=SQL
Server;SERVER=<servername>;UID=<userid>;PWD=<password>;APP=Microsoft®
Access;DATABASE=<databasename>;Network=DBMSSOCN;

Any ideas?

Thanks in advance

Best regards

Søren Horney

 

[Sogge]

Thx Douglas for a quick reply - I'll ask our DB-manager to make a view!

However IF I still need to alter the default timeout of 60 seconds -
how is this done?

Best Regards

Søren

 

[Douglas J Steele]

To be honest, I'm not sure there is a way for linked tables. I'm not sure
what's on the Advanced tab under Tools | Options has anything.

Do you need to be able to update the resultant recordset? If not, what you
can do is create a pass-through query that joins the 4 tables. You can set
the ODBC TImeout for pass-through queries (and it would eliminate the need
for a view to be created)

Sorry I can't be of more help, but I seldom use ODBC-linked tables: I use
pass-through queries, and unbound forms.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thx Douglas for a quick reply - I'll ask our DB-manager to make a view!

However IF I still need to alter the default timeout of 60 seconds -
how is this done?

Best Regards

Søren

 

[Sogge]

Thx again Douglas, I've now managed to make a Pass-Through query, but
how exactly do I set the "ODBC TImeout for pass-through queries" - it's
not in the Tools-Settings-Advanced tab!?

Best regards

Søren

 

[Van T. Dinh]

When you create a Pass-Through Query, Access default the TimeOut Interval to
60 secs. However, you can change it if necessary.

Open the Pass-Through Query in SQL View. Open the Properties window of the
Query using the icon with the left-pointing figer on a list or the Menu View
/ Properties or the function key F4) and you should see the ODBC Timeout
Property of the Pass-Through Query.

--
HTH
Van T. Dinh
MVP (Access)



Thx again Douglas, I've now managed to make a Pass-Through query, but
how exactly do I set the "ODBC TImeout for pass-through queries" - it's
not in the Tools-Settings-Advanced tab!?

Best regards

Søren

 

[Sogge]

Hi' Van,

Tanks a bunch - everything runs smoothly now !!

Søren