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

ID: Q128885

The information in this article applies to:

Microsoft Access 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
Issue type : kbbug