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

MORE INFORMATION

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

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

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.