VBA code to Import and Add Record to Access

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: VBA code to Import and Add Record to Access

  1. #1
    New Member
    Join Date
    Jun 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to Import and Add Record to Access

     
    Hi

    The ideal is to create a button in excel , when user clicked the button it will import and add record to Ms Access

    Code:
    Sub AddRecordtoAccess()
        Dim oAcc As Object
        Dim rstTable As Object
        Dim LRow As Long
        Set oAcc = CreateObject("Access.Application")
        LRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
        'Open Database in Microsoft Access window
        oAcc.OpenCurrentDatabase "C:\Users\WFOO\Desktop\Yong Leong\Project Deadline Search Engine.accdb", True
        
        oAcc.Visible = False
        
        'Create a Recordset based on 
        Set rstTable = oAcc.CurrentDb.OpenRecordset("MainRecords")
        
        With rstTable
            .AddNew
            !Field("Project Name") = Range("A2:A" & LRow).Value
            !Field("Element") = Sheet1.Cells.Range("B2:B" & LRow).Value
           ![Deliverable/Review] = Sheet1.Cells.Range("C2:C" & LRow).Value
          ![Responsibility] = Sheet1.Cells.Range("D2:D" & LRow).Value
           ![Target Date] = Sheet1.Cells.Range("E2:E" & LRow).Value
          ![Status] = Sheet1.Cells.Range("F2:F" & LRow).Value
          ![Stage 1] = Sheet1.Cells.Range("G2:G" & LRow).Value
          ![Stage 2] = Sheet1.Cells.Range("H2:H" & LRow).Value
           ![Stage 3] = Sheet1.Cells.Range("I2:I" & LRow).Value
          ![Stage 4] = Sheet1.Cells.Range("J2:J" & LRow).Value
           ![Stage 5] = Sheet1.Cells.Range("K2:K" & LRow).Value
          ![Comments] = Sheet1.Cells.Range("L2:L" & LRow).Value
            .Update
        End With
        
        oAcc.Quit
        Set oAcc = Nothing
        
    End Subi have this code but i dont know where goes wrong .....

    thank you for your help ><

  2. #2
    Board Regular apr pillai's Avatar
    Join Date
    Aug 2008
    Location
    Kerala, India
    Posts
    223
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to Import and Add Record to Access

      
    This method requires that you add each row of values to the table. Modified code is given below:

    Code:
    Sub AddRecordtoAccess()
        Dim oAcc As Object
        Dim rstTable As Object
        Dim LRow As Long, SRow as Long
        Set oAcc = CreateObject("Access.Application")
        LRow = Sheet1.Range("B" & Rows.Count).End(xlUp).Row
        'Open Database in Microsoft Access window
        oAcc.OpenCurrentDatabase "C:\Users\WFOO\Desktop\Yong Leong\Project Deadline Search Engine.accdb", True
        
        oAcc.Visible = False
        
        'Create a Recordset based on 
        Set rstTable = oAcc.CurrentDb.OpenRecordset("MainRecords")
        
        With rstTable
          For SRow = 2 to LRow
            .AddNew
            .Fields("Project Name").Value = Range("A" & SRow & ":A" & SRow).Value
            .Fields("Element").value = Sheet1.Cells.Range("B" & SRow & ":B" & SRow).Value
          ![Deliverable/Review] = Sheet1.Cells.Range("C" & SRow & ":C" & SRow).Value
          ![Responsibility] = Sheet1.Cells.Range("D" & SRow & ":D" & SRow).Value
          ![Target Date] = Sheet1.Cells.Range("E" & SRow & ":E" & SRow).Value
          ![Status] = Sheet1.Cells.Range("F" & SRow & ":F" & SRow).Value
          ![Stage 1] = Sheet1.Cells.Range("G" & SRow & ":G" & SRow).Value
          ![Stage 2] = Sheet1.Cells.Range("H" & SRow & ":H" & SRow).Value
          ![Stage 3] = Sheet1.Cells.Range("I" & SRow & ":I" & SRow).Value
          ![Stage 4] = Sheet1.Cells.Range("J" & SRow & ":J" & SRow).Value
          ![Stage 5] = Sheet1.Cells.Range("K" & SRow & ":K" & SRow).Value
          ![Comments] = Sheet1.Cells.Range("L" & SRow & ":L" & SRow).Value
            .Update
          Next
        End With
        rstTable.close
        oAcc.Quit
        Set oAcc = Nothing
        
    End Sub
    www.MsAccessTips.com (Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

  3. User Tag List

    Like this thread? Share it with others

    Like this thread? Share it with others

    Posting Permissions

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