Options Table

[David M C]

I'd like to store individual users preferences (how forms are displayed,
print & email options, etc). Some people suggest using an ini file, others
suggest using a table in the front-end.

I'd say the table in the front-end is the easiest option but how should the
table be designed?

Perhaps:

SettingID
SettingName
SettingValue

Or maybe each setting should be a field, and only have one record for all
the settings. eg:

NumOfRecords : number
SupportEmail : text
LastOpened : Yes/No
ShowAll : Yes/No
LastOpened : Date
etc

Thanks

Dave

 

[TC]

I'd say it depends to what extent you would need to store values "per
form" or "per report". For example, if you ran the forms non-maximized,
with several forms open at the same time, and the user could move &
size them arbitrarily, you'd probably want to save the position & size
of each form - which calls out for "a record per form".

But if you had nothing like that, and you only had some miscelleous
settings to store, eg. their preferred foreground & background colors
for all data entry controls - I'd be tempted to use a single record.

One problem with the "table in the FE" approach, is that they will lose
all their settings when you give them a new version of the FE! So you
might want to think about INI files - surely in XML for maximum "get
with it"ness, or perhaps store everyones' data in a single table in the
*B*E, with the record(s) for each user distinguished by a unique ID of
some kind.

Or store the settings in the registry under
HKEY_CURRENT_USER\Software\MyCompany\MyProduct\...

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Brendan Reynolds]

I've used something similar to your first example. I've also, occasionally,
had the need to store different options for different forms, or even
different controls, so there could be as many as four fields ...

OptionName, FormName, ControlName, OptionValue.

Although I use AutoNumber primary keys in most tables, I would not see much
advantage in using one for this table. The primary key would be the
combination of OptionName, FormName, and ControlName. (If you don't need the
FormName and ControlName fields, the primary key can be OptionName.)

To avoid the problem with updates that TC mentions elsewhere in this thread,
I've kept the option table in a separate MDB. I keep it in the same folder
as the application MDB so that code in the application MDB can easily find
it using CurrentProject.Path. If I had to do it again in the future, though,
I might well consider using an XML file as TC suggests.