Update multiple tables

[DADAZHU]

Hi,

I am wondering if anyone could point me into a right direction. I need to update data in an Access database(ADO,VBA).

In the Access database, there are three tables as following,
Unit(ID,UName,TID,AID)
Type(TID,TName)


Area(AID,AName)

When a unit has changed its type or area, then the db needs to be updated. My question is how to do it. Can I get a recordset by joining three table, and do the update in one go?

Any idea or sample codes will be much appreciated.

David

[aytee111]

Where do the changes come from? Will they be entered by a person?

If so, make a form bound to Unit table with TID and AID as combo boxes.

[DADAZHU]

Hi, aytee111,

Thanks for your quick response.

I actually read the changes from an excel spreadsheet, in which, I have a table with four columns: ID(Unit), UName, TName, AName. Users are able to change a unit's type or area, then click a button to change the data in the Access database.

Hope this makes a bit more clearer.

David

[aytee111]

I have to admit I haven't got a great solution. You can run a query to update the Unit table (I don't know why the Type and Area tables need to be updated?).

You will have to have a separate query for each field, here's the one for Type. This is assuming that the name coming from Excel matches the TName in the Type table. If the Type names don't match then you will first have to add them to the Type table before this will work.

UPDATE (ExcelTbl INNER JOIN Unit ON ExcelTbl.UName = Unit.UName) LEFT JOIN Type ON ExcelTbl.TName = Type.TName SET Unit.TID = [type].[tid]
WHERE (((Type.TID) Is Not Null));

[DADAZHU]

Thanks again, aytee111.

I am not sure if I understand correctly, is the above SQL to update the Unit table with a new TID if there is a change(users will only change the type name)? Does it mean I need to go through the spreadsheet row by row and execute the SQL for an update each time? Cheers

[aytee111]

This query will update the whole Unit table at one time updating any matches that it finds (matching the UName from the Excel to the UName from the unit table). When it finds a match then it will update the TID on the Unit table to the one it finds based on the Tname in the excel file. It finds the correct TID by linking to the Type table using the tname.

The reason that this needs to be done in two queries is because there needs to be criteria ("WHERE") otherwise it will change the Unit table in error. So make a copy of this one and change the Type to Area.

[aytee111]

This is an Access question, right? not Excel? This query needs to be put in the Access database.

[DADAZHU]

I am planning to use VBA in excel to achieve this goal.

Do I have to put this query into Access instead of using cn.execute strSQL in excel?

[aytee111]

I can't help you with Excel, sorry. Maybe you need to go to the Excel forum.