XLS Spreadsheet Import to Access 2007 being rearranged

[Mike Bonnardel]

I have a strange problem. I am importing data that I am matching up for
another person. Their data is in a very specific column order. When I am
importing this data into Access 2007 the columns are moving. For example
Column 3 is now Column 5 and etc. I even am having Access create an ID
column. In Access 2000 that used to be the first column. Access 2007 is
putting it elsewhere in the data.

I am not linking to the original spreadsheet but doing an actual import.
Nothing in the wizard allows you to change move the order. This is very
strange.

The XLS file was saved in Excell 2007 but saved in 2000 format.

Any help would be appreciated.

 

[Klatuu]

I can't tell you why the order is changing, but it really doesn't matter what
order the fields are in in a table. If you want to present them in a
specific order, use a query and put the fields in any order you want.

 

[Mike Bonnardel]

Actually it does matter. In the past I would basically match up the data
and place it at the end. So it was real easy...drop all the columns in the
default order and place the data I was adding to the end.

When I gave them back the data the order was all wrong.

Now I have to go in and verify the columns with the spreadsheet either
matches the table design or verify the query order which I never had to do
before. This is a task that I do once every quarter and I don't have the
column order memorized.

For people who are not programers keeping things in the column order that
they are imported in is important. If the wizard is showing it in a certain
order that order should be maintained unless directed by the user to change
it. This is not what is happening. I should add that I am on ACCESS 2007
SP2.

Either way...something in the "lovely" import wizard is bugging out. Why
would Access create an Index called ID and not put in Column Position 1?
That never occurred in the previous version. It has occurred three tiimes to
me in the last three weeks. That is a bug. But that is only the tip of the
iceberg.

 

[Mike Bonnardel]

BTW I did check online before posting. A couple of other people have had
this problem and no one was able to help them either. I figure MS should at
least look at their own board once in a while.

 

[Klatuu]

Actually, they do check it once in a while.
Another option might be to create the table with the fields in the order you
want them in and import into the table rather than using a new table in the
transfer.

 

[Mike Bonnardel]

I do appreciate what you are saying. I understand how to do that, like I
said use this particular data only on a quarterly basis to match up internal
and external identifiers I don't keep a copy of the table lying around in my
DB for various reasons.

The real issue what is going on in the ordering of the data during import.
I have looked all over for some hidden setting that is rearranging the
columns. I don't see an option in the wizard itself. Never had this problem
until now. I was hoping someone knew of where this was controlled in the
program or options or registry or something (INI file).

The particulars are:
Access 2007 SP2. Database: Access 2000 Format.
File Imported from Excell 2007 but the file is XLS format. I get the file
from another user in XLS using I believe Excell 2003. I have to delete two
rows of calculations at the bottom that throws off Access so I save it back
with the same name and format.

Mike

May be I am trying a configuration that they never thought of.