Microsoft KB Archive/202396

= ACC2000: Access Hangs When Mixing DDL and ADO Transaction Methods =

Article ID: 202396

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202396



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:
 * 1) Press CTRL+ALT+DEL to open the Close Program dialog box.
 * 2) 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].
 * 1) Click End Task.
 * 2) 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.



Steps to Reproduce Behavior
 Create a new database called TestCode.mdb. In TestCode.mdb, create a new module. On the Tools menu, click References. In the References dialog box, make sure the following library is selected (checked):

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. </li> Close Access and the Visual Basic Editor using the Close Program dialog box.</li></ol>

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

<div class="references_section">