Solved

Updating a DB Table using SQL and ADO?

Posted on 2006-11-15
1
207 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
1 Comment
 
LVL 27

Accepted Solution

by:
MikeToole earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powerN  challenge 3 57
strDist challenge 35 97
wordmultiple challenge 12 121
Windows and other UI names - The Proper names for the Various elements! 1 61
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
A short article about problems I had with the new location API and permissions in Marshmallow
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now