Access: Multi-User Multi-Company database design - how to change companies for recordsources
Posted on 2003-11-18
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?