Updating a DB Table using SQL and ADO?
Posted on 2006-11-15
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"
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 >>>>>>>>>>>>
MsgBox Err & ": " & Error