Updating a DB Table using SQL and ADO?

I am writing code to read 2 Access Tables while at the same time, updating one of them and I have no idea how to write the combination code. I can construct the SQL to retrieve the data but I'm not sure how to update it at the same time. I wish to update the "B" table by taking a value from the "A" table and inserting it into the "B" table record. Here is my code.

Private Const dsnName = "DSN=PNEC"

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Private Sub cmdConvert_Click()
    Dim sSQL As String
    On Error GoTo ErrProc
    sSQL = "Select A.PDSVendorID, A.PDSVendorName, " & _
                  "B.* " & _
           "From xRefVendor A, " & _
                "PDSProdLocationTest B " & _
           "Where B.PDSVendID = A.PDSVendorID " & _
           "Order By A.PDSVendorID"
    cn.Open dsnName
    rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic
    If rs.RecordCount > 0 Then
        Do While Not rs.EOF
            <<<<<<<<<<<< Here is where I want to compare data and then update >>>>>>>>>>>>
    End If
    Exit Sub
    MsgBox Err & ": " & Error
End Sub


Who is Participating?
MikeTooleConnect With a Mentor Commented:
If the update is a straightforward one you can do it all in one SQL Update statement without retrieving a recordset.

Update xRefVendor A inner join PDSProdLocationTest B on B.PDSVendID = A.PDSVendorID set A.myField = B.myField

Run this via DoCmd.RunSQL if the tables are in the current database or llinked into it, otherwise you can run it viad ADO

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.