Microsoft KB Archive/128885

= ACC2: "ODBC-Remote Query Timeout" Deleting Main Form Record =

Article ID: 128885

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q128885





SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you delete a record on a form that contains a subform, the application seems to stop responding (hang), and then, after the configured ODBC query timeout has passed, you receive the error message "ODBC-remote query timeout expired."

At this point, you can choose to accept or to roll back the delete. When you do, the subform is populated with "#NAME?" messages. The subform is not populated correctly until you re-open the main form.



CAUSE
This problem occurs when the following conditions are true:


 * the main form and subform are based on attached Microsoft SQL Server tables
 * the table on which the main form is based has a delete trigger set up that automatically deletes records from the child table when corresponding records in the parent table are deleted

When you delete a record using the Microsoft Access user interface, Microsoft Access begins a transaction by sending a delete command to the server. Before committing the transaction, Microsoft Access tries to reselect the subform. Microsoft SQL Server does not allow a select if there is an uncommitted transaction on the same data page; it waits for the transaction to be committed. Since both the delete command and the subsequent select command are part of the same transaction, Microsoft Access reports an ODBC timeout.



RESOLUTION
Instead of using the Microsoft Access user interface to delete records, create a button with an SQL pass-through query in its OnClick event procedure to delete the record, as in the following example.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

Sub Button1_Click

Dim mydb As Database, myq As QueryDef Set mydb = DBEngine.Workspaces(0).Databases(0)

' Create a temporary QueryDef object that is not saved. Set myq = mydb.CreateQueryDef("")

' Set the ReturnsRecords property to No in order to use the ' Execute method. myq.returnsrecords = False

myq.connect ="ODBC; " myq.sql = "delete from Parent _        where keyP='" & forms!aParentForm!keyp & "'"

myq.Execute myq.Close forms!aParentForm.Requery

End Sub

The first part of the code sends an SQL pass-through query to delete the record. The requery removes the deleted record. This code returns the application to the beginning of the form's recordset. If you want the application to return to the position where it was before you deleted the record, you need to add additional code using bookmarks.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

Additional query words: freeze

Keywords: kberrmsg kbbug kbusage KB128885

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.