Solved

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

Posted on 2007-03-20
9
444 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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 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