• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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!
0
horalia
Asked:
horalia
  • 4
  • 2
1 Solution
 
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
 
GRayLCommented:
You might also want to check Help - QueryTimeout Property
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now