Access Index files?

[moonlighter]

Does Access have index files? How do you work with them?

Our program is doing some bizarre stuff. The
rst.MoveFirst command goes to the SECOND row in the table
instead of the first. The FindFirst command behaves as if
it were commented - nothing results.

When I export the table in question to an Excel
spreadsheet, and then import it back into the program, it
works fine.

Anyone?

Thanks in advance.

Bill

 

[moonlighter]

Interesting. What does determine the order in which the
recordset is retrieved?

We simply use a Set statement to assign the recordset to
a recordset variable. We assumed the order in the table
was the order of retrieval. (I did read 700 pages of
Running Microsoft Access, maybe that was on pg 800.)
Sounds like we should be doing this with an SQL Select
statement. Do you think that would solve our problem? How
about the FindFirst issue? We put a FindFirst statement
in there and Access simply stays put at the row it was on
before the FindFirst, despite it not meeting the criteria.

Thanks again.

Bill

 

[John Vinson]

Interesting. What does determine the order in which the
recordset is retrieved?

The order in which the recordset is retrieved is whatever order the
Access query optimizer finds convenient. This might be table storage
order, primary key order, or some other unpredictable order.

We simply use a Set statement to assign the recordset to
a recordset variable. We assumed the order in the table
was the order of retrieval.

Since Tables are unordered, "bags" of data, this assumption was the
source of your problem. A table HAS NO ORDER. New records will not
necessarily be stored at the end of the table, but wherever there is
room.

You need to create a Query with an Order By clause to get the records
in a controlled order. If there is an Index on the field you're using
in the order by, Access will use it to speed the sorting - but you
don't need to (in fact cannot) explicitly reference the index by name.

 

[moonlighter]

Thanks. Now I can stop banging my head against the wall!
Bill