Search The Web

Today's Headlines

Monday, March 8, 2010

Microsoft Access Tips & Tricks: List Table Fields

In the previous post on Access, I explained how it is possible to work with system objects to extract data about the different types of objects (tables, linked tables, queries, forms, reports, macros and modules) in an Access database. In this post, we will examine how to extract details of the fields in a given table or query in Access using VBA.

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.
Access Documenter
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.
Access Documenter options
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!

1 comment:

Anonymous said...

Thank you for sharing, I tried this code and it's great !

With Microsoft Visual Basic 6.3 (VBA of Microsoft Office Access 2003), near the end of the function GetFieldType, you may have to replace the following line :
default : FieldType = "Other"

by this line :
Case Else: FieldType = "Other"

Or you won't get any "Memo" or "Hyperlink" : These values of FieldTypes will be overwritten by "Other".

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web