Hi

I would be most grateful if anyone can provide me with some reading material on being able to lookup from and access dB to excel spreadsheet to replace the data in Access with that containeed in the excel s/sheet?

I have a tblMaster in Access - this is split up into 5 excel spreadsheets (using TransferTo cmb) and distributed for updating - on return, the dBase needs to look at the 5 s/sheets (stored in dedicated directory) and replace the data in tblMaster with the updated info in excel.

Thanks

Joost

If you Excel sheet is set up as a proper Excel "database" you can link to the worksheet in your database and then create an update query using the excel data as the data source for the query. A 'proper Excel database' has a starting row of field names, no blank rows until the end of data, and no blank columns 'inside' the data.

Glenn

Hi Glenn

Many thanks for your reply. The excel sheets are published in excel from the dB using the TrasferTo cmd therefore the excel sheets are definately in "database" format (Am I correct).

I fully understand how to do update queries however in this instance I have 1 Master and 5 s/sheets. The 5 s/sheets need to overwrite the data in the Master (the original source of the s/sheets) - if I update excel sheet #1 thats fine - when I update s/sheet #2 using the update query this will overwrite the data in the Master from s/sheet #1. Likewise, updating s/sheet # 3 will overwrite s/sheet #2.

There is a unique field/identifier and the 5 cuts were made on a filter on one of the data fields (Region)

I appreciate any advice you could give me.

Cheers

Joost

I had the same assumption in the back of my mind when I replied but thought I should make the comment 'just in case.' I think you are safe with the assumption.

I am struggling however with the second issue you raise. If you were to manually do the updates, how would you decide which spreadsheet data should have priority, when there is an overlap? The answer to that might give us a clue as to how to proceed.

Glenn

Hi

I think I've just worked it out - I can use an interim table - Using VB I can make tabel with spreadsheet #1. For s/sheets #2-5 I can run append queries to the interim table. Once that's completed, I can run an update qry to my master.

Does that sound feasible?

Cheers

Jon

Yes, I think you have it.

Is there any chance that the same record in the destination table may have different values in the incoming spreadsheet data? If so, you will still need to deal with the issue of how to decide which of the incoming values should prevail.

Glenn