Results 1 to 7 of 7

Update Access Table from Excel using DAO

Threaded View

vish2025 Update Access Table from... 09-14-2010, 02:12 AM
romperstomper Re: Update Access Table from... 09-14-2010, 02:59 AM
vish2025 Re: Update Access Table from... 09-14-2010, 03:29 AM
vish2025 Re: Update Access Table from... 09-14-2010, 03:45 AM
romperstomper Re: Update Access Table from... 09-14-2010, 07:38 AM
vish2025 Re: Update Access Table from... 09-15-2010, 05:53 AM
romperstomper Re: Update Access Table from... 09-15-2010, 06:02 AM
  1. #1
    Registered User
    Join Date
    08-12-2010
    Location
    India
    MS-Off Ver
    Excel 2002 - 2007
    Posts
    18

    Smile Update Access Table from Excel using DAO

    Hi All,

    If any one can help me out. I am new in VB Macro and trying to write the macro for update access table from Excel. Using below codes but getting error on ".Find".
    Macro Reference seeting - Microsoft DAO 3.6 Object Libraey

    If you r having any other code for the same tack pls guide and suggest....
    Thanks in advance.....
    Code:-
    Sub up()
    Dim db As Database
        Dim rs As Recordset
        Dim r As Long
        Dim myDB As String
        Dim wks As Worksheet
    
    Sheets("Job_Details").Select
        Range("A1").Select
        
        myDB = "C:\New_Project\RR.mdb"
    
        
        Set db = OpenDatabase(myDB)
        
        ' open the database
        Set rs = db.OpenRecordset("Work", dbOpenTable)
        
        ' get all records in a table
        r = 2 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0
        ' repeat until first empty cell in column B
            With rs
    'This would search for your primary key field, which I've assumed to be Ref.
        .Find "Prgm_Name1 = " & Range("A" & r).Value
        If .EOF Then
        'Record not found, you could include the Addnew line here.
        Else
        'Record found...
        End If
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("Status") = Range("J" & r).Value
                ' add more fields if necessary...
                .Update ' stores the new record
            End With
            r = r + 1 ' next row
        Loop
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        MsgBox "Appended " & r - 2 & " Records to your database", vbOKOnly, "Confirmation"
    End Sub
    Last edited by vish2025; 09-14-2010 at 02:34 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1