I am tring to update a record in MS Access Data Base from Excel.

Hi Team,

I am tring to update a perticular record in MS Acess as completed.

How do I do it.  I have some code and I need to add "find first" and update the record.

I only need some sample codes.

Sub PullVen()

    Dim Start1 As Long

    Dim End1 As Long

    Dim Num As Integer

    Dim can As ADODB.Connection, rs As ADODB.Recordset



    Start1 = Timer

     Set cn = New ADODB.Connection

     cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

         "Data Source=Q:\FinanceDATABASE\BRT.mdb;"


     Ven = Sheets("Pull_UVR").Range("O2").Value


     Set rs = New ADODB.Recordset

     'rs.Open "select * from DetailUVR", cn, adOpenStatic, adLockOptimistic


    cnt = 2

    Num = Application.WorksheetFunction.CountA(Range("O2:O31"))

    i = 1

    Do Until i = Num + 2

        i = i + 1

        Ven = Cells(i, 15).Value

        date1 = Format(Cells(i, 16).Value, "mm/dd/yyyy")

        Set cn = New ADODB.Connection

        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

         "Data Source=Q:\Finance NA\Finance Shared\AP & Vendor Recon\AP STAFF INDIA\AP MACRO\DATABASE\BRT.mdb;"


        rs.Open "************SQL Statement**************;", cn, adOpenStatic, adLockOptimistic


         Do Until cnt = 60000


             If rs.EOF = True Then

                 GoTo out

             End If


             Range("A" & cnt).Value = rs.Fields(0).Value

             Range("B" & cnt).Value = rs.Fields(1).Value

             Range("C" & cnt).Value = rs.Fields(2).Value

             Range("D" & cnt).Value = rs.Fields(3).Value

             Range("E" & cnt).Value = rs.Fields(4).Value

             Range("F" & cnt).Value = rs.Fields(5).Value

             Range("G" & cnt).Value = rs.Fields(6).Value

             Range("H" & cnt).Value = rs.Fields(7).Value

             Range("I" & cnt).Value = rs.Fields(8).Value

             Range("J" & cnt).Value = rs.Fields(9).Value

             Range("K" & cnt).Value = rs.Fields(10).Value

             Range("L" & cnt).Value = rs.Fields(11).Value

             Range("M" & cnt).Value = rs.Fields(12).Value

             Range("N" & cnt).Value = rs.Fields(13).Value



         cnt = cnt + 1







        End1 = Timer


        MsgBox ("Download done in " & End1 - Start1 & " Seconds")

End Sub

Open in new window

Who is Participating?
TinTombStoneConnect With a Mentor Commented:
Something like this

rst.Find "CustomerID = 'DUNTE'"

If Not rst.EOF Then
    rst.Fields("CompanyName").Value = "New Value Here"
End If
Sandesh555Author Commented:
I need to update of record (edit) from excel to axcees
Sandesh555Author Commented:
What about find first.  How does that work and which one will be faster rst.Find or rst.findfirst
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Sandesh555Author Commented:
Basically I want the search to happen fast and not take too much time.
Find will find the first occurance. I dont think there is a FindFirst in ADO
Sandesh555Author Commented:
Thanks, I have done it...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.