Query produces ODBC error when using 2001 dates 1

[KHerm]

We have an Access97 query that will return data just fine as long as the dates are not in 2001.

We use a start date parameter and an end date parameter entered by the user. The query uses 7 tables that are linked to a SQL Server6.5 database. Whenever we use a date that is in 2001, we get an ODBC error. The error we get implies that the network is down, but we get no specific error number.

Has anyone seen this or have a clue what is going on?

Thanks in advance!

Ken

 

[Eradic8or]

Dont know if I am on the right track here but when you are querying dates in access, they are in American format (mm/dd/yyyy). but in SQL they are in british (dd/mm/yyyy).
Try formatting your SQL to accomodate this and see if that makes any difference.

Eradic8or.

 

[KHerm]

No, it's not the date format. If we use >=#01/01/00# as the start date and <#01/01/01# as the end date, the query runs fine. If we use >=#01/01/01# and <#06/01/01#, the ODBC error occurs (or both dates in 2001). The query did run with >#01/01/01# alone. But we will need to run quarterly queries in 2001 eventually.

I'm thinking now it's a time-out error. I'm going to increase the time-out to 10 minutes tomorrow and see what happens. I'll post the results here.

Thanks for responding!!

Ken

 

[KHerm]

I set the ODBC time-out to 1200 and the query ran for 01/01/01 to 06/01/01. So the query was indeed timing out.

When we run the query for all of 2000, it takes less than 2 minutes (with the ODBC time-out set at 60!). The database has data back to 1996. I ran the query for 12/01/00 to 02/01/01 and the query failed with the ODBC error in about 3 minutes. By the way, setting the ODBC time-out to 600 didn't work either.

Why does Access97 behave this way? And I guess the follow up question is, is there anyway to make Access operate more efficiently?

Ken

 

[tlbroadbent]

If the tables are on SQL Server, have you considered creating pass-through a query? You can also create views in SQL Server that can be linked to Access. The views could join tables at the server rather than having to do the work on the client. The more processing you transfer to SQL Server, the better your response will be.

Check the following links for more info about creating efficient Client/Server applications.

http://www.advisor.com/Articles.nsf/AID/BALTA15

http://www.smartaccessnewsletter.com/sa/samag.nsf/0/BD646FA09A3FC633872568AF0013E226

http://support.microsoft.com/support/kb/articles/Q210/4/15.ASP

http://support.microsoft.com/support/kb/articles/Q128/8/08.asp

http://www.swynk.com/faq/sql/sqlfaq_client.asp#CliProgMsSP

Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[KHerm]

Terry, Thank you for all that information! And your suggestion makes perfect sense. I now have to admit that Access has some nice query features, like using IIf to help with sums and grouping, and a few other things. I'm pretty sure we can do the same kind of things in TRANSACT-SQL but it's going to take some translating. Now we have to decide whether to do that or not.

Thanks again!

Ken