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
    227
    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
    https://msaccesstips.com(Learn MS-Access Tips and Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

  3. Some videos you may like

    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
    •