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

Posted on 2003-11-18
Medium Priority
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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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,...plus 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

588 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