How to programmatically create a Schema.ini file in Access 2000 (210001)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

In Microsoft Access 2000, you can link or open delimited and fixed-length text files. 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 (or files) that 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 resides

   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
				

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type or paste 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 DAO.DATABASE
      Dim tblDef As DAO.TableDef, fldDef As DAO.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 Immediate window, using the correct drive and path for the Northwind.mdb database, and then press ENTER:

   ?CreateSchemaFile(True,"C:\Program Files\Microsoft Office\Office\Samples\","EMP.TXT","Employees")

5. With a text editor, such as Notepad or WordPad, open the Schema.ini file that you created.
   
   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