Trying to import from Excel sheet but order gets messed up

[Danny]

Hi again

I am trying to import from an excel spreadsheet into access 2002, but the
order that is in the spreadsheet is not preserved when I import. using
DoCmd.TransferSpreadsheet in code.

Access sets indexes on certain fields with "Code" in the field. Even if I
remove these index, the order is close, but still some of the fields are out
of order.

There is no true order to this spreadsheet, and it must stay in this order.

What can I do?

Thanks again

[Tom van Stiphout]

On Sat, 02 Oct 2004 15:42:46 GMT, "Danny" <da********@hotmail.com>
wrote:

So it should stay in this "no true order", huh?

I would add a column: LineNumber, and import that as well. Then in
Access you can sort by that column.

I think the Auto-Index feature of Access is mostly more of a
hindrance. Turn it off in Tools/Options/Tables-Queries/AutoIndex.

-Tom.

Hi again

I am trying to import from an excel spreadsheet into access 2002, but the
order that is in the spreadsheet is not preserved when I import. using
DoCmd.TransferSpreadsheet in code.

Access sets indexes on certain fields with "Code" in the field. Even if I
remove these index, the order is close, but still some of the fields are out
of order.

There is no true order to this spreadsheet, and it must stay in this order.

What can I do?

Thanks again

[Pieter Linden]

Simple solution. Add an autonumber primary key to your table and
you're done. The PK will determine the sort order, and will force the
records to remain in import order. I think you're making a
fundamental mistake about database tables - they're *inherently
unordered*. If you want them ordered, apply a filter or open a query
based on the table... In Excel you can do something like this:

A3=A2-1
(This record's value equals the value of the previous record minus
one.) Doing that in Access, you need to specify a sort order as there
is no inherent record order in your table...

[Jamie Collins]

pi********@hotmail.com (Pieter Linden) wrote ...

Your reply is slightly confused:

Simple solution. Add an autonumber primary key to your table and
you're done.
You are assuming the table currently has no primary key, which would
be unusual (technically, it would be a heap rather than a table <g>).
You could've simply suggested adding a IDENTITY/autonumber column, but
why make it the primary key?
I think you're making a
fundamental mistake about database tables - they're *inherently
unordered*.

I think you meant to say, 'if no ORDER BY clause is specified, their
order is not guaranteed'. In practice, the 'default' order will be the
table's physical order (i.e. order on disk) which is determined by the
clustered index. For Jet, the primary key is the clustered index but
new rows are only physically reordered when the database is compacted.

Rather than rely on coincidence and default behavior, it would be
better to add an explicit row ID to the Excel data and ensure this
imported into the database.

Jamie.

--