Update query based on another database

T

Teewan

There is something wrong with the following query and while I have managed
Add and Delete statements along similar lines I cannot get this one to work.

UPDATE tblEmployee SET tblEmployee.LName = (SELECT LName FROM
EditupdateEmployee IN 'D:\COFUpdateFile.mdb' WHERE
EditupdateEmployee.EmployeeId="0070037")
WHERE [tblEmployee].[EmployeeId]="0070037";

This returns the expected value (Taylor)
SELECT LName FROM EditupdateEmployee IN 'D:\COFUpdateFile.mdb' WHERE
EditupdateEmployee.EmployeeId="0070037";

And this will perform an update
UPDATE tblEmployee SET tblEmployee.LName = "Smith"
WHERE [tblEmployee].[EmployeeId]="0070037";

I'm sure the answer is staring at me but I just can't see it.

Cheers
Teewan
 
J

John Spencer

The problem is that Access sees the POSSIBILITY that the subquery can return
more than one record and automatically declares that this query will not be
updatable.

I would try using a join. I'm not sure you can do the following. I've
never tried to do an update this way.

UPDATE tblEmployee INNER JOIN EditupdateEmployee IN 'D:\COFUpdateFile.mdb'
ON tblEmployee.EmployeeID = EditupdateEmployee.EmployeeID
SET tblEmployee.LName = EditupdateEmployee.LName
WHERE tblEmployee.EmployeeID = "0070037"

If the above fails I would try linking the EditupdateEmployee table to the
database.
 
Top