Import into multiple related tables?

[pb100]

Hi - I have a procedure to import data from Excel into Access. The data
needs to go into 3 related tables: Main, Details, Extra. Main has a primary
key ID that relates to the other 2 tables in a one to many relationship. I
am able to get the data into a temp table or three temp tables in Access
(tried both methods). Where I am stuck is in appending the data to the
tables and getting the ID of the record I imported to the Main table into the
data for the other 2 tables?? Basically, just like in using forms and
sub-forms to insert new data, how do I get the Main table ID value into the
data for the other 2 tables?

I have spent many hours trying to find a solution..:} Any help is greatly
appreciated!! I may just be missing something simple...

 

[Jeff Boyce]

How do you get the data from the temp into the Main table? Is the MainID
something you pass into the Main table, or something that it generates for
you (e.g., Autonumber)?

One way to accomplish what you described is to import the data into a temp
table in Access that has its OWN ID field. Keep track in Main which TempID
you used, then use a query to join the TempID in Main to the MainID and back
to the temp table for the other data you need to parse out to your "many"
tables.


Regards

Jeff Boyce
Microsoft Office/Access MVP