Access:  Multi-User Multi-Company database design - how to change companies for recordsources

Posted on 2003-11-18
Last Modified: 2008-03-06
Previous scenario: My client has multiple facility locations, multiple domains, uses NT groups, has Access database with SQL back end.  On terminal server, when a user logged in, a sql Stored proc would grab their domain and use it as a filter for obtaining their location records.  The master access database would be temporarily  copied to their terminal server user directory and relink tables based upon their domain/filter.  (this was done this way because it was the quickest solution to using terminal server in a weeks' notice without having time to redesign the database)

NEW scenario: The users will all be on one domain soon.  I think we'll use naming conventions for NTgroups, using the 1st 3 letters to indicate which location they need to get tables for.  I'm NOT wanting to copy the database at all, but rather, would like to have one multi-user database that a person can select a Location/Company from, and then have their data accordingly.   I envision the database having a "Choose Location" item on the toolbar, then checking permissions, then doing something to ensure the data is filtered for that location.

Here's the problem:

I'm User#1 and want Company A's records.
You are user #2 and want Company B's records

When I selected Company #1, the linked tables (such as tblCompany) were relinked with my filters.  When you, as user #2, selected Company #2, the tables were relinked (same name as my tables) to your filter.  In a multi-user environment, this won't work, because I think the linked table would be tied to whoever the last user's "select company" filter would be.

This database has TONS of objects in it and to make everything an ODBC connection would take tons of time.  Again, we don't have time to redesign the entire application.  All recordsources are based upon the linked tables, which are views limiting data to the company chosen (as linked tables).  What is the best way and quickest to get multiple users to be able to use the same database with different records?

Question by:mariann
LVL 30

Expert Comment

ID: 9772075
Are the views in Access or something like SQL server?

It's a long shot, but in SQL server, you can get the logged in user (SQL or NT), its a global variable, I can't remember which.

You could have a songle view on each table which automatically filters on the current user, set up in SQL server.

The only other option is to do it inside forms etc., as this is the only other spot where you 'aware' of the user.

LVL 28

Expert Comment

ID: 9772091
Can you not cretae a view in SQL based on the user name so the view filters the records. Cheers, Andrew

Expert Comment

ID: 9772163

Create Startup Form.  In Onload Event of this startup form you can filter out unwanted data for a particular user.  All the required tables should be linked once upfront but users can see only the data they need to see by using above mentioned filtering in startup form.

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 32

Expert Comment

ID: 9772425
Hey mariann!

  I'm using terminal services on a large scale Access app, but what I'm doing is maintaining a copy of the frontend in the users application folder on the server that is already linked to the appropriate tables.  All users log on to a separate copy of the front end as they would if it were located on their client machine.

  just my two cents.


Author Comment

ID: 9773786
Hm, I think Jagdish's idea would require that we change every single query and every single ODBC connection string to use this filter.  That would take alot of work.  THe other one with user's variable making a view of each table is what I've done in other databases, but this one's got too many tables/views as it is.  

Jack, I'm thinking something similar along the lines of your terminal server situation.  But rather than have each user have their own copy, I'm thinking that I"ll make a separate database for each location (which already has been filtered) , and when the user in is the menu (another .mdb), the correct location database will open based upon their location (which is retrieved from their NT group or SQL role).  That way, there's only 5 databases to worry about and not much else and hardly any programming changes.  

LVL 32

Expert Comment

ID: 9774030
You may run into problem if the application uses local (resident) tables to cache things like user or filter settings.  This is what caused me to just move the client mdb to a separate client folder on the server, I gained all that "data-over-network" time back by riding the client directly over the data.  The storage requirement does however shift to the application server....
LVL 30

Expert Comment

ID: 9777261
To expand on Jagdish_Bodani's comment, You could go through each form and query and change the filter property programatically upon startup.

but remember:

1. Need to remove user Access to filters
2. Need to ensure that the filter field is in the query
3. Need to ensure any other programmatic filters take it in to account.

However if you wanted to open a seperate database, it might be easier to seperate the forms and code from the data so that you don't need to maintain 5 copies of the same code.


Expert Comment

ID: 10064316
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)


Cleanup Volunteer

LVL 32

Expert Comment

ID: 10077724
It's not like the questioner didn't get any good feedback and ideas from this.  What say you mariann??

Accepted Solution

Computer101 earned 0 total points
ID: 10100026
PAQed, with points refunded (300)

E-E Admin

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question