Access not inserting records properly

[rando1000]

Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record.

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.

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

[nico5038]

I wouldn't use an additional table, but a UNION query and add a sequence number for the order needed.

Would look like:
[code=sql]
select ID, 1 as Sequence, field1 from tblWhatTable
UNION
select ID, 2 as Sequence, field1 from tblWhatTable where Breakmark = '##'
order by ID, Sequence
[/code]

Getting the idea ?

Nic;o)

[janders468]

Could you clarify this a little, I'm having trouble following exactly what it is you are doing. You mention that you have to loop through a file and then go on to call it a table as far as I can tell. Are you ultimately needing to deal with a file or tables. What are all the fields in those tables? Is it that you have field full of id numbers and if you come across a certain number then a blank should be appended after that number (within the same column)? I apologize if I'm missing something obvious.

[rando1000]

Could you clarify this a little, I'm having trouble following exactly what it is you are doing. You mention that you have to loop through a file and then go on to call it a table as far as I can tell. Are you ultimately needing to deal with a file or tables. What are all the fields in those tables? Is it that you have field full of id numbers and if you come across a certain number then a blank should be appended after that number (within the same column)? I apologize if I'm missing something obvious.

Essentially, the table is in a certain order (with the field "pst_seqnum " being a numeric field in that order), but a blank record needs to be inserted after each record that has "##" in a certain column. I tried splitting the table into temp tables and putting the tables back together with the blank record in between, but my the orders were all mixed up. So I thought, if I insert one record at a time based on the pst_seqnum field, it would certainly be in the right order because I'm looping numerically based on this field. However, that does not seem to be the case.

[rando1000]

I wouldn't use an additional table, but a UNION query and add a sequence number for the order needed.

Would look like:
[code=sql]
select ID, 1 as Sequence, field1 from tblWhatTable
UNION
select ID, 2 as Sequence, field1 from tblWhatTable where Breakmark = '##'
order by ID, Sequence
[/code]

Getting the idea ?

Nic;o)

I kind of see where you're going, and my initial results along this line are good, so I'll look into it. Still, I'd like to find out why my records are not inserting and/or displaying properly using my method.

[nico5038]

Guess you need to check the line:
strSQL = "Insert into Split_Temp Select * from Split_Insert"

Here no WHERE clause is used.

Using a query is much faster in processing as using recordset processing. In general queries are at least 6 times faster as code..
In your case, using a temp table, the database will also need regular compacting.

Nic;o)