ODBC-- call failed when a linked table is open for 15 seconds

[theghetto]

Please help. I've been fighting this thing for weeks.

I create a new blank Access 2000 database. I link a table from our SQL server via ODBC. I do this using the
File-->Get External Data--> Link Tables and select ODBC as the source. After choosing the DSN which points to our SQL server, I select the table that I wish to link.

I open the linked table in the Access database with no problem. All records are available for viewing or modification, new records can be created.

The total number of records is not displayed unless you zoom to the last record, probably because Access only reads in a chunk of the data at a time unless requested. There is only 8000 or so records. This is all fine and dandy.

The problem:

If you open the table and let it sit there idle for anywhere between 10 seconds or more, and then attempt to select a record that is not already displayed on the screen, I get the ODBC--Call Failed error, and I lose my connection to the SQL server. (it usually takes more like 20 - 30 seconds but it has happened in as little as 10 seconds)

Sometimes I can just close and re-open the table to reconnect, sometimes I have to close the whole database and re-open to reconnect to the data.

I've noticed that if you zoom to the last record when you first open the table, it takes longer to time out. If you are continuously modifying a record, or switching between records, it will never timeout.

I must be missing something very obvious.

There is an ID field (IDENTITY) which is the primary key, as well as an SQL timestamp field just for kicks.

Your help is greaty appreciated =))

 

[Tranman]

If you switch your table into design view, then back to datasheet view AFTER it has sat idle for a minute or so, is the data then available?

We had this problem with linked Oracle tables and Access 2K. Ended up finding a registry value that was causing it. If I remember right, at the time, we discovered that the same problem existed with SQL.

Tranman



"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[theghetto]

Tranman,

Yes, after the connection gets dropped, if I switch to design view, and back to datasheet view it reconnects the table. Similarly, if I close the table after I lose the ODBC connection and re-open it, I can get back to the data. Occasionally, it will not reconnect and must close the database and reopen it which always works.

I would be so grateful if you manage to dig up any info on that registry value you mentioned. Thank you