Solved Import Excel file into existing table with an autonumber primary key field

[RevJeff]

Hello everyone,

I'm trying to import an excel spreadsheet using VBA into an existing table that has an autonumber primary key as the first field. How can I import the spreadsheet starting with the second field in the table?

I hope that makes sense.

TIA

 

[Gasman]

Link to it, and use a query to select the columns you want?

 

[Mark_]

Having to do something like this, I have an app just to do the import.
I link to the excel file and have a query to verify what I am importing.
I link to the table and have an append query to update the table itself.

Doing an append query means the autonumber primary key works as intended. You don't need to worry about how it is set, Access handles it for you.

Biggest issue is making sure that Access understands exactly what you expect to be loaded from the excel table. Using a query allows you to make sure numbers are seen as numbers, date/times are read properly, and data isn't truncated.

 

[RevJeff]

It was a lot easier to just move the autonumber primary key field to the last field in the table.

 

[Gasman]

It was a lot easier to just move the autonumber primary key field to the last field in the table.

Not what you asked, How can I import the spreadsheet starting with the second field in the table?, as you will still be importing a field, you do not really want?

 

[Mark_]

It was a lot easier to just move the autonumber primary key field to the last field in the table.

If you do this, you are asking for trouble the moment your spreadsheet contains a duplicate to what is in your database. Unless you have a really really good reason to do so, let Access do the work of keeping the autonumbers straight. Less work and headaches for you.

 

[Isaac]

Name a Range in Excel and then point the access import to the named range.
(many ways, this is just one suggestion)