Timeout expired (#0)

I have a MS Access database that is linked to a MSSQL database through ODBC. The query has a where clause that retrieves records that fall into a date range. If I use a small range, lets say 30 days, the query works fine. If I increase the range to 60, I get the following error:

ODBC -- called failed.
[Microsoft][ODBC SQL Server Driver]
Timeout expired (#0)

I looked at other questions similar to mine on Experts-Exchange, and some of the suggestions asked to modify the timeout for the ODBC connection. Where would I find this setting? I didn't find anything in the ODBC settings. If there is another suggestion, I'll be glad to also give it a try. I'm using MS Access 2000 on a Windows XP machine. Thanks!
horaliaAsked:
Who is Participating?
 
GRayLCommented:
Try reading:  ODBCTimeout Property Example (MDB)

Remember this is an ODBC connection not a straightforward query.  I only included the latter to give you the bigger picture.
0
 
EMCITCommented:
Tools>Options>Advanced you can set the timeout interval there. This sounds, however, like the query involves a large number of records as the date range expands. I would try increasing the timeout slightly until it works.
0
 
GRayLCommented:
Check VB Help (Ctl-G or Alt-F11) - ODBCTimeout Property
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
You might also want to check Help - QueryTimeout Property
0
 
horaliaAuthor Commented:
EMCIT, I changed the setting as you suggested but I still get the same error. These are my settings:

OLE/DDE timeout (sec): 0
Refresh interval (sec): 60
Number of update retries: 2
ODBC refresh interval (sec): 1500
Update retry interval (msec): 250

GRayL, I read the help section and changed timeout with the following code:

Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb
dbsCurrent.QueryTimeout = 0

and I still get the error.
0
 
horaliaAuthor Commented:
Worked great. The query takes quite a while to run, but at least it runs! Thanks!
0
 
GRayLCommented:
Thanks, glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.