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.

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Programmer 14 46
Attachment field in SQL 3 25
Cannot Link Master/Child Fields in a Navigation data sheet Sub Form 1 30
ms access filter query with empty combobox 5 28
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now