ODBC time out expired error message with an MS Access DB.

Connectivity

ODBC time out expired error message with an MS Access DB.

Hi all,
I am facing a rather strange issue.
I have an MS Access database that has a linked table to a terardata table.
I can query on the TD table without any problems as long as there is no join to one of the database table. If I do so, then I either get a timeout error message , or the DB just hangs, doing nothing (apparently)
I have linked the 2 tables via an inner join. I first thought that it was related to the size of my table , so to test this, I reduced the size of my access table to hold only one single record. I still get the error message.
I have increased the timeout value in my ODBC driver to no avail
If I query on the TD table by entering direclty the value of the table (and removing the join), it works fine.

has anyone ever experienced something similar ?
thanks in advance,
MR
1 REPLY
Enthusiast

Re: ODBC time out expired error message with an MS Access DB.

If you're not running this as a pass through query in Access, then Access will pull down all the data in the tables involved, and do the join itself.

Switch this to a pass-through query (in query design it is an option from one of the menus...same place you can swtich to an update query). Access will build the SQL statement and submit it to TD. The join will happen on TD and only the resulting rows will be sent across the network back to Access.

If it still times out (i.e. TD just takes a while to complete the work), THere may be an ODBC setting or Access setting for the timeout period. I know that when you do this via VBA hwen you define the connection you can specify a timeout period. I use to call a stored procedure this way that took 20 minutes to complete and was able to extend the timeout period using VB code. There may be a similar setting in the ODBC connection.

James.