ACC: How to Create a Schema.ini File Programmatically

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