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

 Last reviewed: May 20, 1997
Article ID: Q128885
The information in this article applies to:
  • Microsoft Access version 2.0

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 kbusage OdbcSqlms
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbbug
Resolution Type : kbcode

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.