asked on

Using an ADODB connection to execute SQL statements synchronously

I'm using an ADODB connection in VB to execute a series of SQL statements, and it's important that they perform synchronously. The problem I've found is that if a 2nd execute starts before the first one completes, the whole process hangs forever, and my only option is to prematurely bail from it.

I have provided below a simple version of exactly the problem into which I was running. The first command deletes a large table of all its records, and the second repopulates that table. The problem, I fear, is that the code attempts to append records to a table that is still locked in the deletion process; and not negotiating the transitions properly, the process gets stuck in a conundrum it never resolves. I believe the problem has to do with asynchronous execution, and synchronous execution would resolve it.

The code is written in Access VBA, executing on a connection to an Oracle data source.
Your assistance is appreciated.
~Peter Ferber

Sub ExecuteSynchronousCommands(UseConn As ADODB.Connection)
    Dim strSQLCommands() As String
    Dim iCount As Integer

    ReDim strSQLCommands(0 To 1)
    strSQLCommands(0) = "DELETE * from TestTbl;"
    strSQLCommands(1) = "INSERT INTO TestTbl(TestField) Select TestField From Million_Row_Table;"
    
    For iCount = 0 To UBound(strSQLCommands)
        UseConn.Execute strSQLCommands(iCount)
    Next iCount
End Sub

Open in new window

ajexpert

I forgot the VB syntax, but I see that you might have to do COMMIT after DELETE stmnt and before INSERT.

ASKER CERTIFIED SOLUTION

David L. Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yjchong514

See the sample on how to perform commit and rollback to solve the problem.

Sub CreateTransactionADO() 
    Dim conn As ADODB.Connection 
    On Error Goto ErrorHandler 
    Set conn = New ADODB.Connection 
    With conn 
        .Provider = "Microsoft.Jet.OLEDB.4.0" 
        .ConnectionString = "Data Source=C:\Acc07_ByExample\Northwind.mdb" 
        .Open 
        .BeginTrans 
         
         'insert a new customer record
        .Execute "INSERT INTO Customers " & _ 
        "Values ('GWIPO','Gwiazda Polarna'," & _ 
        "'Marcin Garnia', 'Sales Manager', 'ul.Majewskiego 10'," & _ 
        "'Warsawa', Null, '02-106', 'Poland', '0114822230445', Null)" 
         
         'insert the order for that customer
        .Execute "INSERT INTO Orders" & _ 
        " (CustomerId, EmployeeId, OrderDate, RequiredDate)" & _ 
        " Values ('GWIPO', 1, Date(), Date()+5)" 
        .CommitTrans 
        .Close 
        MsgBox "Both inserts completed." 
    End With 
ExitHere: 
    Set conn = Nothing 
    Exit Sub 
ErrorHandler: 
    If Err.Number = -2147467259 Then 
        MsgBox Err.Description 
        Resume ExitHere 
    Else 
        MsgBox Err.Description 
        With conn 
            .RollbackTrans 
            .Close 
        End With 
        Resume ExitHere 
    End If 
End Sub

Open in new window

PeterFrb

ASKER

Thanks!