SQL INSERTS skipped when several Inserts are used in a row

[Toddius]

Hi

For starters I am working from excel and controlling an Access 2007 database. I am trying to use an INSERT with an rs.execute command to add data into a table in one db into a table in another db.

My problem is that I have two insert queries one right after another (header info and then data). Then a loop to load another text file and do the insert again.

So far my code works great if I step through it using F8, but if I just let it go it only loads the header table and skips the data table. I am guessing that the INSERT has not finished running.

Is there some may to make it wait until the execute is done before moving on?

Thanks
Todd

Here is a sample of my code.

 

[Toddius]

Lets try this again:

 

[Toddius]

Sub LoadToDB()
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim conn As ADODB.Connection
.
.
Set conn = New ADODB.Connection
.
For ii = 1 To 10000
If Len(myRange(ii, colAction)) > 0 Then
.
.
.
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & DBName
conn.Open

For Each myFile In fsoFolder.Files
If myFile.Type = "AVD File" Then
Call NewAVDDatabase(tempDBName, gintRefID) 'Create first DB
.
'LOAD DATA INTO FIRST DB HERE

'Move into MAIN DB
'Append Header
strSQL = "INSERT INTO " & gstrHeaderTableAVDTemp & " "
strSQL = strSQL & "SELECT * FROM " & gstrHeaderTableAVDTemp & " IN '\\Sea-flac-10-542\d$\03595\2D Model\DBFiles\DBTemp" & gintRefID & ".accdb'"
Set rs = conn.Execute(strSQL)
'Append Data
strSQL = "INSERT INTO " & gstrDataTableAVDTemp & " "
strSQL = strSQL & "SELECT * FROM " & gstrDataTableAVDTemp & " IN '\\Sea-flac-10-542\d$\03595\2D Model\DBFiles\DBTemp" & gintRefID & ".accdb'"
Set rs = conn.Execute(strSQL)

End If
Next myFile
.
.
.
.
End If
Next ii
End Sub

 

[randy700]

When you want the code to wait for the previous command to finish, try using DoEvents.

Randy

 

[Toddius]

DoEvents doesn't seem to be working. The header table is appending but the data table is skipped.
Thank you for the suggestion. I hadn't tried that.

 

[JoeAtWork]

I would put in an error handler and if that gets triggered log anything in the ADO connection object's Errors collection.

Another thing might be to create a temporary link to the table, run the SQL statement (modified as if it were a local table), and when complete unlink the table.