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

[ronelpisan]

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.

        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()