Import

[Richard via AccessMonster.com]

Hi all,

How can I append or import one spreadsheet into two related tables? I have an
empty database that has two tables’ 1:M. Part of the spreadsheet goes into
the main table and the rest goes into the sub table. I would like it to
happen in one operation if possible.

Thanks for any suggestions
Richard

 

[Phil Smith]

Each section should have a range name. Use TransferSpreadsheet in a
macro to import each piece.

 

[KARL DEWEY]

I would bring the spreadsheet into a Temp table and then append. You will
need a primary key in one side table and foreign key in the many table.

If you want to use an autonumber then you will need to join one side table
with the Temp table so that the autonumber is appended into the foreign key
field.

 

[John W. Vinson]

Hi all,

How can I append or import one spreadsheet into two related tables? I have an
empty database that has two tables’ 1:M. Part of the spreadsheet goes into
the main table and the rest goes into the sub table. I would like it to
happen in one operation if possible.

Thanks for any suggestions
Richard

I'd use File... Get External Data... Link to link to the spreadsheet (rather
than importing it) and then run two append queries from VBA code (using the
Execute method, which traps errors and avoids annoying popup messages, rather
than RunSQL).

The details will depend on the tables and on the nature of the data.