Access Cookbook - Ken Getz [130]
Problem
You want to get a list of fields in a table or query and their properties. The ListFields method is fine for certain situations, but it returns only a few of the fields' properties. Microsoft has also made it clear that this method will not exist in future releases of Access. How can you create a replacement for ListFields that supplies all the available field information?
Solution
In Access 1.x, the ListFields method was the only supported way to return a list of fields and their properties. Its usefulness is limited because it returns only a few field properties and always returns a snapshot. Using the more flexible Data Access Objects (DAO) hierarchy, however, you can get all the properties of field objects and create a replacement for the outdated ListFields method that returns all of a field's properties (or as many as you'd like), placing the results in a readily accessible table.
Open and run the frmListFields form from 06-06.MDB (see Figure 6-9). Choose Tables, Queries, or Both, and whether you wish to include system objects. Select an object from the Object combo box. After a moment, the form will display a list of fields and their properties in the Fields list box. Scroll left and right to see additional properties and up and down to see additional fields.
Figure 6-9. The frmListFields form
To use this technique in your applications, follow these steps:
Import the basListFields module into your database.
Call the acbListFields subroutine, using the following syntax:
Call acbListFields (strName, blnTable, strOutputTable)
The parameters are summarized in Table 6-5.
Table 6-5. The acbListFields subroutine's parameters
Parameter
Example
Description
strName
"Customers"
The name of the table or query
blnTable
True
True if strName is a table, False if it is a query
strOutputTable
"tmpOutputFields"
The name of the table that will hold the list of field properties
The subroutine creates a table with the name specified by strOutputTable and fills it with one record for every field in the specified table or query. The table is similar in structure to the snapshot returned by the ListFields method, except that it has new fields to hold the values of additional field properties. Table 6-6 lists the structure of the resulting table. Note that the first seven fields are identical to those returned by the Access Version 1 ListFields method. The remaining fields are additional information supplied only by acbListFields.
Table 6-6. The acbListFields output table structure
Field name
Data type
Description
Name
String
The name of the field.
Type
Integer
The data type of the field as represented by an integer. Search Access help under ListFields to decode this value.
Size
Integer
The size of the field.
Attributes
Long Integer
The field's attributes. Search Access help under Attributes to decode this value.
SourceTable
String
The name of the field's underlying table. If the table is an attached table, this field will contain the name of the table as it exists in the source database.
SourceField
String
The name of the field.
CollatingOrder
Integer
The collating order of the table. Search Access help under CollatingOrder to decode this value.
AllowZeroLength
Integer
True if zero-length strings are allowed in the field; False otherwise.
DataUpdateable
Integer
True if the field is updateable; False otherwise.
DefaultValue
Text
The field's default value.
OrdinalPosition
Integer
The field's position in the table, starting at 0.
Required
Integer
True if the field requires an entry; False otherwise.
ValidationRule
String
The field's ValidationRule property.
ValidationText
String
The field's ValidationText property.
Caption
String
The field's Caption property.
ColumnHidden
Integer
True if the field is hidden in datasheet view; False otherwise.
ColumnOrder
Integer
The order in which the field appears in datasheet view.
ColumnWidth
Integer
The width of the field as