Microsoft KB Archive/210001

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

Article ID: 210001

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210001





For a Microsoft Access 97 version of this article, see 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.



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.

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

Option Explicit

  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  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")

 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

</li></ol>

<div class="references_section">