Solved

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

Posted on 2007-03-20
9
410 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now