Deployment- Storing Application Settings

[GBalcom]

What is your preferred method of storing application settings for access? Things like Back end location (so an updated FE knows where to look), User Preferences, and other "global" settings. I'd prefer not to hard code them in VBA, because they may need to change at some point. But that is an option.

This is my first time creating an app that will "live" off-site for me. So, I'm trying to be extra cautious, because I won't be there to immediately support it. The BE will reside on a local server, and the FE will be deployed to 5-10 user computers.

So far I can see I have a few avenues:

• Add a table in the back end, with 1 record, and all the settings I would need that are common across users. Then do something else for the user-specific settings.
• Use an .INI file (Still researching if all machines could point to 1 .INI on the server and if this was a recommended practice.)
• Use Registry settings for all of them. I'm not against this, and maybe this is the preferred method?
• Hardcode all of the "global" settings in each FE version. It may be easiest because it's automatic, but if a change is required, I would need time to release a new FE Version. I will be employing a type of FE Auto-update, so future deployment is easier.

 

[NauticalGent]

I personally like the Table based solution. If you choose to build in some User-defined settings, it makes it much easier.

 

[GBalcom]

NauticalGent,
Thank you for your feedback. So, I'm guessing that your table for this is in the BE, and you have a record for each user? (So it can hold user-specific settings as well?) This seems like a good option.

 

[isladogs]

Most of my split databases use a SQL Server BE and are used by multiple clients, each with their own settings.
Certain settings are stored in a BE table so these remain available when a new FE i distributed via my website.
However that clearly won't work for settings related to the BE connections etc so I have a small number of FE tables for those.
The data in those is automatically backed up to a separate 'side end' database only used by the program administrator.

When I release a new version of the FE, the FE settings table is first cleared and the app is put into 'update mode'
After download, the program admin runs the FE and all configuration settings are automatically retrieved from the side end database.
Other changes may be made to the FE or BE e.g. changes to table structure. Again this is automatic so the whole process is 'seamless' for the program admin.

Once completed, the updated FE is placed in an upgrade folder on the network.
Whenever standard users run the app via a desktop shortcut, it checks if a newer version is available and, if so, the update is coped from the upgrade folder to the user's own workstation.

The system has been in use for 20 years at multiple sites and it runs like clockwork...

 

[NauticalGent]

Col covered better than I could have. The only other thing I have done is used a local (FE) table to store the names of the BE tables and their connection strings for relinking

 

[The_Doc_Man]

I'll just point out that depending on the ferocity of the local IT department, registry changes might be considered a no-no. However, smaller shops likely won't have that problem. To be clear, I am therefore suggesting that the registry isn't necessarily the best place for this kind of data.

 

[isladogs]

Col covered better than I could have. The only other thing I have done is used a local (FE) table to store the names of the BE tables and their connection strings for relinking

Hi. Yes I do that as well.
I use DSN-less connections and store the info in the configuration tables in the FE (with a backup in the side end).
I actually do this as two related tables:
a) tblTableLinkTypes - connection strings for each linked database
b) tblTableLinks - names and aliases for each linked table together with linked database ID

 

[Pat Hartman]

I have three FE tables, one of which us updateable by the user.
1. The switchboard Items since this could change if the app itself changes.
2. The "token" which in my case is the license field that defines when the app expires and what features have been activated. I don't have so many users that I need this to be updated by the user but it could be updated once per year when the license is renewed.
3. The default BE location.

When I distribute a new FE to a client, my situation is a little more rigid than Colin's in that I have the client Admin download the update rather than the individual users or I send it via email. The Admin unzips it locally and opens it, the default location is used to link the BE. If it exists, that is the end of the process. If the client is using a non-standard location, my relink dialog pops up and the Admin has to navigate to the location of the BE. Once the connection is made, the default BE location table is updated to reflect the new location and the last step is for the Admin to replace the old copy in the Distribution folder with the new one.

The process for distributing BE changes is more complex.

 

[gemma-the-husky]

As well as using tables in the front end, I often use an INI file, and also store some settings in the User's registry. (nothing fancy - just the part exposed to VBA with GetSetting and SaveSetting). It depends whether I want the options to be application wide, or just affect the user's experience - eg, I allow each user to select their preferred background effect for forms.

 

[isladogs]

When I distribute a new FE to a client, my situation is a little more rigid than Colin's in that I have the client Admin download the update rather than the individual users or I send it via email. The Admin unzips it locally ....

...The process for distributing BE changes is more complex.

Looks like you misread my post as your situation is not more rigid than mine.

As previously stated, the program admin downloads the new version from my website and runs the update process...which is completely automatic then places the new FE in the upgrade folder on the local network. The standard user has no access to any of the above and does absolutely nothing.

The process for distributing BE changes doesn't need to be more complex. In my updates, any changes to the structure or data in Access BE tables is managed using a procedure when runs automatically after relinking is completed. If there are changes needed to the structure or data in the SQL Server BE tables, this is managed by running a SQL Server install script. Once again, this happens automatically when the program admin runs the update to the new version

 

[Pat Hartman]

Sorry, I thought you allowed individual users to download. I didn't notice the reference to "admin".

 

[AngelSpeaks]

What is your preferred method of storing application settings for access? Things like Back end location (so an updated FE knows where to look), User Preferences, and other "global" settings. I'd prefer not to hard code them in VBA, because they may need to change at some point. But that is an option.

This is my first time creating an app that will "live" off-site for me. So, I'm trying to be extra cautious, because I won't be there to immediately support it. The BE will reside on a local server, and the FE will be deployed to 5-10 user computers.

So far I can see I have a few avenues:

• Add a table in the back end, with 1 record, and all the settings I would need that are common across users. Then do something else for the user-specific settings.
• Use an .INI file (Still researching if all machines could point to 1 .INI on the server and if this was a recommended practice.)
• Use Registry settings for all of them. I'm not against this, and maybe this is the preferred method?
• Hardcode all of the "global" settings in each FE version. It may be easiest because it's automatic, but if a change is required, I would need time to release a new FE Version. I will be employing a type of FE Auto-update, so future deployment is easier.

I was just about to start a similar discussion, so please forgive me for reopening this. I was also thinking of the table option on the FE but my question is when do you execute this? I was thinking the Form Load of the Navigation form. If the column holding the location of the linked tables is blank or a yes/no column, then run the link code?

 

[NauticalGent]

I was thinking the Form Load of the Navigation form.

If your Navigation form is the Display Form in your Current Database Options, then yes.

If the column holding the location of the linked tables is blank or a yes/no column, then run the link code?

I do not see the need for a yes/no column and only the names of tables to be relinked, including the connection string, should be in this table; no need to perform any logic checks, simply run the code for every table listed.

 

[NauticalGent]

I was just about to start a similar discussion, so please forgive me for reopening this.

Asking a germane question in an existing thread is preferred to starting a new one. It shows you at least looked for an answer and keeps a more comprehensive "trail" on the subject matter.

Nicely done.

 

[AngelSpeaks]

If your Navigation form is the Display Form in your Current Database Options, then yes.



I do not see the need for a yes/no column and only the names of tables to be relinked, including the connection string, should be in this table; no need to perform any logic checks, simply run the code for every table listed.

Thanks Nautical!

 

[isladogs]

I use two tables for managing DSN-less connections
1. Table with external database(s) info including connection strings and passwords
2. List of tables together with the related database ID from the first table.

This approach allows me to swop BEs easily as & when needed e.g. between the development BE(s) and the live BE(s)

 

[NauticalGent]

Easy day, welcome to AWF!

 

[AngelSpeaks]

So I did some more research, and on this forum, I found a post that stated that if both FE and BE were in the same folder, they don't have to be relinked (which I tested to be true).

Then I moved the FE to another folder and it also worked OK.

 

[theDBguy]

So I did some more research, and on this forum, I found a post that stated that if both FE and BE were in the same folder, they don't have to be relinked (which I tested to be true).

Then I moved the FE to another folder and it also worked OK.

Hmm, that could be misleading. You could be right, but I am not quite sure about it. I would think if the "file path" is the same (not just the folder name), then a relink is not necessary. Are you sure you're not using any auto-relink code? Just curious...

 

[theDBguy]

So I did some more research, and on this forum, I found a post that stated that if both FE and BE were in the same folder, they don't have to be relinked (which I tested to be true).

Then I moved the FE to another folder and it also worked OK.

Since I was curious, I just did a quick test. I created a new FE and BE on my Desktop. I then moved them to a new folder on the same Desktop. When I opened the FE, I got the "Could not find file..." error message when I tried to open the linked table.