3 Replies - 261 Views - Last Post: 29 August 2019 - 06:24 AM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

How to UPDATE JOIN data from another database table with different con

Posted 27 August 2019 - 10:26 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: How to UPDATE JOIN data from another database table with different con

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15308
  • View blog
  • Posts: 61,390
  • Joined: 12-June 08

Re: How to UPDATE JOIN data from another database table with different con

Posted 27 August 2019 - 10:33 PM

As you wrote that - it will not work. Joins would require cross database settings on a user account.

You could, in theory, read the data in from one database to a dataset. Use that dataset to fill in data to a temporary table in the other connection, do the join, and then clear the data.
Was This Post Helpful? 0
  • +
  • -

#3 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 49
  • Joined: 06-March 09

Re: How to UPDATE JOIN data from another database table with different con

Posted 29 August 2019 - 05:59 AM

View Postmodi123_1, on 28 August 2019 - 05:33 AM, said:

As you wrote that - it will not work. Joins would require cross database settings on a user account.

You could, in theory, read the data in from one database to a dataset. Use that dataset to fill in data to a temporary table in the other connection, do the join, and then clear the data.

Thank you. But how?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15308
  • View blog
  • Posts: 61,390
  • Joined: 12-June 08

Re: How to UPDATE JOIN data from another database table with different con

Posted 29 August 2019 - 06:24 AM

How what? Cross joins or inserting into a table?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1