Access Cookbook

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

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