How to update rows value of specific column from another table's specific column?

Hi friends. I'm new into this combination of sql query. I want to update all rows value of column "empnum" of table "scard" from column "Employee_Number"
of table "Employee_Records". If two(2) columns, the "FirstName" of table "scard" and "First_Name" of table "Employee_records" have the same value(like for example they have same value: John) then the value of column "Employee_Number" from another table will reflect to "empnum". I'm using MS Access database. Thank you for responding my query.

Illustration for expected result:

Table Employee_Records
--------------------------------------------------
Employee_Number | First_Name
--------------------------------------------------
01234 | John
65478 | Jade
--------------------------------------------------

Table scard
--------------------------------------------------
empnum | FirstName
--------------------------------------------------
| James
01234 | John
| Rob
65478 | Jade
| Mark
--------------------------------------------------

Below is the code that did not work.

Code:

---


        Dim sSQL As String
     
        '------------------------------------------------------------------------------------------
        sSQL = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ERIS.accdb"
        Dim conn As OleDbConnection = New OleDbConnection(sSQL)
        sSQL = "INSERT INTO scard (empnum) values (@empnum) "
        sSQL = sSQL & "SELECT Employee_Number "
        sSQL = sSQL & "FROM Employee_Records "
        sSQL = sSQL & "WHERE (First_Name) values (@First_Name) "
        Dim command As New OleDbCommand(sSQL, conn)
        conn.Open()
        command.ExecuteNonQuery()
        conn.Close()

---

First of all, do you want to add new rows or modify existing rows? If it's the latter then you need to use an UPDATE statement, not an INSERT statement.

Quote:

---

Originally Posted by jmcilhinney

First of all, do you want to add new rows or modify existing rows? If it's the latter then you need to use an UPDATE statement, not an INSERT statement.

---

I don't want to insert or add new record, I want to modify/update existing rows.
I just want to update existing rows of "empnum" field of "scard" table with the value from "Employee_Number" of "Employee_Records" table.
Please help me to revise the codes. Thank you.

If you want to modify existing records then, as I said, you need to execute a SQL UPDATE statement, not an INSERT statement. You should do some research on that.