|[ Team LiB ]|
Recipe 6.6 Get a Complete List of Field Properties from a Table or Query
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?
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.
To use this technique in your applications, follow these steps:
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:
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).
6.6.4 See Also
|[ Team LiB ]|