John W. Colby
jwcolby at colbyconsulting.com
Sun Mar 21 22:36:59 CST 2004
The following is a discussion of Sysvars. The demo file can be downloaded
from my site. I have begun deleting older MDBs out of the zip file to slim
it back down since the original ZIPs are out there for the demos for the
earlier documents. There is a form in the V5 mdb for demoing the SysVar
system in that MDB.
Framework Discussion – System Variables
One of the structures that many developers need is one that I call System
Variables or SysVars for short. These are things like the client’s company
name, address, phone, fax, tax ID and so forth. Many developers handle this
kind of data by creating a one record table, and adding a new field to the
table every time they find a new System Variable that they need to use.
I also require a system like this but decided that the process of adding
fields to a record was just too clumsy and time consuming for my purposes,
so I decided to use a table where each record contains a System Variable. I
prefix all of my SysVar tables (and there may be several) with usys so that
they can be treated as Access system objects and the table won’t be visible
if that flag is cleared (Tools / Options / View / System Objects check box).
Thus my tables would be named usystblSysVar, usystblFWSysVar,
usystblAppSysVar etc.
For my purposes I decided to use an autonumber PK, a string SysVar Name, a
string SysVar value and a memo SysVar memo field for explanations of
purpose, thus:
SV_ID Autonumber
SV_VarName Text - 50
SV_VarValue Text – 50
SV_Memo Memo
Once you start using System Variables it becomes apparent that they are
useful at several different levels in an application. The first level and
immediately important to this discussion, is to control the Framework
default behaviors. In other words, we can set up SysVars that the framework
itself reads as it initializes to decide whether to load service classes at
init(), whether to turn on or off combo dbl-click or NotInList processing by
default, whether to use Just In Time (JIT) subforms by default etc.
The next level is that SysVars are valuable for the same purpose in the
Application itself for setting up the application program behaviors in a
manner similar to that for the framework. Will the entire client company
name / address / etc be displayed on the main menu or just the company name,
or nothing? Will a splash screen display the developer’s information or
just a pretty picture, or the client’s logo. These kinds of operational
decisions can be programmed by creating SysVars that are then referenced by
the Application’s code to decide what to do in cases where several possible
behaviors could be used.
Another level is the actual Application data such as the examples in the
first paragraphs of the client’s company name, address, Tax ID and so forth.
Each of these uses target a different “level” of the application but each
are or may be equally valuable, thus the developer may need a SysVar table
for the Framework control, the Application control and the Application data,
an possibly even other such tables..
Having determined the table structure I wanted, I designed a set of classes
to read the data out of the table(s) and hold the data itself in a
collection keyed on the SysVar name. This allows the developer to call a
method of a clsSysVars instance (the framework instance perhaps), pass in
the name of the SysVar and get back the value of the SysVar. Classes and
collections are extremely high speed, especially compared to trying to get
that data out of the table each time, so we can realistically expect to use
this to control our applications in real time.
One thing to understand is that because the data is loaded out of the table
into a collection in a class, changes to the SysVars will not be available
to the SysVar users until the table(s) is reread. SysVars are not
appropriate for “flags” where changes need to be used immediately in
controlling some operation, particularly if that usage will be in another
workstation.
The other thing to mention is that at least for the Framework, we need to be
able to “override” SysVars loaded earlier with values loaded later. For
example we have a usystblFWSysVars in the framework library, and we also
have a usystblFWSysVars in the FE. The “default” behaviors of the framework
are determined by the usystblFWSysVars in the framework. Whether or not the
table even exists in the FE, we can set up the framework to act a certain
way. Then, in the FE, we can decide that we would rather the default
behavior be different.
As an example, the framework by default does not use JIT subforms. The
usystblFWSysVar has a Sysvar gUseJIT set to False. Thus if there is no
override in the FE, as forms with tabs with subforms load, all of the
subforms load automatically. However, the developer decides that for
ApplicationX it would be useful to have JIT subforms turned on. By placing
an identically named sysvar in the usystblFWSysVar in the FE, but with the
JIT SysVar gUseJIT set True, the subforms now do not load until the tab that
the subform is on is clicked. JIT is turned on in this FE “by default”.
In order to accomplish this, I need to have a method of initializing the
clsSysVar with a specific SysVar table loaded (when the framework
initializes), but then “merge in” another table later (when the FE
initializes). Further it is occasionally useful, particularly during
development, to modify SysVars and have the table “tree” reread so we also
need to save the names of and the load order of all of the SysVar tables
loaded by a class. Then if we need to, at any time we can reread all of the
tables in the correct order to get the final SysVar values loaded into the
class.
Implementation
In order to break this process down into functional units I designed three
classes to implement SysVars. The first class is clsSysVars which is the
service class itself. clsSysVars is instantiated once for each “set” of
SysVars, i.e. once for the framework SysVars, once for the Application
control SysVars and once for the Application data SysVars (as needed).
clsSysVars then immediately instantiates a single clsSysVarsTbl which is the
class that knows how to read out the data from the SysVar table, and holds
the connection and table name. If the SysVars are to be overridden as will
probably happen using a usystblFWSysVars in the FE to override certain
framework SysVars, then a second instance of clsSysVarsTbl will be
instantiated with the connection and table name from the project. That
second instance will be loaded as the application itself initializes.
clsSysVarsTbl reads all of the SysVar records out of the SysVar table it is
responsible for and builds a clsSysVar (singular) instance for each record
in that SysVar table.
To reiterate, clsSysVars (plural) loads an instance of clsSysVarsTbl for
each SysVar table to be merged into its SysVars collection. clsSysVarsTbl
stores the connection and table name used to read the data from the table
and has the methods to read that table, instantiating clsSysVar (singular)
for each record in the table. clsSysVars passes its colSysVars to
clsSysVarsTbl so that that class can build instances of clsSysVar and store
them in that collection. A method of clsSysVars is then called to read out
any given SysVar.
By the way, I timed the reading of SysVars using a shell function that calls
a method of clsSysVars to read a single SysVar 100,000 times. Reading a
SysVar 100,000 times took ~600 milliseconds, thus averaging .006
milliseconds to read a single SysVar. While this is not something that
should be performed in a query reading a million records, it should be
acceptable to use in program control.
clsSysVar
Starting at the bottom of the class chain, we will look at clsSysVar.
clsSysVar holds all of the fields (data) of a single SysVar record from the
table. In the header we have a private variable for each field in the
SysVar table.
Option Compare Database
Option Explicit
Private mstrName As String
Private mvarValue As Variant
Private mstrMemo As Variant
Private mblnUserEditable As Boolean
Private mblnAllowOverride As Boolean
Since we have no objects to initialize or terminate we don’t use the
built-in class events. The public function Init() passes in all of the data
from one record of the table. All we do is store the data in the variables
in the class header.
Public Function Init(lstrVarName As Variant, lstrVarValue As Variant, _
Optional lstrMemo As Variant = "", _
Optional lblnUserEditable As Boolean = False, _
Optional lblnAllowOverride As Boolean = True)
On Error GoTo Err_Init
mstrName = lstrVarName
mvarValue = lstrVarValue
mstrMemo = lstrMemo
mblnUserEditable = lblnUserEditable
mblnAllowOverride = lblnAllowOverride
Exit_Init:
Exit Function
Err_Init:
MsgBox Err.Description, , "Error in Function clsSysVar.Init"
Resume Exit_Init
Resume 0 '.FOR TROUBLESHOOTING
End Function
We then have properties to allow us to read / write these variables in the
header. It is unclear that we will ever need to write the values through
the property let statements and it might be wise to delete all the property
let statements to cause the values in the header to be read only.
'
'The name of the SysVar
'
Property Let Name(strVarName As String)
mstrName = strVarName
End Property
Property Get Name() As String
Name = mstrName
End Property
'
'The value of the SysVar
'
Property Get Value() As Variant
Value = mvarValue
End Property
Property Let Value(strValue As Variant)
mvarValue = strValue
End Property
'
'The memo of the SysVar
'
Property Get Memo() As String
Memo = mstrMemo
End Property
Property Let Memo(strMemo As String)
mstrMemo = strMemo
End Property
'
'Is this SysVar user editable?
'
Property Let UserEditable(blnUserEditable As Boolean)
mblnUserEditable = blnUserEditable
End Property
Property Get UserEditable() As Boolean
UserEditable = mblnUserEditable
End Property
'
'Allow Override of this SysVar?
'
Property Get AllowOverride() As Boolean
AllowOverride = mblnAllowOverride
End Property
Property Let AllowOverride(blnAllowOverride As Boolean)
mblnAllowOverride = blnAllowOverride
End Property
That’s all there is to clsSysVar. Its function is to store the SysVar data
in variables.
clsSysVarsTbl
This class is responsible for reading the data out of the usys tables
holding the SysVar data. It has to save the connection and the table name
since it might be called on to refresh the SysVars in its table.
Option Compare Database
Option Explicit
Private mcnn As adodb.Connection 'The connection for the table
Private mrst As adodb.Recordset 'The recordset object used
Private mstrTblName As String 'The table name
Private mcolSysVars As Collection 'The SysVars collection from the parent
This class stores objects that need to be cleaned up so we use the Terminate
event to trigger Term() as a failsafe cleanup method.
Private Sub Class_Terminate()
Term
End Sub
The Init() method passes in a connection, a table name and the SysVar
collection. We store pointers to each of these objects in the header. We
then call the MergSysVars method to read the data out of the tables and
build the SysVar class instances.
Public Function Init(lcnn As adodb.Connection, lstrTblName As String,
lcolSysVars As Collection)
On Error GoTo Err_Init
Set mcnn = lcnn
mstrTblName = lstrTblName
Set mcolSysVars = lcolSysVars
MergeSysVars
Exit_Init:
Exit Function
Err_Init:
MsgBox Err.Description, , "Error in Function clsSysVarsTbl.Init"
Resume Exit_Init
Resume 0 '.FOR TROUBLESHOOTING
End Function
We do have cleanup to perform so do it here.
Public Function Term()
On Error Resume Next
Set mcnn = Nothing
mrst.Close
Set mrst = Nothing
Set mcolSysVars = Nothing
End Function
The MergeSysVars method is where all the work is done. The record set is
opened using the connection and table name passed in to init and stored in
the class header. We then move through the record set building new
clsSysVar class instances. Note that we first delete any class by the same
SysVar name first, which is our definition of “merge”.
'
'Merges all of the SysVars in a table into mcolSysVars
'
'A "merge" really means deleting the old value and replacing it with the new
value.
'For this reason all we do is delete any existing SysVar class in the
collection
'keyed on any names in this table and build a new one from scratch
'
Public Function MergeSysVars()
On Error GoTo Err_MergeSysVars
Set mrst = New adodb.Recordset
'open the database and the recordset
mrst.Open mstrTblName, mcnn
'create the collection to hold the sysvars
With mrst
While Not .EOF
On Error Resume Next
mcolSysVars.Remove !SV_VarName
On Error GoTo Err_MergeSysVars
NewSysVar
.MoveNext
Wend
End With
Exit_MergeSysVars:
On Error Resume Next
mrst.Close
Set mrst = Nothing
Exit Function
Err_MergeSysVars:
MsgBox Err.Description, , "Error in Function
clsSysVarsTbl.MergeSysVars"
Resume Exit_MergeSysVars
Resume 0 '.FOR TROUBLESHOOTING
End Function
The NewSysVar method is private to the class and is a class factory that
builds an instance of clsSysVar, storing the data from the current record in
the class variables. It then stores that class instance in mcolSysVars, the
collection of SysVar class instances that is originally created in the main
class clsSysVars.
'
'Builds a new SysVar instance from a SysVar record
'
Private Function NewSysVar()
On Error GoTo Err_NewSysVar
Dim lclsSysVar As clsSysVar
Set lclsSysVar = New clsSysVar
With mrst
lclsSysVar.Init !SV_VarName, !SV_VarValue, !SV_Memo,
!SV_UserEditable, !SV_AllowOverride
mcolSysVars.Add lclsSysVar, !SV_VarName
End With
Exit_NewSysVar:
On Error Resume Next
Exit Function
Err_NewSysVar:
MsgBox Err.Description, , "Error in Function
clsSysVarsTbl.NewSysVar"
Resume Exit_NewSysVar
Resume 0 '.FOR TROUBLESHOOTING
End Function
That’s all there is to clsSysVarsTbl. Its function is to read the data out
of the table and into clsSysVar instances where the data is stored.
clsSysVars
This is the SysVar supervisor class, and is the Service Class actually used
by the framework or application. clsSysVar has two collections in its
header, one for the clsSysVar class instances (the data) and another for the
clsSysVarsTbl class instances (the tables of data).
Option Compare Database
Option Explicit
Private mcolSysVarsTbl As Collection
Private mcolSysVars As Collection
We have objects to be initialized (collections) so we use the class
Initialize event to to the initialization of these classes.
Private Sub Class_Initialize()
On Error GoTo Err_Class_Initialize
assDebugPrint "initialize " & mcstrModuleName, DebugPrint
Set mcolChildren = New Collection
Set mcolSysVarsTbl = New Collection
Set mcolSysVars = New Collection
Exit_Class_Initialize:
Exit Sub
Err_Class_Initialize:
MsgBox Err.Description, , "Error in Sub
clsTemplate.Class_Initialize"
Resume Exit_Class_Initialize
Resume 0 '.FOR TROUBLESHOOTING
End Sub
The Init() method is passed in a connection and table name. This data is
not actually stored here but rather simply passed on to an instance of
clsSysVarsTbl by calling the MergeSysVars method.
Public Sub Init(ByRef robjParent As Object, lstrCnn As adodb.Connection,
lstrTblName As String)
On Error GoTo Err_Init
Set mobjParent = robjParent
'colTblDat.Add SVTblDat(lstrCnn, lstrTblName)
'IF THE PARENT OBJECT HAS A CHILDREN COLLECTION, PUT MYSELF IN IT
assDebugPrint "init " & mstrInstanceName, DebugPrint
MergeSysVars lstrCnn, lstrTblName
Exit_Init:
Exit Sub
Err_Init:
MsgBox Err.Description, , "Error in Sub clsSysVars.Init"
Resume Exit_Init
Resume 0 '.FOR TROUBLESHOOTING
End Sub
Since we have objects to clean up we use the class’ Terminate event as a
failsafe cleanup, which simply calls our Term().
Private Sub Class_Terminate()
On Error Resume Next
assDebugPrint "Terminate " & mcstrModuleName, DebugPrint
Term
End Sub
Term() does our cleanup for us. We build a private colEmpty () method which
we use to empty out the two collections before we set the pointers to these
collections to nothing.
Public Sub Term()
Static blnRan As Boolean 'The term may run more than once so
If blnRan Then Exit Sub 'just exit if it already ran
blnRan = True
On Error Resume Next
assDebugPrint "Term() " & mcstrModuleName, DebugPrint
colEmpty mcolSysVarsTbl
Set mcolSysVarsTbl = Nothing
colEmpty mcolSysVars
Set mcolSysVars = Nothing
Set mobjParent = Nothing
Set mcolChildren = Nothing
End Sub
We provide a couple of property get procedures to pass back pointers to the
class collections in case we ever need them.
Property Get colSysVars() As Collection
Set colSysVars = mcolSysVars
End Property
Property Get colSysVarsTbl() As Collection
Set colSysVarsTbl = mcolSysVarsTbl
End Property
We already mentioned colEmpty, which does nothing more than iterate the
collection holding class instances, calling the Term() method of each
object, then removing the pointer to the class from the collection. Since
the collections (mcolSysVarsTbl and mcolSysVars) hold the only pointers to
their respective class instances, when we remove the pointer form the
collection, the class instance is removed from memory.
'
'Empties out a collection containing class instances
'
Private Function colEmpty(col As Collection)
On Error GoTo Err_colEmpty
While col.Count > 0
On Error Resume Next
col(1).Term
On Error GoTo Err_colEmpty
col.Remove 1
Wend
Exit_colEmpty:
Exit Function
Err_colEmpty:
MsgBox Err.Description, , "Error in Function clsSysVars.colEmpty"
Resume Exit_colEmpty
Resume 0 '.FOR TROUBLESHOOTING
End Function
The MergeSysVars method is a public method and class factory that allows us
to create an instance of clsSysVarsTbl, initialize that instance with the
passed in connection and table name, and finally save that instance in the
mcolSysVarsTbl collection.
'
'THIS FUNCTION ALLOWS US TO MERGE ANOTHER SYSVAR TABLE INTO THE
'EXISTING (FRAMEWORK?) SYSVAR COLLECTION AT RUN TIME, IN EFFECT OVERRIDING
'ANY BUILT IN VARIABLE VALUES WITH ONES FROM THE APPLICATION. THIS ALLOWS
'THE APPLICATION TO SET UP THE FRAMEWORK TO OPERATE DIFFERENTLY THAN IT
MIGHT
'BY DEFAULT.
'
Function MergeSysVars(lcnn As adodb.Connection, lstrTbl As String)
Dim lclsSysVarsTbl As clsSysVarsTbl
Set lclsSysVarsTbl = New clsSysVarsTbl
lclsSysVarsTbl.Init lcnn, lstrTbl, mcolSysVars
MergeSysVars = True
mcolSysVarsTbl.Add lclsSysVarsTbl, lstrTbl & lcnn.ConnectionString
Exit_MergeSysVars:
On Error Resume Next
Exit Function
Err_MergeSysVars:
Select Case Err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
MsgBox Err.Description, , "Error in Function
clsSysVars.MergeSysVars"
Resume Exit_MergeSysVars
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
RefreshSysVars is a public method that allows the developer or the system to
modify one or more SysVar in the table(s) and then cause a reread of all the
tables used to build this SysVar set. It does nothing more that iterate
mcolSysVarsTbl calling the MergeSysVars method of each clsSysVarsTbl
instance in that collection.
'
'This function refreshes existing sysvars by reading all of the SysVars out
of all
'the tables
'
Public Function RefreshSysVars()
On Error GoTo Err_RefreshSysVars
Dim lclsSysVarsTbl As clsSysVarsTbl
For Each lclsSysVarsTbl In mcolSysVarsTbl
lclsSysVarsTbl.MergeSysVars
Next lclsSysVarsTbl
Exit_RefreshSysVars:
Exit Function
Err_RefreshSysVars:
MsgBox Err.Description, , "Error in Function
clsSysVars.RefreshSysVars"
Resume Exit_RefreshSysVars
Resume 0 '.FOR TROUBLESHOOTING
End Function
And finally, the SV method allows you to read a SysVar field out of a
specific instance of clsSysVar (a specific SysVar). The default value read
is the Value field but you can specify any of the other fields.
'
'This method is what actually returns a SysVar value from one of the fields
'The default value returned comes from the SV_VarValue field but you can
'specify any of the other fields, other than the SV_VarName which you must
'have to begin sith since it is the "key" for the collection, used to index
'into the collection.
'
Function SV(strSVName As String, Optional strSVFld As String =
"SV_VarValue") As Variant
On Error GoTo Err_SV
Select Case strSVFld
Case "SV_VarValue"
SV = mcolSysVars(strSVName).Value()
Case "SV_Memo"
SV = mcolSysVars(strSVName).Memo()
Case "SV_UserEditable"
SV = mcolSysVars(strSVName).UserEditable()
Case "SV_AllowOverride"
SV = mcolSysVars(strSVName).AllowOverride()
Case Else
End Select
Exit_SV:
Exit Function
Err_SV:
MsgBox Err.Description, , "Error in Function clsSysVars.SV"
Resume Exit_SV
Resume 0 '.FOR TROUBLESHOOTING
End Function
Summary
The SysVar system is most logically broken down into three classes, one is
the Service Class itself clsSysVars. This is the class that the developer
interfaces with directly. This class then uses clsSysVarsTbl instances to
read the data out of one or more SysVar tables, loading the data itself into
clsSysVar instances which are saved in a collection directly in the Service
Class. clsSysVars then has public wrapper methods that pass requests to
refresh the SysVars and read SysVar values out of the clsSysVar class
instances.
Of course this could have been implemented differently; in fact my first
SysVar system used a single class which “did it all”. It was ugly however,
and I think a three class “system” implementing SysVars is an easier to
understand way to implement it. Each of the three classes has its own job
to do. ClsSysVars uses clsSysVarsTbl to read out the data into clsSysVar
instances. clsSysVars manages the process, setting up and tearing down all
the child classes, and wrapping requests for the child class methods to pass
along the requests for data or services.
SysVars will be used throughout the Framework to allow us to select from two
or more possible behaviors. The table in the Framework will hold the
default values to program default behaviors, but the table in the FE itself
will be able to override the default values to change behaviors as needed in
that specific FE or application. In the next article we will see SysVars
used to do exactly that.
John W. Colby
www.ColbyConsulting.com