ACC: How to Create a Schema.ini File Programmatically

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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 

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.

Additional query words: Text Import Export INI

Keywords : kbprg IntpOff IsmSetup
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto