User controlled program/security settings

[joelmeaders]

Hello Everyone,



I can't seem to find any information on this subject and was hoping for some insight. What is/are the best ways/practices of storing user selectable database and program settings? All settings are shown on administrative forms.


Originally I thought of setting up a table as shown below and writing functions to write/read the data:

setting_ID (primary key, auto number)
setting_ProjectID (multiple projects in same db that need different settings)
setting_Name
setting_Value
setting_MinSecurityLvl (1-3, all users have system-wide and db specific security levels)
setting_Custom (for random settings such as multiple emails, custom operation codes, etc...)
setting_Description
setting_LastUpdated
setting_UpdatedBy


Functions would be as follows:

GetSetting("SettingName", "SettingField") As String
(would return the value of the field queried)

SaveSetting("SettingName", *GetProjectID(), "Name", "Value", "SecurityLvl", "Custom", *Now(), *GetUserName()) As Boolean
(Parameters with a * are contained within the function but are shown for example)


Now that I am starting to implement this it doesn't seem like the best route but I can't think of anything better. It is a dirty setup but I also hate to have a table with 150 different fields (named after each setting). These settings contain everything from project access, form access, field access, email alerts, additions and edits, file attachments and more.

There are quite a few databases which are all linked and launched from a central database and they are all already very resource and data heavy (especially for access). I am hoping to migrate everything to SQL servers down the road but that's a couple years away.

Any insight would be greatly appreciated!


*******EDIT: All settings are currently hard coded and I want to move away from having to recode and redistribute the network accde file which client databases auto-upgrade to when launched.

[Rod]

No one's got back to you for weeks! I shall redress the situation.

You don't mention the nature of your implementation: is it a single stand-alone database; multiple implementations of the stand-alone database; or a client-server (front end/back end) implementation? Taking each of these in turn:

1. For a single stand-alone database consider using the Registry to save the users' settings. Access has built-in functions for this purpose and the settings are saved to the user's hive, so they may be different for each user. Is it by chance you are actually using the Access function names, SaveSetting and GetSetting?
2. You're on your own. Unless the users carry with them their settings (USB?) then I don't see how to transfer settings from one discrete implementation to another. (Cloud computing?)
3. A table at the server end seems to be the only way.

Hope this helps.

[joelmeaders]

Thanks Rod,

The back end databases are located on a server in a central location and all settings I have implemented to date are overall settings affecting all users. I did create the settings table at the server level with all fields mentioned above.

The functions to read settings were custom and they worked but were slow using both a DLookup, allen browns ELookup, and via a queried recordset so I ended up using the solution below, which is very fast.

Upon database startup the entire settings table is opened in a ado recordset and then put into a multi-dimension array via a custom function (error trapping not implemented yet. Get records is a custom multi-purpose function that can both read records into memory and also populate unbound form fields based on control names and tags. In this case GetRecords only reads to a recordset in memory. SettingsArr, SettingsRowNum & SettingsColNum are globals):

Public Function LoadSettings() As Boolean


LoadSettings = False


If GetRecords("tbl_dbSettings") Then
    If Not rst.EOF Then
        SettingsArr = rst.GetRows(rst.RecordCount)
        SettingsRowNum = UBound(SettingsArr, 2)
        SettingsColNum = UBound(SettingsArr, 1)
        LoadSettings = True
    End If
End If


End Function

I changed the read settings function from one that queried a single record and returned results to one that loops through the array and returns the requested value. This method works instantly and there is no more network lag when looking up settings.

Public Function ReadSetting(sName As String, sVar As sVariant) As Variant


Dim sCol As Integer
Dim rCounter As Integer
Dim tmpVal As Variant


sCol = sVar


For rCounter = 0 To SettingsRowNum
    If sName = SettingsArr(0, rCounter) Then
        tmpVal = Nz(SettingsArr(sCol, rCounter), "")
        If sVar = 1 Then
            ReadSetting = CBool(tmpVal)            'True/False On/Off
            Exit Function
        ElseIf sVar = 2 Then
            ReadSetting = CInt(Nz(tmpVal, 1))      'Security Level 1-3
            Exit Function
        ElseIf sVar = 3 Then
            ReadSetting = CStr(Nz(tmpVal, ""))     'Custom Setting Value(s)
            Exit Function
        ElseIf sVar = 4 Then
            ReadSetting = CStr(Nz(tmpVal, ""))     'Setting Description
            Exit Function
        End If
    End If
Next


End Function

sVariant is defined as:

Enum sVariant
    Value = 1
    Security = 2
    Custom = 3
    Description = 4
End Enum

There's more work I need to do such as adding error trapping (I have custom functions for this as well that auto-report bugs) and adding a function to check and reload settings if an unhandled exception causes them and the globals to disappear.

[Rod]

Effectively you've eliminated the disk access overhead - or at least loaded it all into startup - but will stilll incur disk/memory swapping if the array is large? This is a cute solution but I'm surprised the gains are as good as you imply.

Hm! Another technique to store away in that receptacle called my brain. :-)

[joelmeaders]

Just loading the recordset (I have 48 settings records with all fields listed in first post currently) takes about 1-2 seconds and it only increased the program memory use by a couple hundred kilobytes and returning the data is instant.

For some reason when I was using any of the other methods to query and return 1-2 values at a time it would take 3-7 seconds and memory use would increase each time even after the functions would end.

It could just be because of the network I am on or Access but there can be big speed issues, especially when 10-15 people are working within the same database. Everyone has a front end client installed on their own machine (local profiles). I set all programs/databases up with only unbound forms and do as much as I can to limit constant bandwidth use.

I have a larger project coming up that will have hundreds of settings and I'll try this out too.