Listing built-in Access database properties

In Checking Access properties using VBA, I posted code for Access that checks if a given database property exists. I also included code samples for adding and setting built-in properties.

But how do you check the built-in properties names to see what properties you can edit?

The following function will list the properties in your database along with their type and value in the Debug Window.

List Built-In Properties for Current Database

Sub ListBuiltInProperties()

Dim db As DAO.Database
Dim prps As DAO.Properties
Dim prp As DAO.Property

  Set db = CurrentDb
  Set prps = db.Properties

  For Each prp In prps
    Debug.Print "Name: " & prp.Name
    Debug.Print "Type: " & prp.Type
    Debug.Print "Value: " & prp.Value
    Debug.Print "---"
  Next prp

End Sub

You'll see a list similar to this:

Name: Transactions
Type: 1
Value: True
—
Name: Updatable
Type: 1
Value: True
—
Name: CollatingOrder
Type: 3
Value: 1033
—
Name: QueryTimeout
Type: 3
Value: 60
—
Name: Version
Type: 12
Value: 4.0
—
Name: RecordsAffected
Type: 4
Value: 0
—
Name: ReplicaID
Type: 15
Value:
—
Name: DesignMasterID
Type: 15
Value:
—

The problem is that the AddProperty function (from Checking Access properties using VBA) takes a constant for its second argument. We need to convert the property Type number to its constant.

Enter the GetPropEnum function. It takes a given type value and returns the corresponding constant as a string. The DataTypeEnum constants were taken from the Access 2007 Developer Reference (see link inside function).

Return DataTypeEnum Enumeration as String

Function GetPropEnum(typeNum As Long) As String
' from http://msdn.microsoft.com/en-us/library/bb242635.aspx

  Select Case typeNum
    Case 1
      GetPropEnum = "dbBoolean"
    Case 2
      GetPropEnum = "dbByte"
    Case 3
      GetPropEnum = "dbInteger"
    Case 4
      GetPropEnum = "dbLong"
    Case 5
      GetPropEnum = "dbCurrency"
    Case 6
      GetPropEnum = "dbSingle"
    Case 7
      GetPropEnum = "dbDouble"
    Case 8
      GetPropEnum = "dbDate"
    Case 9
      GetPropEnum = "dbBinary"
    Case 10
      GetPropEnum = "dbText"
    Case 11
      GetPropEnum = "dbLongBinary"
    Case 12
      GetPropEnum = "dbMemo"
    Case 15
      GetPropEnum = "dbGUID"
    Case 16
      GetPropEnum = "dbBigInt"
    Case 17
      GetPropEnum = "dbVarBinary"
    Case 18
      GetPropEnum = "dbChar"
    Case 19
      GetPropEnum = "dbNumeric"
    Case 20
      GetPropEnum = "dbDecimal"
    Case 21
      GetPropEnum = "dbFloat"
    Case 22
      GetPropEnum = "dbTime"
    Case 23
      GetPropEnum = "dbTimeStamp"
    Case 101
      GetPropEnum = "dbAttachment"
    Case 102
      GetPropEnum = "dbComplexByte"
    Case 103
      GetPropEnum = "dbComplexInteger"
    Case 104
      GetPropEnum = "dbComplexLong"
    Case 105
      GetPropEnum = "dbComplexSingle"
    Case 106
      GetPropEnum = "dbComplexDouble"
    Case 107
      GetPropEnum = "dbComplexGUID"
    Case 108
      GetPropEnum = "dbComplexDecimal"
    Case 109
      GetPropEnum = "dbComplexText"
  End Select

End Function

We can then alter the ListBuiltInProperties as follows.

Sub ListBuiltInProperties()

Dim db As DAO.Database
Dim prps As DAO.Properties
Dim prp As DAO.Property

  Set db = CurrentDb
  Set prps = db.Properties

  For Each prp In prps
    Debug.Print "Name: " & prp.Name
    Debug.Print "Type: " & GetPropEnum(prp.Type)
    Debug.Print "Value: " & prp.Value
    Debug.Print "---"
  Next prp

End Sub

The result then looks like this:

Name: Transactions
Type: dbBoolean
Value: True
—
Name: Updatable
Type: dbBoolean
Value: True
—
Name: CollatingOrder
Type: dbInteger
Value: 1033
—
Name: QueryTimeout
Type: dbInteger
Value: 60
—
Name: Version
Type: dbMemo
Value: 4.0
—
Name: RecordsAffected
Type: dbLong
Value: 0
—
Name: ReplicaID
Type: dbGUID
Value:
—
Name: DesignMasterID
Type: dbGUID
Value:
—

Now you can use the property name and type with the AddProperty function!

Note that you can also return a DataTypeEnum instead of a String with the GetPropEnum function, but as it was taken from the Access 2007 Developer Reference it won't compile in prior versions.

Function GetEnum(typeNum As Long) As DataTypeEnum
' from http://msdn.microsoft.com/en-us/library/bb242635.aspx

  Select Case typeNum
    Case 1
      GetEnum = dbBoolean
    Case 2
      GetEnum = dbByte
    Case 3
      GetEnum = dbInteger
    Case 4
      GetEnum = dbLong
    Case 5
      GetEnum = dbCurrency
    Case 6
      GetEnum = dbSingle
    Case 7
      GetEnum = dbDouble
    Case 8
      GetEnum = dbDate
    Case 9
      GetEnum = dbBinary
    Case 10
      GetEnum = dbText
    Case 11
      GetEnum = dbLongBinary
    Case 12
      GetEnum = dbMemo
    Case 15
      GetEnum = dbGUID
    Case 16
      GetEnum = dbBigInt
    Case 17
      GetEnum = dbVarBinary
    Case 18
      GetEnum = dbChar
    Case 19
      GetEnum = dbNumeric
    Case 20
      GetEnum = dbDecimal
    Case 21
      GetEnum = dbFloat
    Case 22
      GetEnum = dbTime
    Case 23
      GetEnum = dbTimeStamp
    Case 101
      GetEnum = dbAttachment
    Case 102
      GetEnum = dbComplexByte
    Case 103
      GetEnum = dbComplexInteger
    Case 104
      GetEnum = dbComplexLong
    Case 105
      GetEnum = dbComplexSingle
    Case 106
      GetEnum = dbComplexDouble
    Case 107
      GetEnum = dbComplexGUID
    Case 108
      GetEnum = dbComplexDecimal
    Case 109
      GetEnum = dbComplexText
  End Select

End Function

January 13, 2010 -- Updated Access VBA code to delete empty records
February 4, 2008 -- Import data to Access from Excel
August 16, 2011 -- 4 Tips to Keep your Access Database in Tip Top Shape

1 Comment:

jasmith4 writes:

June 15, 2011 at 9:08 am

Is there any way of seeing and manipulating in VBA the Groups in Access's database window? That's where you see Favorites, and you can manually add a group and create shortcuts in any group by dragging an object there. If it can be done manually, why not in VBA?

By the way, here's some code which will list properties in the current database, every container, and ever document in every container. I made this to try to answer the above, and there's nothing there!

[cc lang="VB"]
Function PrpValue(pprp As DAO.Property) As String
On Error Resume Next
PrpValue = Nz(pprp.Value, "(null)")
If Err Then
PrpValue = "#Error " & Err.Number & " from " & Err.Source & ": " & Err.Description
Err.Clear
End If
End Function

Sub DatabaseProperties()
Dim doc As DAO.Document
Dim prp As DAO.Property
Dim cnt As DAO.Container
Dim F%

With CurrentDb()
F% = FreeFile(): Close: Open .Name & " Information.txt" For Output As #F%

Print #F%, .Name
For Each prp In .Properties
Print #F%, " "; prp.Name; ":"; Tab(40); PrpValue(prp)
Next prp

For Each cnt In .Containers

Print #F%, .Name & " / " & cnt.Name
For Each prp In cnt.Properties
Print #F%, " "; prp.Name; ":"; Tab(40); PrpValue(prp)
Next prp

For Each doc In cnt.Documents
Print #F%, .Name & " / " & cnt.Name & " / " & doc.Name
For Each prp In doc.Properties
Print #F%, " "; prp.Name; ":"; Tab(40); PrpValue(prp)
Next prp
Next doc

Next cnt

End With

Close #F%
End Sub

[/vb]