"Loane Sharp" wrote ...

(2)
However, my objective is to insert the data from the CSV file into a (new)
table in an (existing) Access database, which I attempt to do as follows.

Set cnSource = New ADODB.Connection
strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PathToCSVFile\;" & _
"Extended Properties=""Text;HDR=Yes;FMT=Delimited"""
cnSource.Open strConnect
strSQL = "SELECT * INTO NewTable IN C:\NewAccessDB.mdb FROM
SourceCSVFile.csv"
cnSource.Execute strSQL
Set cnSource = Nothing

P.S. My dataset is actually pipe delimited (|), but I'm presuming that this
will be straightforward ... once I've got the above right! -- ie. I will
create a file "schema.ini" containing the following two lines, and save the
"schema.ini" file in the same folder as the SourceCSVFile.csv:

[SourceCSVFile.csv]
Format=Delimited(|)

The problem with (2) is that the IN syntax (and my preferred 'square
brackets' syntax too) doesn't seem to work where the ADO Connection is
to a text file. Happily, if the target data source is also Jet, you
can connect to the target and use the IN syntax for the text file e.g.

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\NewAccessDB.mdb;

SELECT *
INTO NewTable
FROM [Text;Database=C:\PathToCSVFile\;].[SourceCSVFile#csv]
;

If present, the schema.ini file is still read when using the IN
syntax.

PS as I said before, FMT=Delimited in the connection string has no
effect.

Jamie.

--