Inserting records out of order

[rando1000]

I have a VBA program that loops through a table of table names and imports the records from those tables into another table. In some instances (not every time), the inserting gets confused, and I will see some records from one table, then some records from another. It's like one insert process is not completing before another begins. Any idea why this happens?

 Do While cnt < 89 'Number of tables
    strCurrName = rs("Name")
    With dbs
        strSQL = "Insert into FullTable Select * from " & strCurrName
        .Execute strSQL
    End With
 
    strCurrTab = Replace(Left(strCurrName, InStr(strCurrName, "_")), "_", "")
 
    With dbs
        strSQL = "Update FullTable Set Tab = '" & strCurrTab & "' where Isnull(Tab)"
        .Execute strSQL
    End With
 
    With dbs
        strSQL = "Insert into FullTable Select * from Divider"
        .Execute strSQL
    End With
 
    cnt = cnt + 1
    rs.MoveNext
Loop 

[missinglinq]

Access is asynchronous in the way it runs commands, which is a fancy way of saying it doesn't wait for one command to complete before running the next command in code, which, as you've apparently found out, can cause timing problems and sometimes cause processes to terminate prematurely..

In your code, just before your Loop command, try placing the command DoEvents. This should yield control to system processes, such as pulling records from one table to another, and allow it to complete before starting the next iteration of this.

This is all based on some records not actually getting imported, as opposed to them not appearing in the same order in the table as they were imported. Have you checked for this possibility? That the records are there, just not in the order you expected? A table in Access is, as some like to say, simply a bucket of data. Order really has little meaning in a table.

Linq ;0)>

[rando1000]

Thanks. I'll give that a try. It's weird, because it works as expected for the first couple dozen tables. I guess after that point, memory is bogged down and processes are not completing fast enough, so it's starting the next process before the first completes.

[rando1000]

No luck. Still, toward the end of the file, records from various source tables are intermingled.

[rando1000]

Is there any way to disable asychronus behavior?

[rando1000]

On second hand, I take that back. It does seem to have worked. Thanks much!

[missinglinq]

Glad we could help, and no, there's no way to 'disable' the behavior, you can only work around it.

Besides DoEvents, for example, if you're opening a form to do something and need the code to wait until you close that form before it continues, you can open the secondary form in Dialog mode. All code in the original form will halt execution until you close the second form.

And then there are some 'wait' or 'sleep' hacks out there. The following one, courtesy of Allan Browne at http://allenbrowne.com/tips.html , lets you call a function and enter a set number of seconds that you your code to halt. Notice that this, too, uses DoEvents.

Public Function Wait(lngSeconds As Long)
   Static bRunning As Boolean
   Dim dtEnd As Date
 
   If Not bRunning Then
       bRunning = True
       dtEnd = DateAdd("s", lngSeconds, Now())
       Do Until Now() >= dtEnd
           DoEvents
       Loop
   End If
   bRunning = False
End Function

Linq ;0)>

[rando1000]

I also realized I could simply recreate the table after it's put together using the proper SQL order-by clauses. Now that I know what the issue is, I think I can work around it. Thanks again for pointing that out.

[missinglinq]

Glad we could help!

Linq ;0)>