Unanswered: Import/Append to Several Existing MS Access Tables, All Related by Primary Key
I have been able to get some components of this working, but never all at once. Not for lack of trying or researching.
I'm hoping somebody could point me in the right direction please!
GOAL is to be able to do both of the following...
Manual Record Entry
Open my form in MS Access and add new/edit existing records as needed.
Works 100%, no help needed here.
I have a form created, with tabbed control containing the related sub-forms. All related by the autonumbered DBID.
Import External Records
Import from 1 excel worksheet
Append specific columns of the worksheet to six different existing ms access tables
Create, and have the imported information in all 6 tables linked by the "DBID", which is an autonumbered field.
So my question is really:
When importing/appending new records, how do I generate a new DBID (autonumbered field) that will be carried through to all 6 tables?.
The end result being that I can open my main form, and pull related data together from all six tables.
Thanks for the post. I currently have 6 append queries I run one by one to append data from the one imported worksheet to my 6 access tables. (As far as I know I can't have one append query move data to 6 tables right?)
My problem is if I generate the autonumbered DBID after running my first of six append queries, how do I assign that SAME DBID to the related data I will append in the remaining five append queries.? Or how will MS Access know the data are related?
Another angle would be to create a unique ID on the excel document, append that ID to all six ms access tables, and use it to create relationship between all 6 tables, but then I am unclear on what to do when I want to create new records through my main MS Access form.