Microsoft KB Archive/233318

{|
 * width="100%"|

-

The information in this article applies to:


 * ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1
 * Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

-

SUMMARY
This article discusses how to open a delimited text file using ADO and the ODBC Text driver. It is possible to use RDO and DAO to open such text files. However, the procedure is a little different. Refer to the MORE INFORMATION section below for major changes that make it easier to open such files in ADO. This article assumes that the reader has a previous knowledge of ADO.

MORE INFORMATION
ADO is different than RDO or DAO when dealing with Text files in the following ways:


 * ADO does not use Schema.ini file and hence it is not required as it was with RDO and DAO. Schema.ini file usually contains information about the text file such as File name, File format, field names, etc.
 * If using a DSN-less connection, the syntax of the connection string is different:


 * ADO : "Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\...\; Extensions=asc,csv,tab,txt;"
 * RDO : "DRIVER={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\...\;"
 * The first line in the delimited text file will be used by ADO for column names. If you don't want to have column names in the file, then you must leave the first row blank; otherwise the first row will be used by ADO as a source for column names and your RecordCount will be short by one.
 * You don't need to specify the file format in advance, ADO can handle different delimited files such as "asc, "csv," "tab" or "txt" based on the file extension as specified in the SQL statement.

The following sample codes demonstrate how to open a recordset based on a csv "Comma Separated" file format:

Sample 1:  ' DSN-Less connection cn1.Open "Driver={Microsoft Text Driver (*.txt;" _         &  " *.csv)};dbq=C:\...\;Extensions=asc,csv,tab,txt;"

rs1.Open "select * from TextFile.csv",cn1,adOpenStatic,adLockReadOnly Sample 2:  ' DSN connection cn1.Open "DSN=Text;"

rs1.Open "TextFile.csv",cn1,adOpenStatic,adLockReadOnly,adCmdUnknown Please note that it is possible to use the ADO Data Control with DSN-less connections by copying the connection string as it appears in Sample 1, and then pasting it in the ConnectionString property of the ADO Data Control.