odbc call failed, timeout expire

[Hallgeir]

I have some trouble with a query. In the query I have two linked tables each
with about 100000 records. The query contains to criterias. One is a between
critera on a date field (Between #01.02.05# And #28.02.05#) the other is
also a between criteria and here is my problem. If a use this between
criteria to wide I get an error. "Odbc call failed, timeout expire". Example
"Between 1 and 4" is okay "Between 2 and 5" is okay, but "Between 1 and 5"
gives me the error.

I would appreciate any hint that could tell me why this happens and/or how
I can work around it or avoid the error.

 

[Andreas]

I think you need to change the timeout settings.
Tools.Options.something - can't remember the name.
It will be obvious when you see it.

Regards,
Andreas

 

[Guest]

I'm not sure where the default timeout is set for queries, but for an
individual query, you can go to the query properties and change the ODBC
Timeout value (default is 60) to a higher value (I use 300 frequently) and
save the query. This usually addresses the problem.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is this an SQL pass-thru (SPT) query or a query on a linked ODBC table?

For the TimeOut error, put indexes on the columns that you are using in
the query's criteria. The query will run faster & the timeout may be
avoided. If the query needs more time to complete:

1) open the query in design view
2) on the menu bar select View > Properties
3) in the ODBC Timeout field increase the value (in seconds)
4) close & save the query

I noticed that you were using incorrect date formats for the Between
criteria. When using the # delimiter the date should be in USA date
format: m/d/yy

Between #2/1/05# And #2/28/05#

If you are using an SPT query you will have to format the dates
according to the syntax rules of the accessed db engine. In SQL Server
there are various acceptable date formats. I like to use 'CCYYMMDD'
'cuz it seems to be the most unambiguous format. Also, when using
Between, SQL Server wants the lowest value first. In Access the order
is not important. E.g.:

SQL Server
OK: Between '20050201' And '20050228'
Not OK: Between '20050228' And '20050201'

Access:
OK: Between #2/1/05# And #2/28/05#
OK: Between #2/28/05# And #2/1/05#

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkCC6YechKqOuFEgEQImcgCbBT03LmyqIhZx/LbSUmxYwbOLrYwAnifG
4qvUvqlVaXZH7yxmSFuU6yD0
=cbuf
-----END PGP SIGNATURE-----

 

[Hallgeir]

Thank you my friends!
Now everything workes fine.
Not a big deal but to MGFoster i have to mention that the USA date format is
impossible to use when you have a computer with norwegian regional settings.
If I write #2/28/05# in the criteria field access automatically convert this
to #28.02.05#.

regards
Hallgeir