How to UPDATE JOIN data from another database table with different connection string?

[ronelpisan]

I have codes below that can update and inner join data with another table within a database, also it uses only one connection string because they are both in the same database.

Dim connstring1 As String
        connstring1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AMP.accdb"
        Dim conn1 As OleDbConnection = New OleDbConnection(connstring1)
        Dim updatescardrate As String = "UPDATE scard INNER JOIN Employee_Records ON scard.controlnumber = Employee_Records.controlnumber SET scard.rate = Employee_Records.rate"
        Dim cmdupdatescardrate As New OleDbCommand(updatescardrate, conn1)
        conn1.Open()
        cmdupdatescardrate.ExecuteNonQuery()
        conn1.Close()

All I want is the same function that can update data of a table from another table of another database with different database connection string. Below are the two databases connection string:

Connection1

Dim connstring1 As String
connstring1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AMP.accdb"

Connection2

Dim connstring2 As String
connstring2 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=IMS.accdb"

Sample expected result illustration:
UPDATE Table scard.column1(this is from connection1, database: AMP.accdb)
and JOIN data from Table criteria.column3(this is from connection2, database: IMS.accdb)

Any idea, suggestion or revision regarding with my codes is highly appreciated. Thank you

[si_the_geek]

Here are a couple of methods that may work for you.

I haven't tried them on accdb files (only mdb, and many years ago), but there is a decent chance they will work. Assuming you are connected to the AMP database, these will hopefully return data from the IMS database:

SELECT field1 FROM someTable IN 'c:\database\IMS.accdb' 

SELECT field1 FROM [MS Access;DATABASE=c:\database\IMS.accdb].[someTable]