Solved

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

Posted on 2007-03-20
9
429 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

770 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