Note   If you omit an entry, Microsoft Access uses the default value in Windows Control Panel.

Example of a Schema Information File

The following example of a Schema.ini file specifies the format for the fixed-width file Filename.txt and the delimited file Delimit.txt:

[Filename.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname Char Width 24
Col2=columnname2 Date Width 9
Col3=columnname7 Float Width 10
Col4=columnname8 Integer Width 10
Col5=columnname9 LongChar Width 10

[Delimit.txt]
ColNameHeader=True
Format=Delimited(!)
MaxScanRows=0
CharacterSet=OEM
Col1=username Text
Col2=dateofbirth DateTime

Linking Text Files

To link a text file to your Microsoft Access database, use the CurrentDb function to return an object variable that represents the current Microsoft Access database. Then create a TableDef object and set the connection string and source table name: set the TableDef object’s Connect property to specify the source database type and the full path to the text file, and set its SourceTableName property to the name of the text file.

Important If you want Microsoft Access to use the information in the schema information file, you must place the text file and the Schema.ini file in the same folder. For more information on schema information files, see “Creating a Schema Information File” earlier in this chapter.

The following example links a text file to a Microsoft Access database. It assumes that you want to link the Sample text file located in the Data subfolder of the My Documents folder.

Sub LinkTextFile()
	Dim dbs As Database
	Dim tdfSales As TableDef
	Dim rstSales As Recordset

	' Open the Microsoft Access database.
	Set dbs = CurrentDb

	' Create a TableDef object.
	Set tdfSales = dbs.CreateTableDef("Central Division Sales")

	' Set the connection string to specify the source database type and the
	' full path to the file that you want to link.
	tdfSales.Connect = "Text;DATABASE=C:\My Documents\Data"

	' Set the SourceTableName property to the name of the file you want to access.
	tdfSales.SourceTableName = "Sample.txt"

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

	' Create a Recordset object from the linked text file.
	Set rstSales = dbs.OpenRecordset("Central Division Sales")
End Sub

Tip   Instead of specifying the connection string and source table name by setting properties of the TableDef object, you can specify them by using the connect and source arguments of the CreateTableDef method. You can also use the Microsoft Access user interface to link external data. For more information on using the CreateTableDef method or the user interface to link external data, see “Linking External Tables” earlier in this chapter.

Opening Text Files

To open a text file directly from your Microsoft Access database, create a Database object and set the arguments of the OpenDatabase method to specify the full path to the text file, whether to open the text file exclusively, whether to open it with read/write or read-only permissions, and the source database type. Specify the file name as the source argument of the OpenRecordset method.

The following example opens an external text file directly from a Microsoft Access database and then creates a Recordset object from the text file.

Sub OpenTextFile()
	Dim dbs As Database
	Dim rstAwards As Recordset

	' Point to a folder that contains the text file you want to open.
	Set dbs = OpenDatabase("C:\My Documents\Data", False, False, "Text;")

	' Create a Recordset object from the Awards text file.
	Set rstAwards = dbs.OpenRecordset("Awards.txt")
End Sub