list of extended properties for use with ADO connection strings

  • Thread starter Thread starter Loane Sharp
  • Start date Start date
L

Loane Sharp

Hi there

I'm using ADO to connect between Access databases, Excel workbooks and plain
text files. Do you perhaps know where I can get a full list of all the
extended properties for use with connection strings for text files:

strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\TextFiles\;" & _
"Extended Properties=Text;" & _
"HDR=Yes;"

Best regards
Loane
 
Loane Sharp said:
Do you perhaps know where I can get a full list of all the
extended properties for use with connection strings for text files:
Click to expand...

The properties of the connection string can only get you so far. In
fact, AFAIK, 'Text' and 'HDR' and the only two properties that have
any effect at all. For advance settings you need to use a schema.ini
file. Have a look in your text file's folder: a schema.ini file will
have been created automatically and can be edited. For more details,
see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

Jamie.

--
 
Hi Jamie

Your knowledge and practical advice are awesome. Thanks for the help

Best regards
Loane
 
Hi there

Please would you comment on the two items below. The first piece of code
works OK: all the records make it from the CSV file into the active Excel
worksheet with the correct structure, so I suppose I've got the basic
activity more-or-less right.

(1)

Set cnSource = New ADODB.Connection
Set rsData = New ADODB.Recordset
strConnect = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PathToCSVFile\;" & _
"Extended Properties=""Text;HDR=Yes;FMT=Delimited"""
strSQL = "SELECT * FROM SourceCSVFile.csv"
rsData.Open strSQL, cnSource, adOpenStatic, adLockOptimistic, adCmdText
Activesheet.Range("A1").CopyFromRecordset rsData
Set rsData = Nothing
Set cnSource = Nothing

(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(|)

Please help ...

Best regards
Loane
 
Loane Sharp said:
(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(|)
Click to expand...

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.

--
 
Back
Top