Checking Access properties using VBA

I posted some VBA code in Dynamic icons and images for your Access application that shows you how to check some properties of an Access database and act accordingly. Here is some code that works a bit better, by factoring out the checks into their own procedures.

As I learned from that post, a property can either exist or not exist, and if it exists, it can have different values (and if it doesn't exist, you get an ugly error if you try to set it). So we'll need three procedures.

1. One to see if a given property exists.
2. If the property exists, set or change it.
3. If the property doesn't exist, add it.

The first procedure loops through the existing database properties and returns True if the property exists.

Function PropertyExists(propertyName As String) As Boolean
' checks if property already exists

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
    If prp.Name = propertyName Then
      PropertyExists = True
      Exit Function
    End If
  Next prp

End Function

If the property already exists, we call SetProperty to set its value. propertyValue is declared As Variant because some properties are boolean, others take a built-in constant, and so on.

Function SetProperty(propertyName As String, propertyValue As Variant)
' sets given system property to given value

  Dim db As DAO.Database

  Set db = CurrentDb
  db.Properties(propertyName) = propertyValue

End Function

If the property doesn't exist, we'll need to add it. The AddProperty function creates a Property object and appends it to the Properties collection for the database.

Function AddProperty(propertyName As String, propertyType As Variant, propertyValue As Variant)
' adds given system property and sets to given value

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

  Set db = CurrentDb
  Set prp = db.CreateProperty(propertyName, propertyType, propertyValue)
  db.Properties.Append prp

End Function

Sample usage:

If PropertyExists("AppIcon") Then
  SetProperty("AppIcon", "C:\Myfile.png")
Else
  AddProperty("AppIcon", dbText, "C:\Myfile.png")
End If

I think it makes for a very intuitive set of procedures for checking database properties.

Related Articles:

• January 20, 2010 -- Listing built-in Access database properties