So here's my method. I created two tables with the same structure as the table I'm inserting from. One table, Split_Temp, is the one I'll be inserting to. The other table, Split_Insert, contains the "Blank" record, which actually just has the word "Blank" in an identifier field.
The problem is, after a while, the records stop inserting in order. The numbers in my Split_Temp table start jumping around in groups of < 20, so 116 might be followed by 122, 123, 124, 125, 126, 127, 117, 118, etc. I checked to make sure none of the fields have an index (so that I'm not just being shown the data in a certain order based on an index). There are no indexes.
Expand|Select|Wrap|Line Numbers
- With dbs
- strSQL = "Delete from Split_Temp"
- .Execute strSQL
- End With
- Do While intCount1 < intTotalRecords
- With dbs
- strSQL = "Insert into Split_Temp Select * FROM " & strWhatTable & " WHERE Pst_Seqnum=" & intCount1
- .Execute strSQL
- End With
- Set rs = dbs.OpenRecordset("Select * from " & strWhatTable & " where Pst_Seqnum=" & intCount1)
- strBM = IIf(Not IsNull(rs("Breakmark")), rs("Breakmark"), "")
- If strBM = "##" Then
- With dbs
- strSQL = "Insert into Split_Temp Select * from Split_Insert"
- .Execute strSQL
- End With
- End If
- intCount1 = intCount1 + 1
- Loop