sql odbc timeouts

[antvon]

Hi

I have a MsAccess 2000 database with odbc linked tables to a
Sql 2000 Server .
Some of the tables are huge (+500k records) and is causing
a odbc timeout error when viewing tables or running queries.

I have read that some like to set the timeout to 0 in the registry
which I have done but to no avail.

I could try to create a local table from the linked one but
obvouisly the make table query times out as well before it
can finish.

Any ideas

Please note my knowledge of odbc data sourcing is practically
none existant.

Thanks in advance

Anton

 

[oldhandandy]

Have you tried increasing the ODBC Timeout in the query properties, default is 60 secs

 

[antvon]

Hi oldhandandy

Firstly thanks for the reply, I was beginning to think I
had somehow become persona non grata since nobody seemed keen to answer this thread.

Yes I have tried that approach, I've even switched it off - 0 setting, but the delay becomes unacceptable 3-5 minutes.

I have coded a workaround until a better solution presents itself (hopefully) by slowly extracting all the records I needed from the linked table into a local table.Fortunately the tables were history type records that only get created and not updated.Now I only have to extract a few days worth of data that takes about a couple of seconds instead of minutes

Thanks again for the reply

 

[cmmrfrds]

The solution is to not use linked tables. The Access Project (ADP) does not rely on linked tables or odbc and is not that difficult to set up. I have converted a few access mdb's to adp's and it was not that difficult provided the application was designed correctly in the first place.

If you don't want to go with ADP then depending on how you are using the linked tables there ways in ADO to access the data directly. If you are interested in this, explain how you are using the tables in the application.

 

[thecreator]

You may wish to consider creating a "view" on the SQL database ... this makes your server do all the hard work and doesn't seem to affect timeouts as much.

Either way, consider how much data you are trying to ship across your network and what bandwidth you have to work with.

Also, calling a stored procedure may assist.

Cheers.

 

[antvon]

Hi guys

Firstly my most humblest of apologies for not responding
sooner but work had taken a turn for the worst and just
could not find time for anything.

I don't know if you guys will even see this reply but in case you do I would just like to say thank you for the advice and that it was most insightfull