Microsoft KB Archive/155512

= ACC: How to Create a Schema.ini File Programmatically =

Article ID: 155512

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q155512



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



SUMMARY
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 WARNING: The procedure in this example will overwrite an existing Schema.ini file in the same destination folder without warning.  Open the sample database Northwind.mdb. Create a module and type the following line in the Declarations section if it is not already there: Option Explicit  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  To test this function, type the following line in the Debug window, and then press ENTER:

?CreateSchemaFile(True,"c:\MSOffice\Access\","EMP.TXT","Employees")

 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

</ol>

<div class="references_section">