ACC2000: Access Hangs When Mixing DDL and ADO Transaction Methods

ID: Q202396

The information in this article applies to:

Microsoft Access 2000

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

SYMPTOMS

When you mix ActiveX Data Objects (ADO) transaction methods with Microsoft Jet 4.0 Data Definition Language (DDL) transaction statements, Microsoft Access and/or the Visual Basic Editor may stop responding (hang).

RESOLUTION

To avoid this problem, do not mix ADO transaction methods with Jet DDL transaction statements. They should all be one or the other. For an example, see the "Steps to Reproduce Behavior" section later in this article.

If Access and/or the Visual Basic Editor stops responding, follow these steps:

Press CTRL+ALT+DEL to open the Close Program dialog box.

Select either Microsoft Access or the Visual Basic Editor.

NOTE: If the problem code is started from the Visual Basic Editor, the Close Program dialog box shows Visual Basic [Running] instead of listing Access. If you start the problem code from within Access, such as from a form or a macro, the Close Program dialog box shows Microsoft Access [Not Responding].

Click End Task.

If another dialog box appears with the title Microsoft Access [Not Responding], click End Task on that as well.

Access and/or the Visual Basic Editor will then close.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new database called TestCode.mdb.

In TestCode.mdb, create a new module.

On the Tools menu, click References.

References

Microsoft ADO Ext. 2.1 for DDL and Security

In the new module, type the following procedure:


Sub JET40Transaction()

   Dim conn As New ADODB.Connection
   Dim SQL As String
   Dim ADOXCat As New ADOX.Catalog
    
   On Error GoTo ErrorHandler
    
   Kill "c:\Test1.mdb"
   ADOXCat.Create "Provider=Microsoft.Jet.OLEDB.4.0; _
   Data Source=c:\Test1.mdb"
    
   With conn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open "Data Source=c:\Test1.mdb"
      .Execute "CREATE TABLE TASKS ([Emp ID] Char(5), EmpName char(20));"
      .Execute "INSERT INTO TASKS ([Emp ID],EmpName) VALUES ('1','Bob');"
      .Execute "INSERT INTO TASKS ([Emp ID],EmpName) VALUES ('5','Joe');"         End With

   conn.Execute "BEGIN TRANSACTION"
   conn.Execute "DELETE Tasks.[Emp ID], Tasks.*" & _
      "From Tasks" & " WHERE (((Tasks.[Emp ID])='5'));"

   conn.CommitTrans

Exit Sub

ErrorHandler:
   If Err = 53 Then
      Resume Next
   End If

   MsgBox Error & " error # " & Err

End Sub

On the File menu, click Save and save the module as Module1.

In the Immediate window, type the following and press ENTER:
```
Jet40Transaction 
```
Note that Access and the Visual Basic Editor stop responding.

Close Access and the Visual Basic Editor using the Close Program dialog box.

Note that all the statements in the example use the Microsoft Jet Execute method and DDL except for the line "conn.CommitTrans", which is an ADO transaction statement.

The correct syntax for committing the Jet transaction in this case is the following


conn.Execute "COMMIT  TRANSACTION"

which replaces:


conn.CommitTrans

REFERENCES

For more information about using ADO transaction methods, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Connection property" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using Jet 4.0 DDL transaction statements, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Data Definition Language" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: pra BeginTrans RollbackTrans Rollback

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug