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
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:
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.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
