This article show you how to use a Schema.ini file and Data
Access Objects (DAO) to programmatically open or link to a text file. A
Schema.ini file contains the specifics on how data is formatted in a particular
text file and is used by the Text ISAM driver to read and manipulate data.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information about
Visual Basic for Applications, please refer to your version of the "Building
Applications with Microsoft Access" manual.
Back to the top
To create a Schema.ini file and a fixed-width text file
that you can use in the Example sections later in the article, follow these
steps:
| 1. | Start a text editor, such as NotePad or WordPad.
|
| 2. | In a new text file, type the following text and save the
file as Contacts.txt:
First NameLast NameHireDate
Nancy Davolio 10-22-91
Robert King 10-23-91
|
| 3. | In another new text file, type the following text and save
the file as Schema.ini:
[Contacts.txt]
ColNameHeader=True
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="First Name" Char Width 10
Col2="Last Name" Char Width 9
Col3="HireDate" Date Width 8
|
NOTE: Make sure both the Contacts.txt and Schema.ini files are
stored in the same folder (directory), for example, C:\My Documents.
Back to the top
Example 1
To create a recordset that uses data from a text file
(Contacts.txt), follow these steps:
| 1. | Create a module and type the following line in the
Declarations section if it is not already there:
Option Explicit
|
| 2. | Type the following procedure:
Function TestSchema()
Dim db As DATABASE, rs As Recordset
Set db = OpenDatabase("c:\my documents", False, _
False,"TEXT;Database=c:\my documents;table=contacts.txt")
Set rs = db.OpenRecordset("contacts.txt")
rs.MoveLast
Debug.Print "Record count= " & rs.RecordCount
rs.Close
End Function
|
| 3. | To test this function, type the following line in the Debug
window, and then press ENTER: ?TestSchema() Note that "Record count= 2" is displayed. |
Back to the top
Example 2
To create a table linked to a text file (Contacts.txt), follow
these steps:
| 1. | Create a module and type the following line in the
Declarations section if it is not already there:
Option Explicit
|
| 2. | Type the following procedure:
Function LinkSchema()
Dim db As DATABASE, tbl As TableDef
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Linked Text")
tbl.Connect = "Text;DATABASE=c:\my documents;TABLE=contacts.txt"
tbl.SourceTableName = "contacts.txt"
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Function
|
| 3. | To test this function, type the following line in the Debug
window, and then press ENTER: ?LinkSchema() Note that linked table is added to the database. |
Back to the top
For more information about accessing data in a text file,
search on "accessing data in text documents," using the Microsoft Access Help
Index.
For more information about how to use the Schema.ini
file, visit the following Microsoft Developer Network (MSDN) Web site:
Back to the top
Help and Support
