470,871 Members | 2,335 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,871 developers. It's quick & easy.

Access not inserting records properly

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
  1. With dbs
  2.       strSQL = "Delete from Split_Temp"
  3.      .Execute strSQL
  4. End With
  6. Do While intCount1 < intTotalRecords
  8.     With dbs
  9.        strSQL = "Insert into Split_Temp Select * FROM " & strWhatTable & " WHERE Pst_Seqnum=" & intCount1
  10.        .Execute strSQL
  11.     End With
  13.     Set rs = dbs.OpenRecordset("Select * from " & strWhatTable & " where Pst_Seqnum=" & intCount1)
  15.     strBM = IIf(Not IsNull(rs("Breakmark")), rs("Breakmark"), "")
  17.     If strBM = "##" Then
  18.         With dbs
  19.             strSQL = "Insert into Split_Temp Select * from Split_Insert"
  20.             .Execute strSQL
  21.         End With
  22.     End If
  24.     intCount1 = intCount1 + 1
  25. Loop
Dec 30 '08 #1
5 1952
3,080 Expert 2GB
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
  1. select ID, 1 as Sequence, field1 from tblWhatTable 
  2. UNION
  3. select ID, 2 as Sequence, field1 from tblWhatTable where  Breakmark = '##'
  4. order by ID, Sequence
Getting the idea ?

Dec 30 '08 #2
112 Expert 100+
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.
Dec 30 '08 #3
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.
Dec 31 '08 #4
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.
Dec 31 '08 #5
3,080 Expert 2GB
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.

Dec 31 '08 #6

Post your reply

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

Similar topics

49 posts views Thread by Yannick Turgeon | last post: by
reply views Thread by | last post: by
4 posts views Thread by lupo666 | last post: by
5 posts views Thread by dos360 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.