MS Access Table (and TableDef) Properties

Question

A table in MS Access opened in Design View exposes several properties, as does the table's Property Sheet. Many of these properties are undocumented or documented only for other objects. The question is, to which object do these properties belong? Further, how does one identify them in code? Pressing F1 for context help in each case reveals no clues.

Examples include (and recognize that the names below follow from their visual context, not an object model):

• Field.Description is a column in Design View (along with Field Name and Data Type) but is undocumented. Also, iterating DAO.Field.Properties reveals no Description field and references to the property fail.

• Table.Description appears in the Property Sheet but also is undocumented.

• Table.Filter and Table.OrderBy and their ~OnLoad counterparts appear on the Property Sheet but are documented only for other objects. I understand that information specified here is intended somehow to flow through to forms for which the table is the RecordSource, but the mechanism is not obvious and still leaves the initial question, flowing through from which object's property.

• Table.LinkChildFields and Table.LinkMasterFields appear in the Property Sheet but are documented only for other objects. Also, their use in this context is not obvious.

• Other table properties on the Property Sheet tell the same tale.

Any thoughts, in general or specific to any of the foregoing, would be most helpful and appreciated.

Answer 1

A few things:

Field.Description is a column in Design View (along with Field Name and Data Type) but is undocumented.

No, it is not un-documented.

You are confusing DAO, and that of ms-access.

DAO "field" does not have a description property. So, it not un-documented at all.

Also in Access, there is help. You an put your cursor in the description, and hit help, and you get this:

so, place cursor here, and hit f1 for help:

And now you get this:

So, you are confusing the database engine object called DAO.FIELD with that of ms-access and it allowing you to have/enjoy/see a description in the table desinger.

I should point out that the DAO object model does not have a table designer!!!

In fact, what Access does is add's a custom property to the field, and then display's that. So, field.Description is not un-document, it in fact does not exist.

As noted in the other post here, you can "interate" all of the properties. However, if you use the database engine outside of ms-access, and EVEN create fields in code (or even by sql commands), you WILL STILL find that no descripton property exists. However, as noted, there is this thing called help, and you can give help a try, as it will explain what the description setting in ms-access does.

However, at the end of the day, field.description is not un-documented, and in fact does not exist.

so, if you read/look at/see documentaiton for the DAO field object, then these properties and options will not be found.

After all, you might be using c++, c# or some other system and that database engine that MS-Access just also happens to use.

MS-Access is not the database here. It is a tool that lets you build software, and forms and reports, and write code.

When you using MS-Access, you are not required to use the JET (now called ACE) database engine to store your data. You are free to use the Oracle database, or SQL server or whatever.

So, features of Access and things like link master fields etc.?

Those are MS-Access features, and not the database engine (ACE) features.

Answer 2

To show properties of some Access database object (table, query, form, report, ...), we can do this on VBA, defining this global function:

Function objShowProperties(ByVal xobj As Object)

  Dim i As Long, varPropValue, prop As Object

  On Error Resume Next
'
' loop over properties:
'
  i = 0
  
  For Each prop In xobj.Properties
    varPropValue = prop.Value
    '
    ' sometimes we have error accessing property value:
    '
    If (Err <> 0) Then
      varPropValue = "[UNAVAILABLE]"
      Err.Clear
    End If
    Debug.Print prop.Name, "=", varPropValue
    i = i + 1
  Next
  
  On Error GoTo 0
  
  Set prop = Nothing
  
  objShowProperties = i

End Function

In my Acccess db I've a table named customers. To show properties of this table, I call the above function like this:

objShowProperties CurrentDb.TableDefs("customers")

In my debug console, I got this:

All listed properties can then be accessed directly on VBA code, eg, RecordCount property:

dim lngRecords as long
lngRecords = CurrentDb.TableDefs("customers").Properties("RecordCount")

Hope this will help you.