[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-05-09
6
Medium Priority
?
313 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Sandesh555
  • 4
  • 2
6 Comments
 

Author Comment

by:Sandesh555
ID: 35718772
I need to update of record (edit) from excel to axcees
0
 
LVL 6

Accepted Solution

by:
TinTombStone earned 2000 total points
ID: 35718796
Something like this

rst.Find "CustomerID = 'DUNTE'"

If Not rst.EOF Then
    rst.Fields("CompanyName").Value = "New Value Here"
    rst.Update
End If
0
 

Author Comment

by:Sandesh555
ID: 35718825
What about find first.  How does that work and which one will be faster rst.Find or rst.findfirst
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Sandesh555
ID: 35718841
Basically I want the search to happen fast and not take too much time.
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35719099
Find will find the first occurance. I dont think there is a FindFirst in ADO
0
 

Author Comment

by:Sandesh555
ID: 35719142
Thanks, I have done it...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

834 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