Results 1 to 2 of 2

Thread: VBA code to Import and Add Record to Access
Thanks Thanks: 0 Likes Likes: 0

  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
    235
    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
    •  
    excel 2002 convert numbers to mathematical

    10/16/2019

    (30 responses) i copied numbers from an online account. pasted them into excel 2002. how do i convert the numbers to numbers i can multiply etc? i tried pu...

    Pivot Table Filter For Multiple Survey Responses

    10/17/2019

    (10 responses) I have a spreadsheet with data from a survey: 9 questions, each with an answer between 1 and 5. I have a pivot table that shows me a breakdo...

    ComboBox BackColor change on value

    10/17/2019

    (16 responses) I have a ComboBox that I am trying to get the BackColor to change based on a selection. There are 11 ComboBoxes on the sheet. After I have g...

    Save Excel worksheet as PDF

    10/18/2019

    (11 responses) I am trying to save excel worksheet to PDF however encountering runtime error 1004 with the msg with reads as "Document not saved. The ...

    Converting number to fixed length field

    10/18/2019

    (8 responses) I need to convert a number, which will be of indeterminate length, into a text string of fixed length. The length is 14 characters long. To f...

    MrExcel LX Book

    The Holy Grail of Excel Tips $12.60