ACC: How to Create a Schema.ini File Programmatically (155512)



The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97

This article was previously published under Q155512
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article shows you how to write a procedure that creates a Schema.ini file based on an existing table in your database.

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

In Microsoft Access 7.0 and Microsoft Access 97, you can link or open delimited and fixed-length text files. Microsoft Access can read a text file directly, or it can use an information file called Schema.ini to determine the characteristics of the text file, such as column names, field lengths, and data types. A Schema.ini file is required when you link or open fixed-length text files; it is optional for delimited text files. The Schema.ini file must reside in the same folder as the text file(s) it describes.

The procedure in the following example accepts four parameters:
     Parameter        Value
   ------------------------------------------------------------------------
   bIncFldNames     True/False, stating if the first row of the text file
                    has column names
   sPath            Full path to the folder where Schema.ini will reside
   sSectionName     Schema.ini section name; must be the same as the name
                    of the text file it describes
   sTblQryName      Name of the table or query for which you want to
                    create a Schema.ini file
				
WARNING: The procedure in this example will overwrite an existing Schema.ini file in the same destination folder without warning.
  1. Open the sample database Northwind.mdb.
  2. Create a module and type the following line in the Declarations section if it is not already there: Option Explicit
  3. Type the following procedure:
          Public Function CreateSchemaFile(bIncFldNames As Boolean, _
                                           sPath As String, _
                                           sSectionName As String, _
                                           sTblQryName As String) As Boolean
             Dim Msg As String ' For error handling.
             On Local Error GoTo CreateSchemaFile_Err
             Dim ws As Workspace, db As DATABASE
             Dim tblDef As TableDef, fldDef As Field
             Dim i As Integer, Handle As Integer
             Dim fldName As String, fldDataInfo As String
             ' -----------------------------------------------
             ' Set DAO objects.
             ' -----------------------------------------------
             Set db = CurrentDB()
             ' -----------------------------------------------
             ' Open schema file for append.
             ' -----------------------------------------------
             Handle = FreeFile
             Open sPath & "schema.ini" For Output Access Write As #Handle
             ' -----------------------------------------------
             ' Write schema header.
             ' -----------------------------------------------
             Print #Handle, "[" & sSectionName & "]"
             Print #Handle, "ColNameHeader = " & _
                             IIf(bIncFldNames, "True", "False")
             Print #Handle, "CharacterSet = ANSI"
             Print #Handle, "Format = TabDelimited"
             ' -----------------------------------------------
             ' Get data concerning schema file.
             ' -----------------------------------------------
             Set tblDef = db.TableDefs(sTblQryName)
             With tblDef
                For i = 0 To .Fields.Count - 1
                   Set fldDef = .Fields(i)
                   With fldDef
                      fldName = .Name
                      Select Case .Type
                         Case dbBoolean
                            fldDataInfo = "Bit"
                         Case dbByte
                            fldDataInfo = "Byte"
                         Case dbInteger
                            fldDataInfo = "Short"
                         Case dbLong
                            fldDataInfo = "Integer"
                         Case dbCurrency
                            fldDataInfo = "Currency"
                         Case dbSingle
                            fldDataInfo = "Single"
                         Case dbDouble
                            fldDataInfo = "Double"
                         Case dbDate
                            fldDataInfo = "Date"
                         Case dbText
                            fldDataInfo = "Char Width " & Format$(.Size)
                         Case dbLongBinary
                            fldDataInfo = "OLE"
                         Case dbMemo
                            fldDataInfo = "LongChar"
                         Case dbGUID
                            fldDataInfo = "Char Width 16"
                      End Select
                      Print #Handle, "Col" & Format$(i + 1) _
                                      & "=" & fldName & Space$(1) _
                                      & fldDataInfo
                   End With
                Next i
             End With
             MsgBox sPath & "SCHEMA.INI has been created."
             CreateSchemaFile = True
          CreateSchemaFile_End:
             Close Handle
             Exit Function
          CreateSchemaFile_Err:
             Msg = "Error #: " & Format$(Err.Number) & vbCrLf
             Msg = Msg & Err.Description
             MsgBox Msg
             Resume CreateSchemaFile_End
          End Function
    						
  4. To test this function, type the following line in the Debug window, and then press ENTER:

    ?CreateSchemaFile(True,"c:\MSOffice\Access\","EMP.TXT","Employees")

  5. Open the Schema.ini file that you created with a text editor, such as Notepad or Wordpad. Note that the file contains the following information:

    [EMP.TXT]
    ColNameHeader = True
    CharacterSet = ANSI
    Format = TabDelimited
    Col1=EmployeeID Integer
    Col2=LastName Char Width 20
    Col3=FirstName Char Width 10
    Col4=Title Char Width 30
    Col5=TitleOfCourtesy Char Width 25
    Col6=BirthDate Date
    Col7=HireDate Date
    Col8=Address Char Width 60
    Col9=City Char Width 15
    Col10=Region Char Width 15
    Col11=PostalCode Char Width 10
    Col12=Country Char Width 15
    Col13=HomePhone Char Width 24
    Col14=Extension Char Width 4
    Col15=Photo OLE
    Col16=Notes LongChar
    Col17=ReportsTo Integer

REFERENCES

For more information about the Schema.ini file and the information it contains, search for initializing drivers, and then Initializing the Text Data Source Driver using the Microsoft Access 97 Help Index.

For more information about the Schema.ini file and its relationship to Microsoft Access and the Microsoft Jet database engine, refer to the "Microsoft Jet Database Engine Programmer's Guide", pages 306 - 312.

Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming KB155512