We help IT Professionals succeed at work.

Using an ADODB connection to execute SQL statements synchronously

PeterFrb
PeterFrb used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I forgot the VB syntax, but I see that you might have to do COMMIT after DELETE stmnt and before INSERT.
If you use ExecuteNonQuery like this you won't have any problem.  The result shows you how many rows were affected each time you use the command.  

BTW, I don't thing that the code you are currently running is doing asynchronous executions.  You'd have to be doing threading for that to happen.
Dim myResult As Int32 = sqlClient.sqlCommand.ExecuteNonQuery(strSQLCommands(0))

Open in new window

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

PeterFrbData anslyst, tableau visualization developer, vb certified

Author

Commented:
Thanks!