ACC: How to Create a Custom Database Version Using DAO

MORE INFORMATION

1. Create a new module and type the following four functions.
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         ' **********************************************************
         ' Function: AddDBVersion()
         '  Purpose: Used to add a new Version property to the
         '           current database.
         '   Return: True(-1) for success, False(0) for failure.
         ' **********************************************************
   
   
         Function AddDBVersion (MyVersionNum As String) As Integer
   
            On Local Error GoTo AddDBVersion_Err
   
            Dim MyWS As WorkSpace
            Dim MyDB As Database
            Dim MyVersion As Property
   
            ' Set all DAO objects.
            Set MyWS = DBEngine.Workspaces(0)
            Set MyDB = MyWS.Databases(0)
   
            ' Assign and append the Version number.
            Set MyVersion = MyDB.CreateProperty("MyVersion", DB_TEXT, _
                            MyVersionNum)
            MyDB.Properties.Append MyVersion
   
            ' Pass back success.
            AddDBVersion = True
   
         AddDBVersion_End:
            Exit Function
   
         AddDBVersion_Err:
            MsgBox Error$
            Resume AddDBVersion_End
   
         End Function
   
         ' **********************************************************
         ' Function: UpdateDBVersion()
         '  Purpose: Used to edit the Version property in the
         '           current database.
         '   Return: True(-1) for success, False(0) for failure.
         ' **********************************************************
   
         Function UpdateDBVersion (MyVersionNum As String) As Integer
   
            On Local Error GoTo UpdateDBVersion_Err
   
            Dim MyWS As WorkSpace
            Dim MyDB As Database
   
            ' Set all DAO objects.
            Set MyWS = DBEngine.Workspaces(0)
            Set MyDB = MyWS.Databases(0)
   
            ' Edit the Version property.
            MyDB.Properties("MyVersion") = MyVersionNum
   
            ' Pass back success.
            UpdateDBVersion = True
   
         UpdateDBVersion_End:
            Exit Function
   
         UpdateDBVersion_Err:
            MsgBox Error$
            Resume UpdateDBVersion_End
   
         End Function
   
         ' ***********************************************************
         ' Function: GetDBVersion()
         '  Purpose: Used to return the Version property to the
         '           calling routine or expression.
         '   Return: Version for success, an empty string for failure.
         ' ***********************************************************
   
         Function GetDBVersion () As String
   
            On Local Error GoTo GetDBVersion_Err
   
            Dim MyWS As WorkSpace
            Dim MyDB As Database
   
            ' Set all DAO objects.
            Set MyWS = DBEngine.Workspaces(0)
            Set MyDB = MyWS.Databases(0)
   
            ' Return the version number.
            GetDBVersion = MyDB.Properties("MyVersion")
   
         GetDBVersion_End:
            Exit Function
   
         GetDBVersion_Err:
            MsgBox Error$
            Resume GetDBVersion_End
   
         End Function
   
         ' **********************************************************
         ' Function: DeleteDBVersion()
         '  Purpose: Used to remove the Version property from the
         '           current database.
         '   Return: True(-1) for success, False(0) for failure.
         ' **********************************************************
   
         Function DeleteDBVersion () As Integer
   
            On Local Error GoTo DeleteDBVersion_Err
   
            Dim MyWS As WorkSpace
            Dim MyDB As Database
            Dim MyVersion As Property
   
            ' Set all DAO objects.
            Set MyWS = DBEngine.Workspaces(0)
            Set MyDB = MyWS.Databases(0)
   
            ' Delete the Version property.
            MyDB.Properties.Delete "MyVersion"
   
            ' Pass back success.
            DeleteDBVersion = True
   
         DeleteDBVersion_End:
            Exit Function
   
         DeleteDBVersion_Err:
            MsgBox Error$
            Resume DeleteDBVersion_End
   
         End Function
   						

2. To use the functions, use the following code in an event procedure or your specific code.
   
   
   • To create a version number:

              If AddDBVersion("1.00") Then
                 MsgBox "Version Number Created"
              Else
                 MsgBox "Unable to Change Version Number "
              End If
     								

   • To edit a version number:

              If UpdateDBVersion("2.00") Then
                 MsgBox "Version Number Updated"
              Else
                 MsgBox "Unable to Change Version Number "
              End If
     								

   • To return a version number:
     
     MsgBox "Version Number is " & GetDBVersion()
   • To return a version number on a form or report:
     
     Create a Text Control and set the control's ControlSource property to =GetDBVersion()
   • To delete a version number:

              If DeleteDBVersion() Then
                 MsgBox "Version Number Deleted"
              Else
                 MsgBox "Unable to Delete Version Number"
              End If