How Can Excel VBA Bulk Insert Data into MS Access for Faster Data Export?

Using Excel VBA to Export Data to MS Access Table Swiftly and Efficiently

Originally, your code leveraged loops to export data from an Excel worksheet to an MS Access table. While this approach is viable for relatively small datasets, it becomes less efficient when handling large amounts of data.

To overcome this limitation, you sought an alternative method that avoided iterative processing. Upon exploration, you stumbled upon a solution that utilizes a single SQL INSERT statement to bulk insert data into the Access table.

Public Sub DoTrans()

  Set cn = CreateObject("ADODB.Connection")
  dbPath = Application.ActiveWorkbook.Path & "\FDData.mdb"
  dbWb = Application.ActiveWorkbook.FullName
  dbWs = Application.ActiveSheet.Name
  scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
  dsh = "[" & Application.ActiveSheet.Name & "$]"
  cn.Open scn

  ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
  ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

  cn.Execute ssql

End Sub

This approach significantly improved the export time, evident in your observation that 27,648 records were inserted into the Access table in just 3 seconds.

While you've encountered challenges in specifying specific field names instead of relying on "SELECT *," you continue to explore different methods to achieve the desired outcome.

The above is the detailed content of How Can Excel VBA Bulk Insert Data into MS Access for Faster Data Export?. For more information, please follow other related articles on the PHP Chinese website!