title: MS Access Globals & Settings Best Practice
body: <p>I'm looking for the best practice/solution to the following scenario.</p>
  
  <p>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)</p>
  
  <p>Currently, the structure of the table is a single record with field names that represent the particular setting (awful, I know). Example</p>
  
  <pre><code>tbl_global_settings.reminders_red = "7"
  tbl_global_settings.reminders_yellow = "15"
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>Key                   | Paramater
  ---------------------------------
  reminders_red         |         7
  reminders_yellow      |        14
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Public remindersRed As Integer
  Public remindersYellow As Integer
  
  remindersRed = nz(Dlookup("parameter","global_settings", "[key] = '" &amp; "reminders_red" &amp; "'")) 
  etc.
  </code></pre>
  
  <p>Thankyou</p>