Post by Tony Toews
On Mon, 16 Apr 2012 08:07:56 -0700 (PDT), eighthman11
Post by eighthman11
The problem is when doing the bulk insert I have to have the records
insert in the exact same order as the source file or I can't loop
through the records and update the proper "day worked". The bulk
insert; inserts the record in the exact same order as the text file
about 90 percent of the time, but occasionally it just goes crazy.
You wouldn't happen to have multiple users inserting records at the
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
I really do appreciate all the replies. Manipulating the source file
seems to be the most full-proof solution. Although some of these
files could be 10,000 rows or more and messing with the source file
always sucks and makes the import process more cumbersome.
If someone from Microsoft is saying this is not an issue then they are
wrong or I am missing something in my Bulk insert code (see original
My loop has nothing to do with the order in which the records are
imported to the table. The records are already in the wrong order
before the code gets to the loop.
Bob is correct when he says I cannot ORDER the source file. The file
is a mess with nothing standard to base an ORDER BY on except the
original order of the rows in the source file.
The fact is after running the stored procedure sever dozen times I
have come to the conclusion that the order of the records is correct
almost 90% of the time, but 10% wrong in my mind is always wrong.
So here is what I did please don’t hesitate to tell me how bad this
is. I changed my bulk insert to start at row 13 (FIRSTROW=13). If
the insert is in the correct order field 1 will NOT be null. I
populate a variable with the value of the first field using the TOP
syntax. I then start a WHILE loop that will stop after 5 tries or
until Row 13 Field 1 is not null. If it goes into the loop (which
means Field 1 is null) I delete the records in the temp table and do
the bulk insert again. Crazy huh?
I have tested this over 100 times and he has worked every time. I
know that does not mean it is 100% but if it doesn’t work the user
does not get any data on their report and they can try to run the
report again. If the second try doesn’t work then I will get involved
and manipulate the source data. I do have high hopes that I will not
have to get involved with very many of the imports.
Once again thanks for all the suggestions. If any of you guys hear
anything about a fix for this I would really appreciate you posting it
to the group.