Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Using an ADODB connection to execute SQL statements synchronously

Avatar of PeterFrb
PeterFrbFlag for United States of America asked on
Oracle DatabaseVisual Basic Classic
4 Comments1 Solution1122 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of David L. Hansen
David L. HansenFlag of United States of America imageCEO

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answers