Linking a Comma-Delimited Text File

The procedures for linking a comma-delimited file are similar to those for linking other database tables. If you want Microsoft Jet to use the information in the Schema.ini file, however, you must place the data file and the Schema.ini file in the same directory. The following example illustrates how to link a comma-delimited text file. In this example, strDbPath is the path to the database that will contain the linked table:

Dim dbs As Database
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rst As Recordset

' Open database.
Set dbs = OpenDatabase(strDbPath)

' Create a table definition for text table.
Set tdf = dbs.CreateTableDef("TextProducts")

' Provide source database type, database name with Connect property.
tdf.Connect = "TEXT;DATABASE=C:\JetBook\Samples\Text\CommaDelimited"

' Provide name of text table with the SourceTableName property.
tdf.SourceTableName = "Sample1.txt"

' Append TableDef object to the TableDefs collection to create the link.
dbs.TableDefs.Append tdf

' Run a simple query to ensure the data is accessible.
Set qdf = dbs.CreateQueryDef("")
qdf.SQL = "SELECT Sum(UnitPrice) AS Tally FROM TextProducts;"
Set rst = qdf.OpenRecordset()
MsgBox "Total Sales = $" & rst!Tally
rst.Close
dbs.Close