-1

I need to upload a huge amounts of records from excel to msaccess. How I am doing is currently is that I created an "INSERT INTO " sql query in VBA and iterating over my data-sheet with the actual values. Something like this

for i=1 to NumberOfRecords
Sql=Insert Into Table, cell Field, Values(cell(i,1)
next

As I am experiencing performance issues, I wondering if there is a more efficient way to do it with sql. Thank you

jarlh
  • 42,561
  • 8
  • 45
  • 63
user10957064
  • 9
  • 5
  • Can you use the import from excel feature? – Warcupine Jan 30 '20 at 16:16
  • I don't know what you mean, but I would like to avoid anything that is not triggered from excel. – user10957064 Jan 30 '20 at 16:19
  • 1
    Looks like you're taking it from Excel's end. Take it the other way, from Access'. Instead of *pushing* data from Excel, [pull the data in from Access](https://stackoverflow.com/a/59990266/1188513). – Mathieu Guindon Jan 30 '20 at 16:25
  • can someone explain why my answer got deleted? – Naveen Kumar Jan 30 '20 at 17:11
  • 1
    https://stackoverflow.com/questions/44958471/excel-exporting-to-access-via-vba-is-causing-instability/44959630#44959630 you can add records in a loop and then call UpdateBatch to insert them. Typically pretty performant. – Tim Williams Jan 30 '20 at 18:04

2 Answers2

2

Consider a direct Excel query which is supported by the Jet/ACE SQL Engine. Below assumes the Excel sheet is tabular in format with named headers and begins in A1 cell. Save or run below as an Access query. Of course, adjust all columns, Excel workbook path, and sheet name.

INSERT INTO myTable (Col1, Col2, Col3, ...)
SELECT t.Col1, Col2, Col3, ...
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\Workbook.xlsx].[Sheet1$] t

For Excel data that does not start in left uppermost A1 cell and without headers, consider below query which selects data from sheet range: B100:Z450. Be sure listed table columns match the data type one-for-one in Excel data of nameless headers.

INSERT INTO myTable (Col1, Col2, Col3, ...)
SELECT *
FROM [Excel 12.0 Xml;HDR=No;Database=C:\Path\To\Excel\Workbook.xlsx].[Sheet1$B100:Z450] t

To debug always run the SELECT portion of query before full append query.

Parfait
  • 104,375
  • 17
  • 94
  • 125
0

The way I used to solve this problem was to work with arrays in Excel, write them out to disk in the required format and then employ Access's TransferText method on that file.

NeepNeepNeep
  • 883
  • 7
  • 8
  • Is it something that can be launched from excel as well? – user10957064 Jan 30 '20 at 16:22
  • Yes, by adding a reference to the MS Access library (Tools, References...) you'll be able to instantiate (in the background) an instance of Access, load the DB in question and then use DoCmd.TransferText to import the file. Remember to close down the resources at the end. – NeepNeepNeep Jan 31 '20 at 17:20