CODEWITHSUNDEEP
ms-access
Anno
Anno

Reputation: 43

What is the fastest way to import a text file to MS Access table by using VBA?

I am just wondering what is the fastest way to import data from text file to Microsoft Access table via VBA.

So far as I know, there are 3 ways.

  1. Use docmd.transfertext method to upload the whole file. Deal with the errors later if there are any.

  2. use "Line Input" statement to read the text line by line, then use recordset.add method to add the record line by line.

  3. Set new Excel.application object, open the file via excel, do all the reformatting, then saved as temp spreadsheet. use Docmd.transferspreadsheet method to upload to the table

Is there any other better way to upload text to MS Access?

What is the fastest way?

Upvotes: 1

Views: 2278

Answers (2)

Anno
Anno

Reputation: 43

Thank you to Albert D. Kallal.

Just to share some testing result here. I uploaded 508481 records in to a Access table.

It took 14 mins and 30 secs to complete the upload via Line Input method. And it took 3 mins and 12 secs to complete the upload via transfertext method and reformat the output via vba code.

docmd.transfertext method is a lot faster than line input, even though it need to read the data twice for reformat the output.

The only downside I couldn't resolve via docmd.transfertext is that the order of records in Access table cannot be kept as the same as the record order in source file if the source file doesn't contain any obvious sorting ID or sorting logic.

Upvotes: 0

Albert D. Kallal
Albert D. Kallal

Reputation: 49049

The built in command to transfer the text file will be the fastest.

However, if one needs some increased flexibility, then the line input would be next best (and next fastest).

Launching and automation of a whole copy of Excel is quite heavy. However, to be fair, once loaded, then speed would be ok.

The issue is not really the speed of the load, but what kind of code you need for re-formatting of the data.

If you use transtext, then it is very fast. However, if you now have to re-loop and re-process that data, the you are giving over the data a second time. So the additional time here is NOT the import speed, but the additional processing.

The advantage of line input, is you can then re-format, and deal with processing of that one line, and then send it off to a table. This means you ONLY loop and touch each row of data one time.

So transfertext is the fastest, but now if you have to re-loop and touch every row of data again, then you touched the data two times.

So the transfer speed likely not the real goal to “center” on but that of what kind of processing and how much processing is required once you grabbed the data.

Line input would the ONLY approach that would touch each row of data one time as you pull it from the file, process it, and then send it to the table.

All other approaches involve reading in the whole data set, and THEN processing the data – so you touching the data a second time.

Upvotes: 2

Related Questions