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.
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)