How to update value from one database to another database in VB.NET?

Question

0.00/5 (No votes)

See more:

I'm using MS Access database. I want to update data value of another database base on their primary key. ControlNo(primary key of Database1) and Controlno(primary key of Database2). If the value of Database1 is being updated, so as well in Database2.

What I have tried:

This is my code:

Dim connstring As String
       connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb;"
       Dim conn As OleDbConnection = New OleDbConnection(connstring)
       Dim updatescardempnum As String = "UPDATE Database1_Table [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database2.accdb;].Database2_Table ON Database1_Table.fn = Database2_Table.FirstName SET Database1_Table.ControlNo = Database2_Table.Controlno"
       Dim cmdupdatescardempnum As New OleDbCommand(updatescardempnum, conn)
       conn.Open()
       cmdupdatescardempnum.ExecuteNonQuery()
       conn.Close()

The code above shows this error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: Syntax error in UPDATE statement.

Posted 17-Nov-19 3:22am

kyrons

Updated 17-Nov-19 5:04am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2019-11-17T04:01:00

Solution 1

You will need two separate connection strings and manage two connections.
Or you can try the method shown here How to Connect two or more Databases in Visual Basic? - Stack Overflow[^]

Posted 17-Nov-19 4:01am

RickZeeland

Updated 17-Nov-19 4:07am

v2

Maciej Los · Answer 2 · 2019-11-17T05:04:00

Well...

If you are currently connected to the first database and you want to update the second database, you need to use IN clause[^]. Example usage of IN clause you'll find here: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]. This tip is for Excel, but you can use IN clause the same way in MS Access.

Note: i would strongly recommend to link a table in the second database[^], then you'll be able to update it as a local table.