Update Query from another database

[jrsl]

hi,
I'm trying to update a table in a database based on the info from the same table in another access database. here is what i have so far with no success.

UPDATE jobs_web

SET
  StatusCNTR = 'C:\Users\Justin\AppData\Local\VirtualStore\Program Files (x86)\PaperWRX\Data\pwEssentials.mdb'.StatusCNTR

FROM  'C:\Users\Justin\AppData\Local\VirtualStore\Program Files (x86)\PaperWRX\Data\pwEssentials.mdb'.Jobs
WHERE  'C:\Users\Justin\AppData\Local\VirtualStore\Program Files (x86)\PaperWRX\Data\pwEssentials.mdb'.CNTR = jobs_web.CNTR

I'm getting: sytax error: (missing operator) error

Any help would be great.

Regards,
Justin

 

[statsman]

Why not just import the other table into your database.
Makes running an update query a whole lot easier.

Remember to:
- Give the imported table a different name than your table when importing it
- Delete the imported table when finished.

Of course, it you have to do an update from the other table on a regular basis, it won't be easier at all.

 

[jrsl]

Thanks for the reply,

I will need to update that table based on the data in the other database, as this will be done automatically with a macro.

I think my code will work but i just don't know how to specify the database url in the update query correctly.

I have the same thing to append data from one database to the other.

Any idea's?

I found this on the web:

USE DB1
UPDATE DB1.dbo.MyTable
SET
  Field1 = d2.Field1,
  Field2 = d2.Field2,
  Field3 = d2.Field3
FROM DB2.dbo.MyTable d2
WHERE d2.MyKey = MyTable.MyKey