How Can I Efficiently Export Excel Data to MS Access Without Using Loops?

Using Excel VBA to Export Data to MS Access Table without Looping

When exporting data from an Excel worksheet to an MS Access table, the conventional approach involves using a loop to iteratively insert each row of data into the table. However, this method can be inefficient for large datasets. Here's an alternative method using a single SQL INSERT statement that significantly enhances performance:

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

  ' Construct SQL INSERT statement to bulk insert data
  ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
  ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

  ' Execute SQL INSERT statement to insert data in one go
  cn.Execute ssql

End Sub

In this code:

• The ssql variable constructs a SQL INSERT statement that selects all data from the Excel range dsh and inserts it into the fdFolio table in Access.
• The cn.Execute ssql line executes the SQL INSERT statement, inserting all data in one go, eliminating the need for looping and significantly speeding up the process.

Note

For fields with specific names, you can modify the ssql statement to include specific field names in the INSERT clause:

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

The above is the detailed content of How Can I Efficiently Export Excel Data to MS Access Without Using Loops?. For more information, please follow other related articles on the PHP Chinese website!