Recipe 6.6 Get a Complete List of Field Properties from a Table or Query

6.6.1 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?

6.6.2 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:

1. Import the basListFields module into your database.

2. 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

3. 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 it appears in datasheet view.
DecimalPlaces	Integer	The field's number of decimal places.
Description	Text	The field's description.
Format	Text	The field's format string.
InputMask	Text	The field's input mask string.

6.6.3 Discussion

The acbListFields subroutine uses a table-driven approach to populate the list fields output table with the properties of the fields in the input table or query. Here's the basic algorithm for acbListFields:

1. Call acbMakeListTable to create the output table. This routine either creates a new table or, if one already exists, deletes all of its rows. If it needs to create the output table, it uses a create table query. The names of the fields in the output table are the same as the properties that acbListFields will place there.

2. Open a recordset based on the table created in Step 1.

3. Count the fields in the input table/query.

4. For each field in the input table/query, add a new row in the output table and iterate through the fields in the output table, retrieving the properties for the input table/query field with the same name as the output table fields and adding them in turn to the new row in the output table.

The acbListFields subroutine is shown here:

Public Sub acbListFields( _  strName As String, blnTable As Boolean, _  strOutputTable As String)    ' Purpose:    '     Saves a list of the most common field properties    '     of a table or query to a table.        Dim db As DAO.Database    Dim rst As DAO.Recordset    Dim tdf As DAO.TableDef    Dim qdf As DAO.QueryDef    Dim fld As DAO.Field    Dim intFieldCount As Integer    Dim intI As Integer    Dim intJ As Integer    Dim strOutputField As String        On Error GoTo HandleErr        Call acbMakeListTable(strOutputTable)    Set db = CurrentDb( )    Set rst = db.OpenRecordset(strOutputTable)    ' If the input object is a table, use a TableDef.    ' Otherwise, use a QueryDef.    If blnTable Then       Set tdf = db.TableDefs(strName)       intFieldCount = tdf.Fields.Count    Else       Set qdf = db.QueryDefs(strName)       intFieldCount = qdf.Fields.Count    End If     ' Iterate through the fields in the TableDef     ' or QueryDef.     For intI = 0 To intFieldCount - 1        ' Create a new record for each field.        rst.AddNew        If blnTable Then           Set fld = tdf.Fields(intI)        Else           Set fld = qdf.Fields(intI)        End If        ' Iterate through the fields in rst. The names of these fields        ' are exactly the same as the names of the properties we wish        ' to store in them, so we take advantage of this fact.        For intJ = 0 To rst.Fields.Count - 1           strOutputField = rst.Fields(intJ).Name           rst.Fields(strOutputField) = _            fld.Properties(strOutputField)        Next intJ        rst.Update     Next intI      ExitHere:    Set rst = Nothing    Set qdf = Nothing    Exit Sub HandleErr:    Select Case Err.Number       Case 3270        ' Property not found.          ' Skip the property if it can't be found.          Resume Next       Case Else          MsgBox Err.Number & ": " & Err.Description, , "acbListFields"    End Select    Resume ExitHere End Sub

Once acbListFields has completed its work, you can open the output table and use it any way you'd like. The sample frmListFields form displays the output table using a list box control.

This technique is easy to implement and offers more functionality than the built-in ListFields method. Many more (although not all of the possible) field properties are retrieved, and because acbListFields returns a table instead of a snapshot, you have added flexibility.

acbListFields doesn't decide which properties to write to the output table. Instead, it drives the process using the names of the fields in the output table. If you wish to collect a different set of properties, all you need to do is modify the code in acbMakeListFields and delete the output table (which will be recreated the next time you run acbListFields).

There is useful sample code behind the frmListFields form. Look at the GetTables function for an example of how to get a list of tables and queries and at the FillTables function for an example of a list-filling function (see the Solutions in Recipe 6.8 and Recipe 7.8 for more details on list-filling functions).

In your own applications, you may want to hide the output table in the database container. You can do this either by prefixing its name with "USys" or by checking the Hidden setting in the table's properties.

6.6.4 See Also

For more information on working with properties, see Recipe 7.9 in Chapter 7.