If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries and working with system objects
Microsoft Access does not store the list of fields in a table in any system table. As such, it is not possible to extract that information using a simple query. You can use the built-in database documenter to get a complete listing of all the table fields and their data types.
To run the documenter on your database, in Access 2003, you can click on Tools->Analyze->Documenter. The resulting GUI has several tabs, one each for Tables, Queries, Forms, Reports, Macros and Modules, and one for Current Database and another for All Object Types. To get information about table fields, click on the Tables tab, then choose the tables for which you want field information.
Now click on Options and select what information you want about each table, and about each field in the tables. The cleanest, easiest-to-read report is obtained when you choose just Properties for a table, Names, Datatypes and Size for fields and Nothing for indexes. This produces a tabular report that has some information about the table such as name, number of records, whether it is updateable, etc., and then a table that contains each field along with its datatype and size.
However, the format of the data in the documenter may not be to your liking. One notable short-coming of the Access documenter is the inability to produce the field description as part of the documenter output. Field descriptions can be entered by the user when creating the table in design view. It is a highly encouraged practice since the description can provided valuable documentation about the purpose of each field in a table. The inability to extract the field descriptions as part of the table documentation using Access's built-in documenter is therefore quite inconvenient.
Moreover, there is no easy way to change the format in which data is presented in the documenter. If you want the columns in the documentation to be presented in a different order, for instance, you can not do it using the documenter. So, if you want to structure the information in your prefered format (whatever that may be), you have to write your own code to accomplish this. We will see how to do so in the following paragraphs.
Writing code to get basic details about a field is pretty straight-forward. Consider the VBA function below which takes a table name as an argument and produces a list of field names as its output:
Function FieldNames(TableName as String) Dim Rst As Recordset Dim f As Field Set Rst = CurrentDb.OpenRecordset(TableName) For Each f In Rst.Fields Debug.Print (f.name) Next Rst.Close End Function
Note that even though we have written the above code to extract information about a table, the same code, without modificaction, can be used on queries also. For all practical purposes, queries and tables are almost identical in Access, so if you call the above function with a query name instead of a table name, it will produce the list of fields included in the query. This can be useful for complex queries that are produced by multiple joins or queries that use other queries in addition to tables to produce their results.
The code above can also be easily modified to populate the rowsource property for a listbox or combobox in a form to provide users with advanced search capabilities. This way, there is no need to hard-code the rowsource, which needs updating every time a table or query design is changed with the addition or removal of a field.
If, in addition to just the field name, you need more information, the code snippet above can be enhanced with some more features. For instance, the code can be used to produce a listing of fields with both their names and their field types. Before we go there, though, we need to understand how the field type is determined. Each field in access has an attribute called Type. Type contains an integer, and the value of this integer tells us the type of the field in most cases. Access conveniently provides us a set of constants against which the value of type can be compared to figure out the field type of a field.
The table below shows how to translate the Type attribute to an actual field type in English:
Value of Type Field Type ============= ========== dbBoolean Yes/No dbByte Byte dbInteger Integer dbLong Long Integer or AutoNumber dbCurrency Currency dbSingle Single dbDouble Double dbDate Date dbBinary Binary dbText Text dbLongBinary OLE Object dbMemo Memo or Hyperlink
As we can see in the table above, in some cases, the Type attribute alone does not tell us unambiguously the type of field we are dealing with. In those cases, we need to use another property of the field, called Attributes to narrow down the exact type of the field. The Attributes property uses a binary encoding to create one integer that is a combination of several different attributes (each of the attributes is boolean, allowing for this type of encoding). For instance one attribute sets the first bit of the Attributes property while another sets the next bit and so on.
To check whether a particular bit in the Attribute property is set, one has to perform a bit-wise AND operation with the appropriate integer represented by just that attribute being set and all other attributes unset to see if the resulting value is zero or non-zero. Once again, Access conveniently provides us constants that represent these individual bits being set with all other bits in the Attribute property being turned off, so we can choose the constant to perform the bit-wise AND operation with, quite easily.
The relevant Attributes bits that we need to worry about are in the table below:
Constant Set for fields of type ======== ====================== dbAutoIncrField AutoNumber dbHyperlinkField Hyperlink
As mentioned earlier, the built-in documenter in Access does not provide details about field descriptions as part of its report. There is a property of each field called Description that contains the description of the field. Printing it out is a little tricky though because Access generates an error every time you try to access the Description property of a field that does not have a description (yet another reason why it is a good idea to always create fields with good descriptions during table design!). To get around this, we need to catch and ignore this error for every field that generates this error.
The code below uses the information in the two tables above, as well as insight about the Description property of a field to produce a listing of table (or query) fields with their names, sizes, field types and descriptions. You can modify this code to suit your particular needs (add more columns, remove columns, rearrange columns, etc.), which makes this code very valuable compared to the built-in documenter of Access.
Function FieldNames(TableName as String) Dim Rst As Recordset Dim f As Field Set Rst = CurrentDb.OpenRecordset(TableName) Debug.Print "Name Size Type Description" For Each f In Rst.Fields Debug.Print f.Name, f.Size, GetFieldType(f), GetFieldDescription(f) Next Rst.Close End Function function GetFieldType(f as field) as String dim FieldType as String Select Case (f.type) case dbBoolean: FieldType = "Yes/No" case dbByte: FieldType = "Byte" case dbInteger: FieldType = "Integer" case dbCurrency: FieldType = "Currency" case dbSingle: FieldType = "Single" case dbDouble: FieldType = "Double" case dbDate: FieldType = "Date/Time" case dbBinary: FieldType = "Binary" case dbLongBinary: FieldType = "OLE Object" case dbText: FieldType = "Text" case dbLong if (f.Attributes AND dbAutoIncrField) = 0& then FieldType = "Long Integer" else FieldType = "AutoNumber" end if case dbMemo if (f.Attributes AND dbHyperLinkField) = 0& then FieldType = "Memo" else FieldType = "HyperLink" end if default: FieldType = "Other" End Select GetFieldType = FieldType End Function function GetFieldDescription(f as field) as String dim Description as String Description = "" On Error Resume Next Description = f.Properties("Description") GetFieldDescription = Description End Function
First, notice how we use the statement "On Error Resume Next" to make sure the program does not stop every time it encounters a field that does not have a description. The function returns the default value of a zero-length string for such fields. When it does find a description, this default value is replaced by the actual description for such fields.
Notice also the use of Attributes to check and decide whether a field is a Long Integer or AutoNumber, and similarly to decide between Memo and HyperLink fields.
Also note that we use the attribute Size of the field to get its size. This is the size of the field itself, not the contents of the field in any particular row of the table or query. For numeric types, the sizes are always constant (that is, the size of the field is in fact the size of the data in that field in any row of the table). The size is dependent on the type of numeric field it is. For text fields, the size that is printed out by the above code is the maximum size of the text field.
For memo and ole object field types though, the attribute Size is always zero. The actual size of such a field in a given row of the table is stored in another attribute called FieldSize. If you are interested in the actual space occupied by a row of the table rather than being interested in the size of the fields themselves, and your table contains memo or ole object type fields, then you have to find the row you are interested in in the recordset, and then write code to extract the fieldsize property of the field for that particular row.
Finally, note that we use a default case to handle field types that could be present in tables that may not have been created in Access. These include linked tables that may have been attached to using ODBC and other technologies. These tables could be hosted in other database products that have more field types such as Decimal, Big Integer, Time Stamp, etc. In general, it is always a good programming practice to use a default case in a select case construct to catch stuff that does not fall into the explicitly mentioned classifications (even if we are absolutely certain that nothing should ever come along that does not belong to one of the classifications explicitly mentioned in the case statements!).
Hope this post has been helpful in solving any problems you might have had listing table fields and their properties in Access. If you have any problems or concerns with the code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!