Best practice for setting DB wide default

[sheusz]

Apologies in advance for the wordy post.



I'm looking for some advice on the best way to set a system wide default in a split DB.

We have a number of offices that are all running their own versions of the same DB. The offices are not linked, that's for the future. However sometimes data is shared across the offices, so each office has a unique region assigned to it and they assign this region to the customers in their customer table.

Each location has a Back End and a number of Front Ends. At each location the Back End is stored on their local server as are the Front Ends. Each user has a shortcut on their PC pointing to their version of the Front End on the server. I find this easier then trying to get users to install the Front End on their PC's.

Apart from data in the Back End, all of the offices have identical Back End structure and Front End files. Primary field values like customer numbers are set by the head office so that there is no data collision when sharing data.

Currently, when I create a query, I need to include a prompt that asks the user to enter their region before the query executes so that they get the results they want for their region only.

It works OK but looks a bit lazy and messy, and it means that the users always need to enter their region before any query executes. One of my projects in my final year of employment is to tidy up things like this.

I've been thinking about a couple of approaches including creating a "SystemDefault" table from where I can load values in to the queries, or maybe setting a public variable in the Front End that will be used in the queries.

Whichever way I go, I want to be able to have the user set their region as a default, or change the regional value if they need (that's mostly for head office).

I thought about a form that would allow the user to choose the region from a combo box linked to the SystemDefault table and include a tickbox that will allow them to set that value as a default, thereby only needing them to set the default region value once within and between sessions.

I'm open to any suggestions or thoughts on which would be the best approach to this.

[June7]

Could have a Users table where user is associated with a region. Include network username in table and grab user's Windows login USERNAME with Environ(USERNAME) and lookup user's region from table. Save this value to a variable (global, TempVar, textbox) that can be referenced when needed anywhere anytime throughout db. This can be coded to execute when form opens. Can allow user to change region.

[NoellaG]

You can have a Users table with name; lastLoggedInOn, security settings for that user and so one. These values cannot be changed by the user.
Linked to that with a one to many relation you can have a table with the user settings with the fields UserID, Setting (setting can be 'Default folder', 'Language', 'Region', ...) and Value. Users can change the values in this table.

[CJ_London]

At each location the Back End is stored on their local server as are the Front Ends. Each user has a shortcut on their PC pointing to their version of the Front End on the server. I find this easier then trying to get users to install the Front End on their PC's.

does the shortcut point to their personal version? or do they all point to the same version? If the latter, be prepared for corruption.

And if each region has their own back end - how are they sharing data?

[ArviLaanemets]

In some location, have/install a terminal server;
In Terminal server, have a profile created for every user from any of your offices, and the the copy FE of your DB installed either in their TS user's profiles, or in some shared network resource in same LAN with TS, for every DB user;
In same terminal server, have Access installed and available for all DB users;
In same LAN with terminal server, have a shared network resource, to where every DB user has access rights needed for them, with your DB back-end stored there;
Set up Remote Access rights and rules to Terminal Server and to LAN with TS , for every DB user.

Users use Remote Access to log in into their Terminal Server profile in this specific location, and from there start their copy of DB FE. All FE's and the BE work now in same LAN.

[NoellaG]

or use a SQL server as database, create user groups and views per region and set the security per user group. Link all FE to the same SQL database.

[sheusz]

Hi CJ

does the shortcut point to their personal version? or do they all point to the same version? If the latter, be prepared for corruption.

And if each region has their own back end - how are they sharing data?

Each user has their own personal front end. When needed data is imported from one DB to the other using update queries. It's usually only head office that does this.

[sheusz]

This sounds like an idea. I already gather that information for logging in and security, so I could reuse some of that.

[sheusz]

Thanks for the suggestion. I'll have a close look at what is involved in each location.

[sheusz]

TBH, I think moving to SQL is beyond me. That can be a project for the next team when we link all of the offices together.

[sheusz]

Thank you all for your suggestions. I went with June7's suggestions since I already had most of thsi done. I just didn't think of using it in that way.

Could have a Users table where user is associated with a region. Include network username in table and grab user's Windows login USERNAME with Environ(USERNAME) and lookup user's region from table. Save this value to a variable (global, TempVar, textbox) that can be referenced when needed anywhere anytime throughout db. This can be coded to execute when form opens. Can allow user to change region.