Sorting in Access

  • Thread starter Thread starter johannes
  • Start date Start date
J

johannes

If I export an Access2000 table to a text file, how can I ensure that a
sorting order by Access is strictly preserved? The textfile appears
sorted in the beginning, but this breaks down after about the 1,292,960'th
record!
 
Realistically, you can't. Tables don't have a "sorting order": they're
"sacks of data", where the rows are stored wherever the DBMS sees fit to put
them.

If the order is important, create a query with an appropriate ORDER BY
clause, and export the query, not the table.
 
OK, thanks.

Douglas J. Steele said:
Realistically, you can't. Tables don't have a "sorting order": they're
"sacks of data", where the rows are stored wherever the DBMS sees fit to put
them.

If the order is important, create a query with an appropriate ORDER BY
clause, and export the query, not the table.
Click to expand...
 
just use Access Data Projects and you can use a real ETL tool if you
buy the 'SQL Server 2000 Developers Edition' it is only $49.

it makes you about 100 times more powerful than any of this MDB crap.

with SQL Server; you _CAN_ specify the ordering of rows in a table; it
is called a 'clustered index'

MDB has been obsolete for 10 years now; it's not stable or reliable
enough for real world use

-Aaron
 
You can also sort rows with Access2000, but ordering is not necessarily
preserved when exporting to a text file. That was the problem. It may
superficially look sorted OK, until you inspect the million'th row or so.
Anyway, I have solved it by a little VB proggy.

Another export pitfall is if you swap columns around by moving the column
names in table view. This could be done because a program reading the
textfile, require a particular field order. Then the textfile export has
also moved the columns, but not moved the column names in row 1! Highly
dubious if the swapped columns contain similar data.
 
Back
Top