Access sorting problem?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have an .mdb file in which I have two linked tables. When I insert records
in the "slave" table and then query for those records, they appear NOT
ordered by their primary key (indexed autonumber) - even though the field is
INDEXED. This causes major problems in our application. Sometimes if I insert
40 new records, the record with the bigest index appears first, then the rest
of the records. Sometimes, the last 5-6 records appear first, then the rest.
Now, after I compact the database, the problem is fixed. However, if I
insert another set of records, the problem re-appears with the lastly
inserted records.
This seems to happen with the Access 97 format, I have not reproduced it
with 2003 format.
Has anyone seen anything like this? I can provide the .mdb file on demand.
It is really interesting.

Ivan
 
You should never assume anything about the order in which records are stored
in tables.

If the order is important, make sure that you have a field that can be used
to sort on, and create a query with the appropriate ORDER BY clause.
 
Thanks,
I thought that using an indexed primary key guarantees that the recordset
will be sorted by this field by default unless ORDER BY... is specified.
 
Ivan said:
Thanks,
I thought that using an indexed primary key guarantees that the
recordset will be sorted by this field by default unless ORDER BY...
is specified.
Click to expand...

Nope.
 
Thanks,
I thought that using an indexed primary key guarantees that the recordset
will be sorted by this field by default unless ORDER BY... is specified.
Click to expand...

No, there is NO such guarantee. A Table is an unordered "heap" of
data. Access will present the records in whatever order is most
convenient, unless you have a query with an Order By clause.

A table datasheet will (AFAIK) always be presented for viewing in
primary key order - but even this is done by a behind-the-scenes
query, and has no bearing on the actual order of storage of records on
disk.

John W. Vinson[MVP]
 
Bu this is as expected. Indexes speed up data retrieval, they do not order
the records.

Unless you specifically order the records using something like a Query, you
cannot guarantee the sequence.
 
While I agree in principle with your statement about unordered heaps
of information in tables, it is not quite true that Access presents
the records in the most convenient order. If you open a table and sort
it by any means, then close the table, Access will offer to "save" the
table (whatever that means in the absence of a design change...).
Subsequently opening the table again will retain the sorting order.

Where is this info stored? Open the table in design mode. Access will
complain if it's a linked table, but do it anyway. Right-click on the
design grid, and choose "Properties" to see the sorting order.

Would an experienced developer care about the sorting order on a
table? Probably not. Would a new user be tempted to apply sorting
order to a table? Probably yes. Does that sorting order get applied to
recordsets opened via code? No. Is that confusing? Yes...

I agree with you that users should be taught to use queries, rather
than open tables directly, because *SQL* tables are unordered heaps of
information. But *Access* tables are not necessarily unordered.
Microsoft "thoughtfully" provides a tool to persist the sorting order
in the Access interface. I suppose it is a byproduct of "making it
easier for end-users of Access" even if it ultimately muddies the
waters and promotes the use of bad practices.



No, there is NO such guarantee. A Table is an unordered "heap" of
data. Access will present the records in whatever order is most
convenient, unless you have a query with an Order By clause.

A table datasheet will (AFAIK) always be presented for viewing in
primary key order - but even this is done by a behind-the-scenes
query, and has no bearing on the actual order of storage of records on
disk.

John W. Vinson[MVP]
Click to expand...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
Thanks to all for the feedback. It looks like the application should have
taken care of the ordering using Order By clause. It is only unfortunate that
until recent versions of the MDAC libraries for some reason the table was
always ordered by the primary key (even if not guaranteed to...) and only
lately the MS libraries started expressing the "non guaranteed" behavior....
Kind of misleading...
 
Back
Top