Faster way to move a recordset (DAO or ADO) into a table

Question

I am new to StackOverflow, and not and expert coder. I have created a VBA sub to load a tab-separated file (160k records, about 20 columns) as a recordset (Recordset.Open method) within less then a second.

I then need to move it into a MS Access 2013 table for further processing with several queries. If I run a DoCmd.Import method into a table it takes about 1000 seconds (x2000 longer)

Is there any way to move all the records from the recordset into a table (existing or not) other then go through the entire recordset and append a record at a time?

Thanks

Admittedly, the DoCmd.Import method does more work than simply loading a csv into a recordset. The Access method needs to then append into a defined table schema. — Parfait

Parfait Parfait · Accepted Answer · 2017-05-20T14:58:48

Consider directly querying the text file from an Access query where you query the folder that contains text file with a period qualifier on the file. This is a unique feature of the Jet/ACE SQL Engine (not compliant in other databases). You may need to create a schema.ini file in same directory as the tab-delimited file to properly define delimited format:

Schema.ini (save in same folder as tab file; without this all data comes in one column)

[myTabFile.txt]
ColNameHeader=True
CharacterSet=65001
Format=TabDelimited

Append Query

INSERT INTO myCurrentTable (Col1, Col2, Col3, Col4, Col5)
SELECT t.Col1, t.Col2, t.Col3, t.Col4, t.Col5
FROM [text;HDR=Yes;FMT=Delimited;Database=C:\Path\To\Text\File].myTabFile.txt t;

Make-Table Query

SELECT t.Col1, t.Col2, t.Col3, t.Col4, t.Col5
INTO myNewTable
FROM [text;HDR=Yes;FMT=Delimited;Database=C:\Path\To\Text\File].myTabFile.txt t;

Faster way to move a recordset (DAO or ADO) into a table

1 Answers