Question Odd Behavior

[cuttysark55]

I have an Access 2000 database of Color data containing over 3000 records.
One table is "ColorsByName" - Color names sorted alphabetically.
Some names contain spaces (ie, Antique White)

I read the table using the following code in part :

Dim Field As Integer ' Field counter
Dim strColors() As String ' Array of strings

Redim strColors(0 to RecordCount - 1, 0 to FieldCount - 1

' Load Array
For Row = 0 To RecordCount - 1
Field = 0
For Col = 0 To FieldCount - 1
strColors(Row, Col) = CStr(rsADO.Fields(Field).Value)
Field = Field + 1
Next
rsADO.MoveNext ' Step Next record
Next

Everything works fine until a block of names is skipped only to be inserted later down the list. It does this a couple more times before the read is complete.

In other words, the data is not read sequentially as it should be.

Does anyone know what the problem is?
More to the point, how do I fix it?

Thanks for your help,

cuttysark55

 

[WayneRyan]

Cutty,

You don't show the SQL for filling the recordset.

My guess would be that there is no "Order By" clause.

Try explicitly sorting by name.

Wayne

 

[plog]

One table is "ColorsByName" - Color names sorted alphabetically.

No, you're wrong. There's never order in a table. There's no next, no prior, no 4th record, no last record. Table's are just a collection of records, thrown in a big bucket of memory. If you want order to exist on a set of data, you have to explicitly tell the computer to apply that order by using a query with an ORDER BY clause.

You don't actually provide the code where you read the recordset, so my guess is you are just reading the table and hoping it sorts in the manner you want. Again, to actually sort in the manner you want, you must use an ORDER BY clause. So set the record set to a query that does that instead of just the table directly.

 

[cuttysark55]

Thanks Wayne for your quick response and correct solution

It worked perfectly. Well, almost.

Actually, I ended up Ordering on the autonumber Index because two of the colors "CG Blue" and "CG Red" were not in the proper order alphabetically. The sort was done using LCase when building the Table. This would put them out of order when Access sorted the Color names.

It never occured to me to use an ORDER BY clause since the Table was already sorted. Very interesting how that solved my problem.

Another curious thing, my database also contains other Tables (ColorsByHue, ColorsByColor, etc.) with only about 1000 colors. These tables read correctly. It was only the larger Table (3000 plus colors) that acted up. Anyway, I have added ORDER BY clauses to all of the SQL statements.

You obviously know your stuff. Thanks again.

cuttysark55

 

[cuttysark55]

Thanks PLOG

Both you and Wayne hit the nail on the head.

See my reply to Wayne.

You provided me information that I never knew about a RecordSet. I didn't realize that the memory RecordSet was not a mirror copy of the Database. I am 81 years old and learn new things every day.

Thanks again,

cuttysark55