Import Data into Related Tables?

[spacepro]

Hi Guys,

Done a search of the forum on this, but some of the answers have not been concise or don't apply to my situation.

Anyway, I want to import data into the db but how do I import into multiple related tables.

FOr example:

tblCustomer

CustID
CustName
CustAddress

tblOrders

OrderID
CustID
OrderNo

tblOrderDetails

OrderDetailsID
OrderID
Product
ProductQty

How do I import the data from excel into access into the above tables? Do I import into a temp table then transfer data to the individual tables - how would I relate the data and keeping RI rules intact.

I would like to do this through code, but need an overview from someone on how to accommplish this.

Many Thanks

Andy

 

[Mile-O]

And what's the design of the Excel spreadsheet?

 

[spacepro]

And what's the design of the Excel spreadsheet?

I knew you were going to ask!

The following format is:

Name            Address              OrderNo    Product    Qty

Joe Bloggs     Anyaddress       12345           A            23
Joe Bloggs     Anyaddress        12345           B           21
A Name           AnyStreet           123             A            100

So that Name and Adress go to Customer table.
OrderNo goes to Orders table
Product and qty go to the orderdetails table.

But the trick is that they are related.


Thanks Mile

Andy

 

[Mile-O]

Is this going to be a regular thing?

This thread may also be helpful.

 

[spacepro]

Is this going to be a regular thing?

Not at all.

Thanks Mile

Just out of curiosity is it possible to carry this out in access from a temp table putting database design aside for the minute.

I will have a look at the spreadsheet properly, but just wondered if this could be done in access.



PS. Search Me!!

Andy

 

[spacepro]

Thanks for the info Pat, much appreciated.

Andy

 

[mollycase]

not sure if this only applies to Access 2007 but...

i am new to access and i read Pat's answer and it seemed to make sense.

however, when i got to the part about creating an update query to update the PK field on the table, i was all confused. i kept hitting "view" in access 2007 to see how it would look. there was the PK field with nothing in it. just blank rows. i was SO confused. i spent, quite literally, 3 hours trying to figure it out. looking for all sorts of things about blank fields etc.

then, in sheer desperation i hit RUN. it worked.

so, important step there...hit RUN and hope you did it right

thanks for the tip Pat, i know it's a few years old, but it really helped me out!!!

 

[roi8877]

Hmm, This is an interesting angle of attack in importing tables.

I'm trying to do the same as thing as well.

My only differece is, the top level table already has existing records and its primary key is random long interger.

If I went ahead with this method, (to generate pk first using make table query) how do I ensure that the pk generated in the preparation proccess does not conflict with the existing pk in the top level table?

As a side note: actualy what I need is to put records into the main table and get its random pk and update it to the temp table..from there than I can do other updates...