How Can I Efficiently Transfer Large Datasets from Excel to Access Using SQL?

Using SQL Insert Statement to Efficiently Transfer Data from Excel to Access

In your Excel VBA code, you were manually looping through each row of data to insert values into an Access table. While this approach works for small datasets, it becomes inefficient for large datasets like 25,000 records.

Instead, you can utilize SQL INSERT statements to insert multiple rows at once, drastically reducing the processing time. The following revised code demonstrates how:

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

By using the SQL INSERT statement shown in line 11, all rows from the Excel range are inserted into the Access table at once. This eliminates the need for the loop and significantly improves performance.

Although you mentioned wanting to specify field names instead of using "SELECT *", the code provided does not demonstrate that. If you require assistance with setting specific field names, please provide the desired field names, and we can help you modify the code accordingly.

The above is the detailed content of How Can I Efficiently Transfer Large Datasets from Excel to Access Using SQL?. For more information, please follow other related articles on the PHP Chinese website!