Settings - best approach - Public constants or dlookup 1

[Moss100]

I am writing a database that has quite a few settings which are used throughout the database.

I am wondering what the best approach in dealing with storing the settings is.

I am thinking either to have a Settings table and then use dlookup to get the setting values when needed, or whether to store the settings as constants in a module.

The table makes it easier to view and edit the settings (especially for the user if I tie the table to a form), but the constants appear quicker and are a bit more flexible in some ways.

Perhaps there are other ways of approaching this. Any advice would be gratefully received.

Thank you mark.

 

[lameid]

You can store the values in a table and then set them to Tempvars in a startup process.

Tempvars are better than global variables in that when code fails, the values persist unlike global variables.

Tempvars exist as soon as they are used. The below sets a new Tempvars value Maximizeforms...

Tempvars!MaximizeForms = Dlookup("MaximizeForms", "SettingsTable") 'but since this is startup, it probably makes more sense to read everything with a recordset to get multiple values rather than multiple dlookups

A more traditional way to go if using a table...

Function MaximizeForms () as boolean
static blRunBefore as boolean 
Static blMaximizeForms as boolean

If blRunBefore then 'default / first run value is false
  MaximizeForms = blMaximizeForms 
Else
  blMaximizeForms = Dlookup("MaximizeForms", "SettingsTable") 
  MaximizeForms = blMaximizeForms 
End if
End function

The first way requires you to load all values at startup and the second uses a function call that will remember the value because it is using a static varialbe. The first way has vaules that persist no matter what, the second could initialize but will just have everything initialized and will just run form scratch as if it is the first time. The real difference is whether you take the time to do it at startup or to do it when the value is first used. Memory could be a concern too. Setting everthing at startup potentially requires more memory if some values aren't used in the session. One other thought if you embed a tempvars variable in a SQL statement and open it as a recordset, it will error out but a function call works. A workaround if using tempvars is to have a function return the tempvars variable value.

Global constants is the fastest. The above are two ways to deal with using a table either for configurability or maintenance resaons. You can mix and match these two for table values but I would definitely either use just constants or just table values or someone will be confused later (maybe even you).

 

[Moss100]

Thank you -

I have tried setting TempVars.

Can TempVars be set at module level, so they are available when Access opens, or do they have to be set from a form event?

I have successfully tested it by using it with the form load event:

TempVars.Add "checkPeriod", 3

The problem with this, is I would like the TempVars to be set when the database first opens, without having to rely on loading a particular form. Is there a way to do this? (Having to have a certain form load, particularly when developing would be a bit of a pain).

I was hoping that I could set a TempVars to be available like a Public Global Variable stored in a module.

Many thanks for your continued help. Mark

 

[Moss100]

I have looked further. I can set the TempVars by using the Autoexec macro, but again if I use the shift key to enter the database using for development, then the Autoexec macro does not fire.

I'm trying to use TempVars and ensuring they are always available (like a constant would be) is this possible?

Thanks Mark

 

[Moss100]

For others, I ended up having a settings table holding all the values I may have previously stored as constants.


I have a function that runs through the table and create the TempVars from the data in the table.

I run this function through an Autoexec macro at startup and also on the opening of the main form (just to make double sure that the TempVars get set - if the database is opened by shift key the Autoexec doesn't run, but at least once the navigation form is opened the macro runs)

Hope this may help others - I find it very useful - much easier than using dlookup around the database.

Regards Mark.

 

[MajP]

Although this approach works fine, I am a bigger fan of the approach shown by LameID. This approach provides a little more flexibility and a little easier to manage.

Function MaximizeForms () as boolean
static blRunBefore as boolean 
Static blMaximizeForms as boolean

If blRunBefore then 'default / first run value is false
  MaximizeForms = blMaximizeForms 
Else
  blMaximizeForms = Dlookup("MaximizeForms", "SettingsTable") 
  MaximizeForms = blMaximizeForms 
End if
End function

Advantages
1) Error checking the return value. I can run each function from the command line and test the values.
2) Error checking within the function. Example: Assume you had a setting for Form width. Not only does this value have to be numeric, it has to be positive and cannot exceed an allowable value. If it does not meet this maybe you want to set it to an acceptable value or provide some detail error message. Or a setting for a default form. Not only does that have to be a string, but that form name has to be a name of an existing form. So you can validate the values when you use them.
3) Never have to worry about running the code to load the variables, do not need a startup form or autoexec macro. Loads when you call it.

Now you can do it the way you are doing, but you would then validate the values as they are entered into the table and not when you pull them out. So when they enter Form Width into the settings table you validate the value there.

Efficiency and memory is probably not an issue with either approach, unless we are talking thousands of settings. Loading all your tempvars upfront or loading the static variables when you need them is unlikely to be noticeable.