import/export headache

[Howard]

I import/export txt files using MS Access 2000
acImport/acExport. When the text file was imported into an
Access table, I (SOMETIMES, BUT NOT ALWAYS) find that the
data sort order has been changed in the table.

For example, the sort order in the original text file is:

A
B
C
...
...
Z

After imported into the table, the sort order becomes:

C
A
Z
...
...
D

Same situation happens to the case of exporting from
Access table to a .txt file.

Why does it happen? How to avoid? Please help.

Thanks in advance.

Howard

 

[Kevin3NF]

No need to avoid. Access does not store records in any particualr order.
If you need to present them that way, create a query incding an order by
clause to sort them. From there you can show them on a form, report, or
export the query results.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Dirk Goldgar]

I import/export txt files using MS Access 2000
acImport/acExport. When the text file was imported into an
Access table, I (SOMETIMES, BUT NOT ALWAYS) find that the
data sort order has been changed in the table.

For example, the sort order in the original text file is:

A
B
C
..
..
Z

After imported into the table, the sort order becomes:

C
A
Z
..
..
D

Same situation happens to the case of exporting from
Access table to a .txt file.

Why does it happen? How to avoid? Please help.

Thanks in advance.

Howard

The order of records in a table is undefined, according to the
relational database model. In the absence of an explicit Order By
clause in a query, records may be returned in any order that Access
finds convenient. As it happens, Access will generally return records
in order by the primary key, if there is one and no other order is
specified, but this behavior is completely arbitrary.

If the table you are importing into has no primary key defined, then you
cannot rely on any record sequence, at any time you process those
records, unless you use a query with an Order By clause (or open a table
datasheet after specifying an Order By property for the table). If you
are exporting the records and want the output to be in a particular
record order, don't export from the table directly; instead, export a
query that explicitly sorts the records the way you want.

 

[Howard]

Thanks a lot, Kevin!

-----Original Message-----
No need to avoid. Access does not store records in any particualr order.
If you need to present them that way, create a query incding an order by
clause to sort them. From there you can show them on a form, report, or
export the query results.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm




.

 

[Howard]

Dirk,

Thanks very much for your quick and detailed response.

Howard