Record Set - Add a record does not always add to the end of the file

[pgshelton]

I have an app that takes multiple text files, imports to a table, modifies data in the records, exports back to a text file. Problem is in the order of the records after a particlar process.
The process deletes certain records, puts them in a new table, modifies them and then puts them back into the original table. This usually works fine. Records are added to the end. It appears that it will randomly (various input files) put the new records at the beginning of the table. I have changed the order of files and still no pattern or reason for the wrong order.
CODE:
tir_in is the original file, BZC records are deleted, manipulated and added back to the (usually) end of tir_in.

cn.Execute "DELETE * FROM tir_in WHERE di=" & "'BZC'"
rsTIR_IN.Open strTIR, cn, , , adCmdTable

intBZC = DCount("*", "tbl_BZC")

If intBZC > 0 Then
rsBZC.MoveFirst
Do While Not rsBZC.EOF
'Append to TIR_IN
With rsTIR_IN
'.MoveLast tried movelast but did not solve problem
.AddNew
!di = "BZC"
!ri_to = rsBZC!ri_to
!RI_From = rsBZC!RI_From
!ric = rsBZC!ric
!doc_no = rsBZC!doc_no
!b5 = rsBZC!b5
!qty = rsBZC!qty
!prep_date = rsBZC!prep_date
.Update
End With
rsBZC.MoveNext
Loop
End If

 

[GingerR]

how about when you import them, you put it into a table that has an ID (Autonumber) field?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[pgshelton]

The table has no autonumber field and none of the fields are indexed.

I have changed the code to try a query to append the BZC records to the TIR_IN table. Do not get 100% of the files built correctly. Append is defined as adding records to the end of the file. I think that somehow the eof is getting messed up, but there is no pattern.
Thanks

 

[GingerR]

I understand, what I mean this: You say you have a text file that you are importing. How about importing it into an existing empty table that has an autonumber field? then the records will be imported in their current order, and records added will be at "the end" in the order that they are added.


If the table has nothing indexed, then why does it matter where the new records are put? how do you know they are not put at "the end"? what constitutes "the end" if there is no data that designates any type of ordering? Maybe if you give us some sample data, what you expect and what is not working, it'll help explain things. Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[pgshelton]

The input text file is in a specific order and needs to be exported in the same order. Business rule.
I know that some are not at the end by looking at the resulting exported text file.
I will try adding an autonumber field to the table used for the import. I delete all records from this table prior to importing each text file. I believe the autonumber does not reset. There is a way to reset so it doesn't get crazy. If you can help with that issue also...
Thanks

 

[JoeAtWork]

There is no such thing as a "beginning" or "end" in database tables. If you SELECT records from a table and do not have an ORDER BY clause, the records will not be returned in any predictable order.

I believe the autonumber does not reset

No it doesn't - does that matter? What's the difference if the first record starts at 999 or 1, as long as the records return in the order you want them to? Since you currently don't have an autonumber, it suggests to me it is not something that the user needs to see. So in your user interface (i.e. form) simply don't display the autonumber field, but in the record source of the form make sure to ORDER BY your autonumber.

 

[GingerR]

if you compact a db and there are no records in the table, the id will begin again at 1. However, what Joe says is true....what does it matter? just don't export that field.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[pgshelton]

Thanks to both respoders. I did have to use an autonumber field to force the order of the output. The field had to be removed to produce the correct record length in the output file. The reason for the reset of the autonumber is the volume of records input over time.
Thanks again.