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 clients 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 wont 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 developers information or just a pretty picture, or the clients logo. These kinds of operational decisions can be programmed by creating SysVars that are then referenced by the Applications 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 clients 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 dont 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 Thats 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 Thats 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