Update tabke from other table with criteria

[tomash01]

Hi!

I ve got two tables in Access 2007.
I want to update a field of DataTable from another table, DataSumTable when two of their fields equals.
( the fields : Name and Period)

I tried this sql code:

UPDATE DataTable
SET DataTable.SumHours =
( SELECT DataSumTable.SumHours 
 FROM  DataSumTable 
 WHERE DataTable.Date =DataSumTable.Date AND 
 DataTable.A_Name =  DataSumTable.A_Name  )

WHERE EXISTS 
( SELECT DataSumTable.SumHours
FROM DataSumTable , DataTable
WHERE DataTable.Date = DataSumTable.Date AND DataTable.A_Name = DataSumTable.A_Name )

I run the sql from VB 6.0 like this:
(SQLcmd contains the sql code as a string)

DoCmd.SetWarnings False
DoCmd.RunSQL (SQLcmd)
DoCmd.SetWarnings True

I get this error message:

Run-time error '3703':
Operation must use an updateable query

But there is no problem with the permission.
I tried to update with INNER JOIN and I got the same error message.

Have you any idea what the problem can be?

Or if you know another way to update a table like this, it would be a big help for me too.

I tried DLookup in the UPDATE too but I dont know how to reach other tables in the criteria.
I want to make work it like this, but it doesnt work like this:

DLookUp( "SumStunden" , "DataSum" , " [Date] = '" & DataTable.Date & "' " ")
Can DLookup reach other tables?

Its a simple problem, but somehow i cant solve it...
Thank you very much for your help!

Tamas Molnar

 

[tomash01]

Somedy told me the solution:

UPDATE DataTable INNER JOIN DataSumTable
ON DataTable.[Date] = DataSumTable.[Date] AND DataTable.A_Name = DataSumTable.A_Name
SET DataTable.SumHours = DataSumTable.SumHours