?
Solved

Updating a DB Table using SQL and ADO?

Posted on 2006-11-15
1
Medium Priority
?
244 Views
Last Modified: 2010-04-16
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
Comment
Question by:BlakeMcKenna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 27

Accepted Solution

by:
MikeToole earned 2000 total points
ID: 17949517
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is about my first experience with programming Arduino.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Introduction to Processes
Suggested Courses
Course of the Month8 days, 12 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question