Excel Macro to import data in a sheet to an Access Table

[Slomaro2000]

Hello,
I need help appending data in an excel sheet (180,000 plus rows) to an Access table.

I have the following code that works for small amounts but when I try to get a large range it does not work.


Any help on this?

Option Explicit

Sub ImportToAccess()
    Dim ACC As New Access.Application
    Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
    ACC.OpenCurrentDatabase "\\path to Access\Database.accdb"   'Intan put the path to the ACCESS Database Here  -  Below is "TableName" that will need to match the table in ACCESS (Should be correct).
    ACC.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="AccessTableNameHere", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="ExcelSheetNameHere$A1:O" & LastRow
    ACC.CloseCurrentDatabase
    ACC.Quit
    Set ACC = Nothing
End Sub