• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

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

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?

0
mariann
Asked:
mariann
1 Solution
 
nmcdermaidCommented:
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.





0
 
TextReportCommented:
Can you not cretae a view in SQL based on the user name so the view filters the records. Cheers, Andrew
0
 
Jagdish_BodaniCommented:
HI,

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.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jadedataMS Access Systems CreatorCommented:
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.

regards
Jack
0
 
mariannAuthor Commented:
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.  

0
 
jadedataMS Access Systems CreatorCommented:
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....
0
 
nmcdermaidCommented:
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.



0
 
ornicarCommented:
----------------------------------------------------------------------------------------
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/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
0
 
jadedataMS Access Systems CreatorCommented:
It's not like the questioner didn't get any good feedback and ideas from this.  What say you mariann??
0
 
Computer101Commented:
PAQed, with points refunded (300)

Computer101
E-E Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now