Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to programmatically create a Schema.ini file in Access 2000

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

View products that this article applies to.

Summary

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

↑ Back to the top

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

↑ Back to the top

References

For more information about the Schema.ini file and the information itcontains, click Microsoft Access Help on the Help menu, type initializing the text and html data sources in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB210001, kbprogramming, kbhowto, kbcode

↑ Back to the top

Article Info
Article ID : 210001
Revision : 5
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 342