Microsoft Access Tips for Serious Users

Custom Database Properties

Often you get the situation where you want to store a single item of data in the database, eg an Author Name, a version, a language selection. The most usual way to do this is to define a global const in a module. This has two problems, it is not updatable, and it is not easily accessible from outside the database. A better solution is to make use of database properties.

DAO objects - tables, querydefs, formdefs and the database itself have a list of properties. You can add user-defined properties to Database, Field, Index, QueryDef and TableDef objects. This is something you do once for the life of the object, so the best way to do it is via a bit of scrap code.

To add (say) a Copyright Notice to the database,

• open a new module
• create a function named (say) tmp:

      Function Tmp()
          Dim DB As Database
          Dim P as Property
          Set DB = DBEngine(0)(0)
          Set P = DB.CreateProperty("Copyright Notice", DB_TEXT, "(C) JT Software 1995")
          DB.Properties.Append P
      End Function

• open the immediate window
• run tmp by entering ?tmp(). this will add the property to the DB
• run it again. This time it should give an error "Can't Append: Object already in collection"

And that's it. Don't bother saving the function - the property is now a permanent part of the database.

Now you need a function to get the copyright notice:

    Function CopyRight()
        Dim DB As Database
        Set DB = DBEngine(0)(0)
        CopyRight = DB.Properties![CopyRight Notice]
    End Function

The interesting thing is that you can fetch the copyright notice from a different database from the current one:

    Function CopyRight(filename as string)
        Dim DB As Database
        Set DB=OpenDatabase(filename)
        CopyRight = DB.Properties![CopyRight Notice]
        DB.Close
    End Function

Perhaps a function to update the notice would be good too:

    Function CopyRightUpd(filename as string)
        Dim DB As Database
        Set DB=OpenDatabase(filename)
        DB.Properties![CopyRight Notice] = "(C) JT Software " & Year(Now)
        DB.Close
    End Function

Tip 2.1 - Version control for split databases

Database properties are the way I prefer to do version control of split databases. To each database I add the following properties:

Product=Database for Section XYZ
Component=GlobalData
Version=3
Compat=2

This means that this database contains global data for the database I wrote for the guys in XYZ. It is version 3, but is compatible backward to version 2 (eg-just contains some longer field lengths on one of the tables).

On opening the front-end database, I grab the name of the data database from the connect property of one of my linked tables, and then CheckCompat(extdb):

Function CheckCompat (ext As String) As Integer
    Dim ws As WorkSpace
    Dim DB As Database
    Dim ver1 As Integer
    Dim compat1 As Integer
    Dim ver2 As Integer
    Dim compat2 As Integer
    Set ws = DBEngine(0)
    Set DB = ws(0)
    ver1 = db.properties!version
    compat1 = db.properties!compat

    On Error Resume Next
    Set DB = ws.OpenDatabase(ext)
    If Err Then
        MsgBox "Can't open """ & ext & """: " & Error, 48
        checkcompat = False
        Exit Function
    End If
    ver2 = db.properties!verversion
    compat2 = db.properties!compat
    If Err Then
        MsgBox "Can't check version on """ & ext & """t: " & Error, 48
        checkcompat = False

        Exit Function
    End If

    If ver1 > ver2 And ver2 < compat1 Then
        MsgBox "Can't link the specified data file. This database requires a version " &
                              Format(CDbl(compat1) / 100, "0.00") & " data file.", 48
        checkcompat = False
        Exit Function
    ElseIf ver2 > ver1 And ver1 < compat2 Then
        MsgBox "Can't link the specified data file. It requires a version " &
                              Format(CDbl(compat2) / 100, "0.00") & " forms database.", 48
        checkcompat = False
        Exit Function
    End If

    checkcompat = True
End Function

If the checkcompat is OK, I then do a refreshlink on all the attached tables.

The other properties are used when the user wants to link to a different data file. I check that the file they want to link to:

1. 1 - Is an access database
2. 2 - Is of the same product as the current database
3. 3 - Is a "data" component (ie, not a forms component)
4. 4 - has an appropriate version.

Tip 2.2 - Serial Numbers without using counters (aka: Can I reset a counter to zero?)

There have been quite a few people on the comp.databases.ms-access newsgroup asking if you can reset a counter to zero. Briefly, not really. If you need as serial number or usage count that persists after the database is closed, a good way is to use a property named "SerialNo". As before, create a temporary function to create the property:

    Function Tmp()
        Dim DB As Database
        Set DB = DBEngine(0)(0)
        DB.properties.Append DB.CreateProperty("SerialNo", DB_LONG, 0)
    End Function

And run it once from the immediate window. You then need one or two functions to access it

    Function CurrSerial() as Long
        Dim DB as DataBase
        Set DB = DBengine(0)(0)
        CurrSerial = DB.properties!SerialNo
    End Function

    Function NextSerial() as Long
        Dim DB as DataBase
        Set DB = DBengine(0)(0)
        DB.properties!SerialNo = DB.properties!SerialNo + 1
        NextSerial = DB.properties!SerialNo
    End Function

    Sub ResetSerial()
        Dim DB as DataBase
        Set DB = DBengine(0)(0)
        DB.properties!SerialNo = 0
    End Sub

[Note: This tip is useful for simple databases. Designers of multi-user databases should consider the possibility of duplicates when users append records at the same time. For a more complex solution involving locking of the custom counter, see Getz, Litwin, Reddick Microsoft Access 2 Developer's Handbook (Sybex, 1994) pp.772-779. -- Allen Browne.]

This tip was supplied, by Paul Murray, 14 June 1995.

Who is Paul Murray? (blatant plug). I have been working in MS Access for nearly 3 years, and have been a participant on the comp.databases.ms-access newsgroup. I also work in word/excel, and have 10 years experience in C, as well as C++/windows. I am looking for a full-time position, preferably in Canberra, Australia, but I would be willing to relocate (Brisbane would be nice). To contact me: