physically sort records in a table

[Jamie Collins]

If we're talking about data in Access, I don't think you have to do any
recompacting to get the data to show up ordered by the PrimaryKey. At
least, in my test case I was able to type in key values in descending order
on successive rows, close the table, and reopen it, without compacting, to
find the data sorted exactly as I expected.

Try something like this:

CREATE TABLE Test6 (ID INTEGER NOT NULL PRIMARY KEY)
;
INSERT INTO Test6 VALUES (3)
;
INSERT INTO Test6 VALUES (2)
;
INSERT INTO Test6 VALUES (1)
;
SELECT ID FROM Test6
;

Until the file is compacted the rows will be returned in order
date/time inserted. After compacting, the rows will be returned in
order of the primary key.

Jamie.

--

 

[Rick Wannall]

Thanks for the SQL. I ran the SQL that you posted, in the order given and I
did not see the behavior you're describing: that values are not returned in
order by primary key. They are.

I'm in Access 2003. Could it behave differently from other version on this
score?

 

[Rick Wannall]

Thanks for the SQL. I ran the SQL that you posted, in the order given and I
did not see the behavior you're describing: that values are not returned in
order by primary key. They are.

I'm in Access 2003. Could it behave differently from other version on this
score?