Results 1 to 3 of 3
  1. #1
    darkdarkwt is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2015
    Posts
    1

    VBA code import and Add record from excel to MS 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
    i have this code , but i dont know where goes wrong
    thank you for the help


    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 <Table name>
        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 Sub

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    but i dont know where goes wrong
    What does this mean? you get an error? if so what error and on what line? the record is not inserted? not all fields are inserted? in which case which fields? Something else?

  3. #3
    Mohnishkumbhar is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    9
    Hi... I want to delete account from Access Forum.. Please help..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  2. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  3. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  4. Unparsable Record w/ Excel Import
    By cherzigMX in forum Access
    Replies: 7
    Last Post: 10-16-2012, 04:11 PM
  5. VBA Code to Import Excel Files
    By Rxp in forum Programming
    Replies: 2
    Last Post: 07-26-2012, 03:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums