Microsoft Access: Append Query problem

pts.
Tags:
Microsoft Access
I have a table with a surrogate key created by Autonumber. I have data (in a linked spreadsheet) which I wish to append to that table. I would like to have the new data appended with automatically generated keys, but I cannot find how to do it (without writing a specific macro). If I try to append the new data either using * or individually naming the fields, I get an error saying that the keys are null. If I add a "key" column to the spreadsheet, it will (of course) only successfully append the data if those "keys" are distinct from ones already in the table (and then it uses those keys). I do not want to do this every time I add new data.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the key field is autonumber, then in your append statement do not include that field. When you run the query, Access will automatically increment the number in that field.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • EricThomas
    [...] key created by Autonumber. I have data in a linked spreadsheet which I wish to append to that table.http://itknowledgeexchange.techtarget.com/itanswers/microsoft-access-append-query-problem/Appendix I -- Building AccessabilityThe idea wasto remove barriers to wheelchair-user access to and [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following