Importing from excel and linking tables 1

[ajking]

I have an excel workbook which is emailed to me from different people. In this workbook there are 3 sheets Techinfo;Customerinfo;requestinfo
I have created an access database with 3 tables that have the same name. The data imports ok (It would be great if you could import all 3 sheets at the same time oh-hum!) and I have set them up to append new data to the equivalent table.
The Customer table has an extra field (Customer_ID) which autonumbers at each new import; the other tables have an extra field for a record number.
What I need is for the record number fields in the 'Techinfo' and 'Requestinfo' tables to be populated with the 'Customer_ID' from the 'Customerinfo' table.
Does anyone have any suggestions how to go about this. TIA

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[kjv1611]

Sounds like you just need a query for updating that on a regular basis.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

To be more specific, you need an update query to pull the CustomerID from the Customer table, and update the other 2 tables.

And actually, if you can save all 3 of these worksheets to the same folder, you can sort of import them at once USING VBA for sure.

And then you could also run your Update query in the same VBA procedure.

--

"If to err is human, then I must be some kind of human!" -Me

 

[ajking]

I was thinking along those lines but I am wondering now if I could carry out the whole import process programmatically using VBA

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[kjv1611]

Isn't that sorta what I just said?

To be more specific, you need an update query to pull the CustomerID from the Customer table, and update the other 2 tables.

And actually, if you can save all 3 of these worksheets to the same folder, you can sort of import them at once USING VBA for sure.

And then you could also run your Update query in the same VBA procedure.

If you wanted it 100% in VBA, you could create a SQL query in the VBA code, and just DoCmd.RunSQL strYourSqlQuery

--

"If to err is human, then I must be some kind of human!" -Me

 

[ajking]

Yeah, you're right. Put it down to a very senior moment, late on a friday afternoon, after a week of my boss saying "This is what I want! You are the one to do it! and and I want it now!"
There's a bottle of Chardonnay waiting for me at home

Many thanks


'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980