CODEWITHSUNDEEP
ms-accessvbasql-update
TBone2087
TBone2087

Reputation: 71

VBA Access - Update statement using In

I can't find out if the below is possible. I want to run an update statement in one database from another database. I know that I can save a query and run that from the master database but I thought the below would be possible

UPDATE tblOne 
 INNER JOIN tblTwo 
    ON tblOne.[TID] = tblTwo.TID 
   SET tblTwo.PC = [tblOne].[PC] 
  from tblOne in 'C:\DB.mdb' ;

Any help would be greatly appreciated

Upvotes: 0

Views: 52

Answers (2)

Lee Mac
Lee Mac

Reputation: 16025

Per the MSDN Documentation, the IN clause is only compatible when used in conjunction with the SELECT statement (i.e. Select queries), INSERT INTO statement (i.e. Append queries), or SELECT INTO statement (i.e. Make Table queries) - it cannot be used as part of an UPDATE statement.

I would suggest that you import tblOne as a linked table in the database in which you intend to execute your UPDATE query - this will also result in improved performance over the use of the IN clause.

To link the table, select Access Database from the Import panel of the External Data ribbon tab, and then select the Link option from the wizard:

enter image description here

After the table has been linked, your UPDATE statement will become simply:

UPDATE 
    tblOne INNER JOIN tblTwo ON tblOne.TID = tblTwo.TID 
SET 
    tblTwo.PC = tblOne.PC

Upvotes: 1

Simon R
Simon R

Reputation: 3772

I think you just need to reverse what you’ve written because the table you’re updating needs to be the main table.

Try:

UPDATE tblTwo  
INNER JOIN tblOne ON tblOne.[TID] = tblTwo.TID  
SET tblTwo.PC = [tblOne].[PC] ;

Please note without a where clause you will be updating all rows.

Upvotes: 0

Related Questions