Thread: Data flow from linked table to excel

Hi,
Need help in flowing data in the below case;
I am combining data from 5 different excel workbooks linked to an access DB into a seperate master excel workbook through a union and select query. Now, is there a way to update a few columns on the 5 different excel workbooks
after I make changes to the consolidated master excel workbook?

current steps:
1) Create 5 different linked tables on Access from Excel
2) write a union query to consolidate data from the 5 tables
3) write a customized select query to retrieve data from step 2
4) import step 3 data on a master excel sheet

Now, I make changes to the master file (step 4) which I want to flow back to the 5 different excel sheets based on the unique identifier that exists on all the sheets. That is, I want to link the step 4 (master excel sheet) to the same access DB and write an update query to update the 5 different linked tables.
Is there a way to accomplish that?
Thanks for your help.

Originally Posted by alansidman

Have you tried it and if you have, what have you experienced. It seems that you should be able to do a simple linking of the Master Sheet to Access in the same manner you linked the individual sheets. Once it is linked it should be available as a record source to run update queries on your individual tables. Do you know how to run update queries. If not, there are some good tutorials on you tube. Just google MS Access Update Query Tutorial.

Alan

Thanks Alan for your response,
I wasnt sure if what I was doing was the right way,

However, i wrote this sample update query that gives me a "Operation must use an updateable query" error..

UPDATE Finance AS A INNER JOIN Master AS AI ON A.[ITR #]=AI.[ITR #] SET A.[Cross functional Business Units] = AI.[CROSS FUNCTIONAL BUSINESS UNITS];

In the above query, finance and master are both linked tables. I have to perform similar update operation on 5 different tables.

I checked with the permission and I do have full control.
Please advise what am I doing wrong
Thanks