Custom Settings V2 for MS Access – with IntelliSense and User Settings

Simple. Fast. Extensible. Intuitive. The new version (V2) of dynamic Custom Settings shines with full IntelliSense support, enum-based operation and flexible extensibility – for frontend, backend and individual user settings.

Introducing the new version (V2) of dynamic Custom Settings – a smart solution with full IntelliSense support, intuitive operation and flexible extensibility, all without additional coding effort.

What’s new?

• IntelliSense in all settings calls
• Enum-based operation for maximum clarity and accuracy
• Create custom settings – without a single line of code
• Read, Create, Change, Delete – all integrated
• Frontend, backend and user settings directly supported
• New tables? Integrated with minimal effort

How does it work?

The system is based on three tables and three modules.

The tables:

• tblFrontend – settings for the front end (client-specific)
• tblBackend – global settings (for all users)
• tblUser – individual user settings

The modules:

• modHelper_Settings – all functions: settingCreate, settingRead, settingChange, settingDelete
• modHelper_Enumeration – handles the enums (for IntelliSense), including adding/renaming/deleting dynamically at runtime
• modHelper_SQL – provides secure, type-compliant SQL strings via cSQL()

Storage, types and IntelliSense

All settings are stored internally as strings. When retrieved with settingRead, a type conversion to the originally defined data type happens automatically – the type information is stored in the SettingType column.

Thanks to the enums (EnumSettingName, EnumTable, EnumDataType), IntelliSense is available for every call. You only specify the enum value – the rest is assigned correctly, without lookups and without typos.

Examples

Create (the name is passed as a string; settingCreate turns it into the enum entry):

settingCreate "Theme", dtString, "Dark", tblFrontend
settingCreate "Items", dtInteger, 22, tblFrontend

Change, read, delete:

settingChange Theme, dtString, "Light", tblFrontend
settingChange Items, dtLong, 444444, tblFrontend

Debug.Print settingRead(Theme, tblFrontend)
Debug.Print settingRead(Items, tblFrontend)

settingDelete Theme, tblFrontend
settingDelete Items, tblFrontend

User settings (with UserID as the last parameter):

settingCreate "Active", dtBoolean, True, tblUser, 1
settingChange Active, dtBoolean, False, tblUser, 1
Debug.Print settingRead(Active, tblUser, 1)
settingDelete Active, tblUser, 1

Adding new tables

1. Create a new table (simply copy an existing one), e.g. tblInvoiceSettings
2. Add an enum entry to enumTable
3. Adjust the GetTableName() function

All functions (Create, Read, Change, Delete) work without any additional code – you extend the system modularly, without touching the core code.

Advantages and limits

• Ready to use in no time, no more hard-coded SQL
• IntelliSense prevents errors in names and types
• Settings depending on the area of application (frontend, backend, user)

Conclusion

This new version of Custom Settings with IntelliSense is a huge step toward professional, maintainable and modern Access development: no cryptic SQLs, no errors due to names. Once integrated, you’ll never want to do without it again. You’ll find the detailed description directly in the code, well documented as usual.

Download