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
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 - Is an access database
- 2 - Is of the same product as the current database
- 3 - Is a "data" component (ie, not a forms component)
- 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:
|smail:||JT Software, PO Box 169, Belconnen, ACT, 2616, Australia|