Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Updating a DB Table using SQL and ADO?

Posted on 2006-11-15
1
Medium Priority
?
249 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Six Sigma Control Plans

618 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