Import Excel into Access - Handling Autonumbers

[forsythe303]

I have an Excel spreadsheet on my computer that I want to use as a front-end
for an Access dB on another machine.

My Access key field is an autonumber, which I have no way of knowing the
value of at the time of entering information into the Excel spreadsheet.

What can I put in the Excel spreadsheet to get Access to accept the rest of
the record and autonumber for me?

Ex. Access Fields
(AutoNumber) Name Address

Ex. Excel Fields
???? Name Address

 

[Jamie Collins]

What can I put in the Excel spreadsheet to get Access to accept the rest of
the record and autonumber for me?

AFAIK you cannot assign a value to an autonumber column, not even
NULL. Simply omit the autonumber column from your INSERT DML.

an autonumber, which I have no way of knowing the
value of at the time of entering information into the Excel spreadsheet.

That is incorrect. One approach is to use a recordset behind the
scenes: add the cell values to a new record in the recordset and write
the generated autonumber value from the recordset back to the
worksheet. You may need to use a disconnected recordset or a
transaction to ensure data isn't written uncontrollably to the
database.

Jamie.

--