Trying to import from Excel sheet but order gets messed up

  • Thread starter Thread starter Danny
  • Start date Start date
D

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
 
Danny,

This is an Access question, I think. You might need to put a column of
consecutive numbers (The fill handle will do it with the Ctrl key held) to
ensure the order once in Access.

Please don't post a question to more than one newsgroup.
 
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...
 
(e-mail address removed) (Pieter Linden) wrote ...

Your reply is slightly confused:
Simple solution. Add an autonumber primary key to your table and
you're done.
Click to expand...

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*.
Click to expand...

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.

--
 
Back
Top