Update query based on another database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Back
Top