ODBC timeout - update on linked tables - SQL Server 2000 - Microsof Access 2003

Microsoft Access 2003 MDB - DSN Less connection to SQL Server 2000

I have 2 databases operating this way.  1 with no issues.  The 2nd is generating an update on linked table failed - ODBC timeout expired.  I have check the table but can find nothing that seems to be different.  The form is a basic main form with subforms using the same record source.  When I change a value on the subform and then move to the parent form the hourglass appears and then the timeout eventually appears.  This same operation on the identical form in the 1st database has no problems.

Any ideas?
Who is Participating?
genaughtonConnect With a Mentor Commented:
Additional thought (shifting to SQL mode!)...

Try running the UPDATE code directly on the server via the SQL Query Analyzer.  If it's bad data or something about the table design, it should fail there, also.  Only with better error messages.

In the ODBC world, I also used the SQL Trace tool, where you could view the actual text streams of code going back and forth between the client and server -- don't know if that's available for the DNS less connections, but would be worth a look.


good night
¿the 1st database is in other server?
Go to Tools --> Options --> Advanced  and increase the OLE/DDE TimeOut. See if that works.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Set a commandtimeout value on your connection object...

dim cnn as Connection
etc., etc.

cnn.commandtimeout = 180   '* in seconds
softdimensionsAuthor Commented:
the 2 databases are identical instruture i.e. they contain different data but the structure is presumably the same.  Is there something in the data that could cause this.  Even if it worked after increasing the timeout it would be unacceptably slow
It sounds like  a locking issue -- here's some random thoughts...

Why are you using separate forms (main & sub) if the data source is the same?  Are you using separate recordset objects (one for the main and one for the sub)against the recordsource?

I'm wondering if the "main" form is holding a row (or page) lock on your data, so when the subform tries to update data it's timing out waiting for the main form to relinquish the lock.

Of course, if the forms and code are EXACT duplicates that shouldn't be happening on the client/Access end of one but not the other (check your recordset.open parameters).

Is the data locking set differently on the two databases (pessimistic vs. optomistic; row vs. page, etc)?  My dim recollection from when I was a heavy -duty SQL Server type was that the default configuration for SQL Server 2000 was page locking (locks a lot of rows), although it could be changed to row-level.  I also think this can be set at the table level.

Good luck,

Jerry N
excuse me ,
¿is the first database in w2k server and the second in w2k3 server?
softdimensionsAuthor Commented:
Both databases are on the same server and should have the same code, although the problem most probably lies in the main for sub form relationships being slightly different between versions.  I will try some of genaughton suggestions this weekend / Monday and see what happens
softdimensionsAuthor Commented:
It was "bad" data - but not really bad data but a corrupt index so genaughton: gets the points since he was closest.  I removed the indexes for the table in questions and recreated them and now both databases operate the same

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.