The information in this article applies to:
- Microsoft Access versions 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes 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
REFERENCES
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.