Help and Support

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

View products that this article applies to.
Article ID:210001
Last Review:October 11, 2006
Revision:3.2
This article was previously published under Q210001
For a Microsoft Access 97 version of this article, see 155512 (http://support.microsoft.com/kb/155512/).
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

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

APPLIES TO
•Microsoft Access 2000 Standard Edition

Back to the top

Keywords: 
kbcode kbhowto kbprogramming KB210001

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.