Why does compacting a Access 2000 db changes the order of records in a table?

Question

As the question says. The table has no primary key defined. Other tables in the database do not change after compacting, and have as well no primary key defined.

Answer 1

Without a primary key, Access is free to rearrange the rows of a table as it sees fit. My guess is that the effect depends on the usage pattern for the the table. When you delete a row, the database simply marks it as deleted. When you compact, it reclaims the space by overwriting deleted rows with a non-deleted rows. If a table has a primary key, then the database will be careful to preserve the row ordering during this process (which is consequently a much more expensive operation); otherwise, it won't.

Answer 2

"If a primary key exists in the table, compacting re-stores table records into their Primary Key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient."

ACC2000: Defragment and Compact Database to Improve Performance

"New compacting method. Compacting the database now results in the indices being stored in a clustered-index format. While the clustered index isn't maintained until the next compact, performance is still improved. This differs from Microsoft Jet 2.x where rows of data were stored the way they were entered. The new clustered-key compact method is based on the primary key of the table. New data entered will be in time order."

New Features in Microsoft Jet Version 3.0

What neither of these articles tells you is something that must be observed: that if a UNIQUE constraint (or unique index) exists on NOT NULL columns then this will be used in lieu of the PRIMARY KEY. What I haven't been able to ascertain is how ACE/Jet picks one if there are multiple UNIQUE constraints on the table: first created? first column's ordinal position? first NOT NULL column's ordinal position? Your guess is as good as mine.

Answer 3

Tables don't have order. A table datasheet may, but that's not the table, but a UI object used to display the table.

If you want to control the order of your table, use a SQL statement with an ORDER BY clause.

You shouldn't be using table view in an application.

If you don't have an application and you don't like the default order, set the ORDER BY property on the table datasheet and save it, or write a query with the order you like.

But you need to give up the idea that tables have any order at all. The mere idea is completely contradictory of the basics of SQL and set theory. You shouldn't care what order the records are actually stored in the physical database.