By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,107 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,107 IT Pros & Developers. It's quick & easy.

Inserting records out of order

P: 80
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?

Expand|Select|Wrap|Line Numbers
  1.  Do While cnt < 89 'Number of tables
  2.     strCurrName = rs("Name")
  3.     With dbs
  4.         strSQL = "Insert into FullTable Select * from " & strCurrName
  5.         .Execute strSQL
  6.     End With
  7.  
  8.     strCurrTab = Replace(Left(strCurrName, InStr(strCurrName, "_")), "_", "")
  9.  
  10.     With dbs
  11.         strSQL = "Update FullTable Set Tab = '" & strCurrTab & "' where Isnull(Tab)"
  12.         .Execute strSQL
  13.     End With
  14.  
  15.     With dbs
  16.         strSQL = "Insert into FullTable Select * from Divider"
  17.         .Execute strSQL
  18.     End With
  19.  
  20.     cnt = cnt + 1
  21.     rs.MoveNext
  22. Loop 
Aug 14 '09 #1
Share this Question
Share on Google+
8 Replies

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 14 '09 #2

P: 80
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.
Aug 14 '09 #3

P: 80
No luck. Still, toward the end of the file, records from various source tables are intermingled.
Aug 14 '09 #4

P: 80
Is there any way to disable asychronus behavior?
Aug 14 '09 #5

P: 80
On second hand, I take that back. It does seem to have worked. Thanks much!
Aug 14 '09 #6

missinglinq
Expert 2.5K+
P: 3,532
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function Wait(lngSeconds As Long)
  2.    Static bRunning As Boolean
  3.    Dim dtEnd As Date
  4.  
  5.    If Not bRunning Then
  6.        bRunning = True
  7.        dtEnd = DateAdd("s", lngSeconds, Now())
  8.        Do Until Now() >= dtEnd
  9.            DoEvents
  10.        Loop
  11.    End If
  12.    bRunning = False
  13. End Function
Linq ;0)>
Aug 14 '09 #7

P: 80
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.
Aug 14 '09 #8

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Aug 16 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.