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.

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

nico5038

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

Would look like:

Expand|Select|Wrap|Line Numbers

 
select ID, 1 as Sequence, field1 from tblWhatTable 

UNION

select ID, 2 as Sequence, field1 from tblWhatTable where  Breakmark = '##'

order by ID, Sequence

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

@janders468
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

@nico5038
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)

Access not inserting records properly

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ