• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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 >>>>>>>>>>>>
              rs.MoveNext
        Loop
    End If
   
    Exit Sub
   
ErrProc:
    MsgBox Err & ": " & Error
End Sub


Thanks,
Blake

0
BlakeMcKenna
Asked:
BlakeMcKenna
1 Solution
 
MikeTooleCommented:
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


0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now