10-30-07, 17:49 #1Registered User
- Join Date
- Jul 2004
Unanswered: ODBC SQL Driver Timeout From Access 2000
I'm running a machine with windows xp pro sp2. I have a MS Access 2000 database on that machine. The database contains 1 odbc linked table
and 1 Access query.
The odbc linked table is connected to a MS SQL Server 2000 database with a ODBC SQL Server System DSN.
The table on SQL Server to which the table in Access is linked has about
3 million + rows.
When I open the ODBC linked table in Access, it opens without a problem.
However, if I use even so much as one criterion such as "where company = ABC" for example, then it "thinks" for a while and returns the dreaded:
[Microsoft][ODBC SQL Driver] Timeout Expired (#0).
I have to use MS Access 2000 as the front end for this SQL Database table.
How do I get around the timeout issue. Is there a setting in SQL?
Please keep in mind that
I can't write the queries as Store procs because the requirement of the client is that there be one Linked Access table that the users can use
to write various access queries. No user are not techies.
10-30-07, 21:11 #2Moderator
- Join Date
- Dec 2004
- Madison, WI
If you're sure your criteria is correct (usually you just put ="ABC" for the criteria if you're in the query design, you can also look at the properities of the query and change it fron Dynaset to Snapshot which gives you a quicker respones. There is also an ODBC TimeOut which you can set to 0 (or try another higher number).
Also, I've found success in adding a TimeStamp datatype field to the SQL Server table. This has helped for large recordsets. Make sure you refresh all the linked tables.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)