TransferSpreadSheet import excel to access problem (undesired reordering)

[qq]

Hello,
I've got a problem importing a worksheet Excel into a temporary table
in
access. All the information is imported but the table is not ordered
as
it is originally in excel.

I'm using a VBA code rather simple as the following:

Private Sub Import()
...
Dim TableName As String
DoCmd.TransferSpreadsheet , , TableName, XLFileName, , test!"A10:AX"
....
End Sub

where XLFileName is my excel file and "test" my worksheet within that
file.


This code imported perfectly all the records from the excel file but
since the worksheet increased number of lines up to 1800 (which I do
not
consider is a big number!) all of them are imported but after the line
35 (not always the same) jumps to te line 1400 or so, then comes back
to
the line 36 until 80, then again another jump...
Despite of having all data the different order does not help
afterwards.

Do you have an idea about this strange re-ordering? Any help out
there?

Thanks beforehand (sorry for the cross-posting)
Qq

 

[Joseph Meehan]

Hello,
I've got a problem importing a worksheet Excel into a temporary table
in
access. All the information is imported but the table is not ordered
as
it is originally in excel.

I'm using a VBA code rather simple as the following:

Private Sub Import()
..
Dim TableName As String
DoCmd.TransferSpreadsheet , , TableName, XLFileName, , test!"A10:AX"
...
End Sub

where XLFileName is my excel file and "test" my worksheet within that
file.


This code imported perfectly all the records from the excel file but
since the worksheet increased number of lines up to 1800 (which I do
not
consider is a big number!) all of them are imported but after the line
35 (not always the same) jumps to te line 1400 or so, then comes back
to
the line 36 until 80, then again another jump...
Despite of having all data the different order does not help
afterwards.

Do you have an idea about this strange re-ordering? Any help out
there?

Thanks beforehand (sorry for the cross-posting)
Qq

Access and databases in general do not have an order. The data is
stored like in a bucket. The way it appears can change at any time.

If you want any specific order, you have to provide a means of ordering
it and then you can sort on that order in a query, form or report.

Note: autonumber should not be relied on to provide an order. You can
roll your one using the DMAX function.

 

['69 Camaro]

Hi, Enrique.

All the information is imported but the table is not ordered
as
it is originally in excel.

A database table is not a spreadsheet. The data isn't guaranteed to be
stored in a particular order until the database is compacted, and then rows
are stored in the order of the primary key. Unfortunately, there's no way
for Jet to keep these rows in perfect order when records are updated or new
records are added to the table.

If you want to see the rows in a particular order, then use a query with an
ORDER BY clause on the columns you want to sort by.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.