Search code examples
vbams-accessdao

MS Access Control Property.Type not making sense

I have written code dealing with TableDef and Field properties in the past. It's not hard, it just involves looping through the Field.Properties collection and doing a certain amount of error checking along the way. Property.Name, Property.Type and Property.Value yield everything we need.

The problem: I have just written some code to do the same kind of property enumeration for Form and Report controls. The property name and values come through fine, but the .Type is not correct. An example:

Public Sub TestPropTypes()
Dim dbs As DAO.Database
        
    Set dbs = CurrentDb()
    
    Dim td As TableDef
    Dim fld As DAO.Field
    
    Set td = dbs.TableDefs("CalendarEvent_")
    Set fld = td.Fields("EventDescription")
    PrintObjectProps fld
    
    Dim f As Form
    Dim c As Control

    DoCmd.OpenForm "fmCalendarDates", acDesign
    Set f = Forms!fmCalendarDates
    Set c = f.Controls("Label7")
    PrintObjectProps c
End Sub

Private Sub PrintObjectProps(c As Object, Optional RecursionDepth As Integer = 0)
Dim ExistingProperty As DAO.Property
Dim PropCount As Integer
Dim GotValue As Boolean
Dim v As Variant

    Debug.Print c.Name
    
    For Each ExistingProperty In c.Properties
        If PropCount > 12 Then Exit Sub
    
        GotValue = True
        On Error Resume Next
        v = ExistingProperty.Value
        If Err.number <> 0 Then GotValue = False
        On Error GoTo 0

        If GotValue Then
            Debug.Print "  " & ExistingProperty.Name & " " _
                & GetFieldDDLTypeName(ExistingProperty.Type) & "(" & ExistingProperty.Type & ") " _
                & xf.Gen.dq(CStr(ExistingProperty.Value))
        End If
        
        PropCount = PropCount + 1
    Next
End Sub

Public Function GetFieldDDLTypeName(FieldType As DAO.DataTypeEnum) As String
Dim rtnStr As String
    ' as per: http://allenbrowne.com/ser-49.html
    
    Select Case FieldType
        Case dbBoolean: rtnStr = "YESNO"
        Case dbByte: rtnStr = "BYTE"
        Case dbInteger: rtnStr = "SHORT"
        Case dbLong: rtnStr = "LONG"
        Case dbCurrency: rtnStr = "CURRENCY"
        Case dbSingle: rtnStr = "SINGLE"
        Case dbDouble: rtnStr = "DOUBLE"
        Case dbDate: rtnStr = "DATETIME"
        Case dbBinary: rtnStr = "BINARY"
        Case dbText: rtnStr = "TEXT"
        Case dbLongBinary: rtnStr = "LONGBINARY"
        Case dbMemo: rtnStr = "MEMO"
        Case DBGuid: rtnStr = "GUID"
    End Select
    
    GetFieldDDLTypeName = rtnStr
End Function

Yields:

TestPropTypes
EventDescription
  Attributes LONG(4) "2"
  CollatingOrder SHORT(3) "1033"
  Type SHORT(3) "10"
  Name MEMO(12) "EventDescription"
  OrdinalPosition SHORT(3) "2"
  Size LONG(4) "100"
  SourceField MEMO(12) "EventDescription"
  SourceTable MEMO(12) "CalendarEvent_"
  DataUpdatable YESNO(1) "False"
  DefaultValue MEMO(12) ""
Label7
  EventProcPrefix DATETIME(8) "Label7"
  Name DATETIME(8) "Label7"
  ControlType BYTE(2) "100"
  Caption DATETIME(8) "Description"
  Visible LONGBINARY(11) "True"
  Width BYTE(2) "1875"
  Height BYTE(2) "285"
  Top BYTE(2) "425"
  Left BYTE(2) "1048"
  BackStyle BYTE(2) "0"
  BackColor SHORT(3) "16777215"
  BorderStyle BYTE(2) "0"
  OldBorderStyle BYTE(2) "0"

So you can see the first group, from a Field in a TableDef, yields the kind of results we'd expect.

The second group, using exactly the same rendering code, is from a control on a form. All the MEMO properties are coming through as DATETIME, all the YESNO as LONGBINARY, etc. It's at least consistent, and I could probably deduce the type translation and write a conversion algorithm. I thought it might be using ADO values at first (because YESNO is 11), but there's no consistency with other ADO values and we are also explicitly using a DAO object, so that just wouldn't make sense.

This is inconsistent, and I'd like to work out and document some kind of sensible solution. Has anyone come across this before ?

Tested on Access 2003 and 2007, so I'm sure it's not a weird bug in my Access ver. It's an Access 2003 format database, so there are no extended types throwing a spanner in the works.

EDIT: Problem is solved thanks to HansUp. Now we're checking out if the table/field versus form/report properties do use different return values. I offer this:

Public Sub TestPropTypes1()
Dim prop As DAO.Property
Dim dbs As DAO.Database
Set dbs = CurrentDb()

    DoCmd.OpenForm "fmCalendarDates", acDesign
    
    Debug.Print "Property", "Value", "varType(.Value)", ".Type"
    Debug.Print
    
    Set prop = dbs.TableDefs("CalendarEvent_").Fields("EventDate").Properties("Name")
    Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type

    Set prop = dbs.TableDefs("CalendarEvent_").Fields("EventDate").Properties("Required")
    Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type

    Set prop = Forms!fmCalendarDates!Label7.Properties("Name")
    Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type

    Set prop = Forms!fmCalendarDates!Label7.Properties("Visible")
    Debug.Print prop.Name, prop.Value, varType(prop.Value), prop.Type
End Sub

yielding

TestPropTypes1
Property      Value          vartype(prop.Value)   prop.Type
Name          EventDate      8                     12 
Required      False          11                    1 
Name          Label7         8                     8 
Visible       True           11                    11

which would strongly suggest that the TableDef Field properties do indeed use the DAO.DataTypeEnum types, whereas the form properties appear to yield a VBA.VbVarType return. For example, the Required property of the field returns a type corresponding to the VbVarType of NULL, whereas it's a DataTypeEnum of YesNo.

Note the subtle difference between prop.Type and varType(prop.Value)

While we could use varType(prop.value), this would normally be considered bad practice because the type may depend on the contents of the value (eg. a Null), whereas the .Type is authoritative metadata. In the case of system properties like this, the values may be well behaved and there may not be a practical difference.

The really surprising thing is that the reference materials make no mention of this issue whatsoever.

Solution

  • Use the VBA.VbVarType enumeration when evaluating property Type.

    Your code treats a property's Type as a member of DAO.DataTypeEnum. And that causes the code to translate Type incorrectly.

    Actually this isn't a problem which appears only with form and report controls. You had the same issue with your table's field properties. For example, the output sample incorrectly identified the field's Name property as memo type:

    Name MEMO(12) "EventDescription"

    But a field's Name property is a variant whose subtype is string.

    ? CurrentDb.TableDefs("tblFoo").Fields("long_text").Properties("Name").Type
 12 
? VBA.VbVarType.vbVariant
 12
' this is the WRONG translation ...
? DAO.DataTypeEnum.dbMemo
 12

    If your goal is to translate the property's Type to human-friendly text, consider the TypeName() function:

    ? TypeName(CurrentDb.TableDefs("tblFoo").Fields("long_text").Properties("Name").Value)
String

    If that suggestion is not acceptable, you can create a custom function to translate Type to the text you want. However, translating Type to a DDL field datatype name is the wrong approach, IMO.