OpenRecordset does not open to first record?

[HartJF]

I've had this problem on several occasions, but I always assumed the error was something else. The current episode is the simplest, so I don't see another error to blame.

My goal is to parse several .txt reports. My table has two fields, ID, an autonumber, and strRaw, an 80-character string. The only index on the table is the PrimaryKey, ID. The table is populated by importing atext file into strRaw. The lowest ID is for the first line of the report. No problems so far.

My parsing routine uses a very simple instruction:
Set rstRaw=CurrentDB.OpenRecordset("tblRaw")
to open the table. After stepping through this instruction, I enter
Debug.Print rstRaw!ID
in the immediate window. The ID is several hundred records into the 4000-record table. I repeat the process; the results are the same. I delete the data and import another report. The ID just after opening is again somewhere in the middle of the file. Repeating the process yields the same record on the same data, but different records on different data.

Where have I gone wrong? I didn't want to open the table dbOpenForwardOnly; the reports are often so long that I don't want all the data in memory, even though I make just one pass through it. (I guess I could if that would solve the problem) Do I have to open any sequence-critical table as a dynaset from a query with an Order By clause?
Set rstRaw=CurrentDB.OpenRecordset("SELECT * FROM tblRaw ORDER BY PrimaryKey")

Thanks for your help!

 

[boblarson]

The key here is to know that tables do not store data in a particular order. It is like a big bucket which everything gets thrown into. So, the key, is if you want to have a specific order is to use a QUERY to sort the records so that the records are ordered the way you want.

Just because you open a form and see a particular record as the first record doesn't mean it will be that way when you open a recordset or a query. To have order, you must impose order on an applicable field. Most of the time a date/time stamp works well.

 

[HartJF]

That makes sense, Bob. I was hoping (bad decision) that the data was in PrimaryKey order, as it is when opening the table through the UI. Once again, quick and sloppy yields sloppy results.

Thanks for the answer, Bob.

 

[gemma-the-husky]

going further - if the order matters, then you HAVE to have some objective sequence avaialble either by sorting on existing data, or adding your own numeric sort to the data

(now the thnig is - the order shouldn't mtter within the table - since in a dbs - each row should stand on its own - so if it does, in that data in one row depends on data in another - then there is most likely a data analysis and normalisation error

it's also getting used to the idea that a dbs is best at accumulating totals for sets of data, and not managing runnnig totals and comparing rows of data.)

 

[HartJF]

Thanks for your reply, Dave.

This data are reports from an unattached system. (It's a DEC VAX system run on users PCs by terminal emulation. It's been phased out, but I have legacy data to retrieve.) The only way I could retrieve it without re-entering it manually was by this import/parse. One logical record could cover eight to twelve lines (input records), depending upon the data included. If the input was handled in a haphazard order, details for one logical record could be posted to another... or disregarded entirely. The crux of this project was to collect this data into a normalized system.

You and Bob have confirmed that I must explicitly sequence my data, if sequencing is important. I cannot trust a table-based recordset to implicitly follow the primary key.

I appreciate your insight! Thanks again!