ID: Q155512
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
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.
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
1. Open the sample database Northwind.mdb.
2. Create a module and type the following line in the Declarations section
if it is not already there:
Option Explicit
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
and then press ENTER:
?CreateSchemaFile(True,"c:\MSOffice\Access\","EMP.TXT","Employees")
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
For more information about the Schema.ini file and the information it contains, search for "initializing drivers," and then "Initializing the Text Data Source Driver," using the Microsoft Access 97 Help Index.
For more information about the Schema.ini file and its relationship to Microsoft Access and the Microsoft Jet database engine, refer to the "Microsoft Jet Database Engine Programmer's Guide", pages 306 - 312.
Additional query words: Text Import Export INI
Keywords : kbprg IntpOff IsmSetup
Version : 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowtoLast Reviewed: November 21, 1998