Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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.

 strName

 blnTable

 strOutputTable

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 .

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.