By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Access not inserting records properly

P: 80
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
  5.  
  6. Do While intCount1 < intTotalRecords
  7.  
  8.     With dbs
  9.        strSQL = "Insert into Split_Temp Select * FROM " & strWhatTable & " WHERE Pst_Seqnum=" & intCount1
  10.        .Execute strSQL
  11.     End With
  12.  
  13.     Set rs = dbs.OpenRecordset("Select * from " & strWhatTable & " where Pst_Seqnum=" & intCount1)
  14.  
  15.     strBM = IIf(Not IsNull(rs("Breakmark")), rs("Breakmark"), "")
  16.  
  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
  23.  
  24.     intCount1 = intCount1 + 1
  25. Loop
  26.  
Dec 30 '08 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
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
  5.  
Getting the idea ?

Nic;o)
Dec 30 '08 #2

Expert 100+
P: 112
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

P: 80
@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.
Dec 31 '08 #4

P: 80
@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.
Dec 31 '08 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 31 '08 #6

Post your reply

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