CODEWITHSUNDEEP
sqlms-access
Wotterbed
Wotterbed

Reputation: 31

Update query using a field from another Access database

Is it possible to update Table1.Field1 in Database1 with Table2.Field2 values in Database2 with an update query and inner join in Access? Both databases are in Access.

I read about the IN Clause (https://msdn.microsoft.com/en-us/library/bb177907.aspx) but could not correctly implement it in an update query.

This code doesn't work but it gives the idea as to what I am looking to do: 

 UPDATE Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID IN "C:\Folder\Database.accdb"
SET Table1.Field1 = Table2.Field2

It doesn't work because Table 2 is in "C:\Folder\Database.accdb" but Table1 is in the database where I am running the update query.

Thanks!

Upvotes: 1

Views: 1452

Answers (2)

Erik A
Erik A

Reputation: 32632

You can use a subquery to work with the IN clause in updates. That way, it's explicit which table is in which database.

UPDATE Table1 INNER JOIN (SELECT * FROM Table2 IN "C:\Folder\Database.accdb") t2 ON Table1.ID=t2.ID 
SET Table1.Field1 = t2.Field2

Upvotes: 2

Jakub
Jakub

Reputation: 91

Try below code.

Steps: 1. create linkedTable 2. run sql to update your table 3. delete linkedTable from point 1

Sub UpdateTAble()
    Dim sql As String
    Dim tbDef As DAO.TableDef

    'Create new table definition
    Set tbDef = CurrentDb.CreateTableDef("Table2")

    'SourceTableName is a table name from soure db.
    tbDef.SourceTableName = "Table2"
    'Connect is full path to your source db.
    tbDef.Connect = "MS Access;DATABASE="C:\Folder\Database.accdb;"

    CurrentDb.TableDefs.Append tbDef
    CurrentDb.TableDefs.Refresh

    'Table2 is linked table but save in main DB - can be used without IN
    sql = "Update table1 INNER JOIN Table2 ON Table2.ID = Table1.ID set table1.Field1 = table2.Field1"
    CurrentDb.Execute sql

    CurrentDb.TableDefs.Delete "Table2"
End Sub

Upvotes: 0

Related Questions