Using Excel VBA to export data to MS Access table

Question

I am currently using following code to export data from worksheet to MS Access database  the code is looping through each row and insert data to MS Access Table       Public Sub TransData    Application ScreenUpdating   False Application EnableAnimations   False Application EnableEvents   False Application DisplayAlerts   False  ActiveWorkbook Worksheets  Folio Data original   Activate  Call MakeConnection  fdMasterTemp    For i   1 To rcount - 1     rs AddNew     rs Fields  fdName     Cells i   1  1  Value     rs Fields  fdDate     Cells i   1  2  Value     rs Update  Next i  Call CloseConnection  Application ScreenUpdating   True Application EnableAnimations   True Application EnableEvents   True Application DisplayAlerts   True  End Sub     Public Function MakeConnection TableName As String  As Boolean           Routine to establish connection with database     Dim DBFullName As String    Dim cs As String     DBFullName   Application ActiveWorkbook Path  amp    FDData mdb      cs    Provider Microsoft Jet OLEDB 4 0  Data Source    amp  DBFullName  amp          Set cn   CreateObject  ADODB Connection       If Not  cn State   adStateOpen  Then       cn Open cs    End If     Set rs   CreateObject  ADODB Recordset       If Not  rs State   adStateOpen  Then        rs Open TableName  cn  adOpenKeyset  adLockOptimistic    End If  End Function     Public Function CloseConnection   As Boolean           Routine to close connection with database  On Error Resume Next    If Not rs Is Nothing Then        rs Close    End If      If Not cn Is Nothing Then        cn Close    End If    CloseConnection   True    Exit Function  End Function   Above code works fine for few hundred lines of records  but apparently it will be more data to export  Like 25000 records  is it possible to export without looping through all records and just one SQL INSERT statement to bulk insert all data to Ms Access Table in one go   Any help will be much appreciated   EDIT  ISSUE RESOLVED  Just for information if anybody seeks for this  I ve done a lots of search and found the following code to be work fine for me  and it is real fast due to SQL INSERT   27648 records in just 3 seconds        Public Sub DoTrans      Set cn   CreateObject  ADODB Connection     dbPath   Application ActiveWorkbook Path  amp    FDData mdb    dbWb   Application ActiveWorkbook FullName   dbWs   Application ActiveSheet Name   scn    Provider Microsoft Jet OLEDB 4 0 Data Source    amp  dbPath   dsh        amp  Application ActiveSheet Name  amp         cn Open scn    ssql    INSERT INTO fdFolio   fdName    fdOne    fdTwo       ssql   ssql  amp   SELECT   FROM  Excel 8 0 HDR YES DATABASE    amp  dbWb  amp        amp  dsh    cn Execute ssql  End Sub   Still working to add specific fields name instead of using  Select     tried various ways to add field names but can t make it work for now

User · Accepted Answer

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 Explicit

Sub 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 = Nothing
End Sub

User · Answer

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        amp  Application ActiveSheet Name  amp        amp   Data2    Data2 is a named range   sdbpath    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    amp  dbWb  amp        amp  dsh  Dim dbCon As New ADODB Connection Dim dbCommand As New ADODB Command  dbCon Open  Provider Microsoft Jet OLEDB 4 0 Data Source    amp  sdbpath  amp     Jet OLEDB Database Password    dbCommand ActiveConnection   dbCon  dbCommand CommandText   sCommand dbCommand Execute  dbCon Close   End Sub

[sql] Using Excel VBA to export data to MS Access table

Examples related to sql

Examples related to excel

Examples related to ms-access

Examples related to vba