Using Excel VBA to export data to MS Access table

is it possible to export without looping through all records

For a range in Excel with a large number of rows you may see some performance improvement if you create an Access.Application object in Excel and then use it to import the Excel data into Access. The code below is in a VBA module in the same Excel document that contains the following test data

Option ExplicitSub AccImport()    Dim acc As New Access.Application    acc.OpenCurrentDatabase "C:\Users\Public\Database1.accdb"    acc.DoCmd.TransferSpreadsheet _            TransferType:=acImport, _            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _            TableName:="tblExcelImport", _            Filename:=Application.ActiveWorkbook.FullName, _            HasFieldNames:=True, _            Range:="Folio_Data_original$A1:B10"    acc.CloseCurrentDatabase    acc.Quit    Set acc = NothingEnd Sub

@Ahmed

Below is code that specifies fields from a named range for insertion into MS Access. The nice thing about this code is that you can name your fields in Excel whatever the hell you want (If you use * then the fields have to match exactly between Excel and Access) as you can see I have named an Excel column "Haha" even though the Access column is called "dte".

Sub test()    dbWb = Application.ActiveWorkbook.FullName    dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2"  'Data2 is a named rangesdbpath = "C:\Users\myname\Desktop\Database2.mdb"sCommand = "INSERT INTO [main] ([dte], [test1], [values], [values2]) SELECT [haha],[test1],[values],[values2] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dshDim dbCon As New ADODB.ConnectionDim dbCommand As New ADODB.CommanddbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbpath & "; Jet OLEDB:Database Password=;"dbCommand.ActiveConnection = dbCondbCommand.CommandText = sCommanddbCommand.ExecutedbCon.CloseEnd Sub