ACC: How to Use Schema.ini for Accessing Text Data

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to programmatically open or link to a text file using a Schema.ini file and Data Access Objects (DAO). 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.

MORE INFORMATION

Follow these steps to create a Schema.ini file and a fixed-width text file that you can use in the Example sections later in the article:

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
   

Example 1

To create a recordset using 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.

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.

REFERENCES

For more information about accessing data in a text file, search on "accessing data in text documents," using the Microsoft Access Help Index.

Keywords          : kb3rdparty PgmHowTo
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto