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.
Download

Sub PullVen()

    Dim Start1 As Long

    Dim End1 As Long

    Dim Num As Integer

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

    

    Sheets("Pull_UVR").Range("A2:N65536").ClearContents

    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

             

             rs.MoveNext

         cnt = cnt + 1

         Loop

         

out:

        rs.Close

    Loop

    

        End1 = Timer

        Sheets("Pull_UVR").Range("O3").Select

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

End Sub

Open in new window

Sandesh555Asked:
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"
    rst.Update
End If
0
 
Sandesh555Author Commented:
I need to update of record (edit) from excel to axcees
0
 
Sandesh555Author Commented:
What about find first.  How does that work and which one will be faster rst.Find or rst.findfirst
0
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.
0
 
TinTombStoneCommented:
Find will find the first occurance. I dont think there is a FindFirst in ADO
0
 
Sandesh555Author Commented:
Thanks, I have done it...
0
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.