access: read CSV file into table

Question

is it possible to programmatically have access open a certain csv file and read it into a datasheet and set parameters like what time of delimiter, TEXT QUALIFIER etc, including all the steps that one takes to manually import a csv file into a table?

Answer 1

You can create a Scripting.FileSystemObject, then stream in the file line by line, separating fields by your delimiter, and adding each parsed record to the underlying recordset or another table.

You can also experiment with DoCmd.TransferText, as that seems the most promising built-in method.

Edit: for the more complete (and complex and arguably less efficient) solution, which would give you more control over schema and and treat any csv file like any other datasource, look at ODBC: Microsoft Text Driver - http://support.microsoft.com/kb/187670 for script examples. I believe you can already just link to any cvs files via the standard MS Access front-end, thereby allowing you to do just about any table read/copy operation on it. Otherwise, just set up a ODBC file dns entry to plug in your csv file (via Start->Program Files->Administrative Tools->Data Sources), and then you can select/link to it via your Access file.