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

Article translations Article translations
Article ID: 149090 - View products that this article applies to.
This article was previously published under Q149090
Advanced: Requires expert coding, interoperability, and multiuser skills.

Expand all | Collapse all

On This Page

SUMMARY

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.

MORE INFORMATION

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.

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.

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.

For more information about how to use the Schema.ini file, visit the following Microsoft Developer Network (MSDN) Web site:
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp

Properties

Article ID: 149090 - Last Review: January 19, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kb3rdparty kbhowto kbprogramming KB149090
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com