I'm looking for the best practice/solution to the following scenario.
We have a multiuser ms access database that allows the configuration of "global_settings". These settings are applicable to all users. This data is currently stored in a table 'tbl_global_settings'. (we have a table user_settings to handle individual stuff)
Currently, the structure of the table is a single record with field names that represent the particular setting (awful, I know). Example
tbl_global_settings.reminders_red = "7"
tbl_global_settings.reminders_yellow = "15"
These values are used for conditional formatting expiry dates for system reminders - when a reminder is due within 15 days it will be yellow, due within 7 will be red.
When the database loads, a hidden form called frm_global_settings is loaded with all fields from the table tbl_global_settings. These are then easy to access as required by referencing the form field.
This works pretty much perfectly. However, as this system has grown over the last few years, the number of fields has increased (60+) and this just doesn't seem like the best solution.
I was considering moving this to a skinnier table with Key, Parameter style approach. This concerns me though as the use of DLookup() in MS Access seems to constantly cop a blasting for inefficiency. Example:
Key | Paramater
---------------------------------
reminders_red | 7
reminders_yellow | 14
The values are rarely changed but often called, I was wondering if anyone could comment on perhaps loading this data into global variables or a global array at startup? Example:
Public remindersRed As Integer
Public remindersYellow As Integer
remindersRed = nz(Dlookup("parameter","global_settings", "[key] = '" & "reminders_red" & "'"))
etc.
Thankyou
Your current approach (one row, many columns) is not "awful". It does get a bit unwieldy as the number of columns grows, but it will work fine until you have more than 255 values to store (the limit on the number of columns in an Access table). It also has the advantage that each column has a specific type and can have validation rules associated with it.
If you have more than 255 values to store you could always use more than one "global settings" table, perhaps based on the setting type (e.g. display settings, folder locations, etc.). You could centralize the lookup logic by creating a Public Function to look up the specified values in the appropriate table and possibly even cache them in a Static Dictionary object to avoid hitting the tables repeatedly.
As for DLookup() it is not inherently inefficient and will work just fine, especially on a relatively small table. (Much of its "bad reputation" comes from its being poorly used by inexperienced developers.)