??? how to import excel table to existing access table

[Guest]

I am trying to import an excel table to an existing access table that has an
autonumber field.

Would appreciate some expert advice.

 

[Jeff Boyce]

Jaylin

As I recall, one of the options when you use the import wizard is to append
to an existing table. Of course, if that table has an Autonumber field, you
won't be appending a value to that field.

General descriptions lead to general suggestions...<g>

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Dear Mr Boyce

Does it mean I cannot append from an excel table to an access table that
has primary key and autonumber defined?

In order to import the excel table, should I delete the primary key and
redefine the autonumber format to number format?

 

[Jeff Boyce]

No, that means you may not be able to (successfully) append a key value to
the autonumber primary key field. I don't believe you should have any
trouble appending any/all other fields.

What happens when you try?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Dear Mr Boyce
Thank you so much again.

After I import, there is an error table produced indicating the error is due
to null value of the field where it is defined as autonumber

Does it mean I have to do append query to import the rows from excel file
instead?

Additionally, in the access table, there is a field that will record the
date & time of entry. When I import the excel file, should I leave that
column blank.

Sorry for my silly question. I am new to using access. I wonder if there
is any book or reference that I can go to help master the application better.
I ve tried the help section, but it does not really solve my problem
specifically.

 

[Jeff Boyce]

I'm confused.

If you Autonumber field is defined as a Primary Key, it won't/can't have a
Null.

If you are trying to import and append to a table with an Autonumber Primary
Key, you would NOT append to the Autonumber Primary Key field.

Thus, I don't see how you could have a Null error in that field, since
Access will create a new Autonumber for each record you try to append.

More info, please...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/