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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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

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.

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.

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.  

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

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

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

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.