Solved

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

Posted on 2007-03-20
9
436 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:softdimensions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:dbeneit
ID: 18759139

good night
¿the 1st database is in other server?
0
 
LVL 9

Expert Comment

by:nito8300
ID: 18759685
Go to Tools --> Options --> Advanced  and increase the OLE/DDE TimeOut. See if that works.
0
 
LVL 1

Expert Comment

by:genaughton
ID: 18759757
Set a commandtimeout value on your connection object...

dim cnn as Connection
etc., etc.

cnn.commandtimeout = 180   '* in seconds
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:softdimensions
ID: 18770556
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
0
 
LVL 1

Expert Comment

by:genaughton
ID: 18774151
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
0
 
LVL 1

Accepted Solution

by:
genaughton earned 500 total points
ID: 18774210
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.

JN
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 18774243
excuse me ,
¿is the first database in w2k server and the second in w2k3 server?
0
 

Author Comment

by:softdimensions
ID: 18781693
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
0
 

Author Comment

by:softdimensions
ID: 18801904
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

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question