ODBC – timeout expired

Without even typing anything into Google to begin my search for what this means, my first guess would be to check the connection to the server that this database lives on.  I did that and didn’t see anything wrong.  Then I move on to see if everybody who uses the database is having the problem or if it’s just one person.  Since we use an Access front end, and they all have a separate instance of that on their computers, this would tell me whether it’s the Access part screwing up or something on the server.  Well, it was everybody – oh joy.

There are plenty of posts about this error and I tried everything I could possibly find.  I changed the query timeout value in the registry, I added a timestamp field on the problem table, I deleted some of the old data in case the table had become too large, I rebuilt the indexes, and I restored from a backup shortly before they started seeing this error.  I spent 3 days working on this, almost non-stop, and tried everything that did not include completely wiping out the table.  I forget exactly why the timestamp idea was suggested, but it had something to do with it allowing Access to search through the data quicker.

Finally, I was losing my mind so as a last resort I completely wiped out the data from the table in the error message and its child tables.  This ended up being 17,000 records from each table, which is quite a bit for us.  After that it worked.  What I don’t quite understand is why clearing out the entire table worked, but deleting old data and then the data from the week it broke didn’t work.  I still think something somebody entered in the day that it crashed is what killed it.  But if deleting everything from that week didn’t fix it, I don’t know how what they did would have impacted that entire table.  But at least it’s working now.

Sources:

,

  1. #1 by John on September 25, 2010 - 2:38 PM

    Nice job getting it working again and good luck finding the source of the initial issue.

    (Good to see you posting again, BTW)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: