ACC2000: How to Use ADO or DAO to Retrieve a Field's Description

This article describes two methods you can use to retrieve the field description stored with a field in a table definition. One method shows how to do this with ActiveX Data Objects (ADO) and the other shows how to do this with Data Access Objects (DAO).

Each field has a Description property that you can reference with ADO or DAO. The following examples demonstrates how to return a field's description:

ADO Method

The sample code in this article uses ActiveX Data Objects. For this code to run properly, you need to reference the Microsoft ADO Ext. 2.5 for DDL and Security Library.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type or paste the following procedure:

   Function GetFieldDesc_ADO(ByVal MyTableName As String, _
     ByVal MyFieldName As String)
      
      Dim MyDB As New ADOX.Catalog
      Dim MyTable As ADOX.Table
      Dim MyField As ADOX.Column
   
      On Error GoTo Err_GetFieldDescription
   
      MyDB.ActiveConnection = CurrentProject.Connection
      Set MyTable = MyDB.Tables(MyTableName)
      GetFieldDesc_ADO = MyTable.Columns(MyFieldName).Properties("Description")
      
      Set MyDB = Nothing
   
   Bye_GetFieldDescription:
      Exit Function
   
   Err_GetFieldDescription:
      Beep
      MsgBox Err.Description, vbExclamation
      GetFieldDescription = Null
      Resume Bye_GetFieldDescription
   
   End Function
   					

4. Click Immediate Window on the View menu or press CTRL+G to open the Immediate window.
5. To test this function, type the following line in the Immediate window, and then press ENTER:
   ? GetFieldDesc_ADO("Employees", "EmployeeID")
   Note that the description for the EmployeeID field is returned.

DAO Method

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type or paste the following procedure:

   Function GetFieldDesc_DAO (ByVal MyTableName As String, _
     ByVal MyFieldName As String)
      Dim DB As DAO.Database
      Dim TD As DAO.TableDef
      Dim FLD As DAO.Field
   
      Set DB = CurrentDB
   
      On Error GoTo Err_GetFieldDescription
   
      Set TD = DB.TableDefs(MyTableName)
      Set FLD = TD.Fields(MyFieldName)
   
      GetFieldDesc_DAO = FLD.Properties("Description")
   
   Bye_GetFieldDescription:
      Exit Function
   
   Err_GetFieldDescription:
      Beep
      MsgBox Err.Description, vbExclamation
      GetFieldDescription = Null
      Resume Bye_GetFieldDescription
   
   End Function
   					

4. Click Immediate Window on the View menu or press CTRL+G to open the Immediate window.
5. To test this function, type the following line in the Immediate window, and then press ENTER:
   ? GetFieldDesc_DAO("Employees", "EmployeeID")
   Note that the description for the EmployeeID field is returned.