Sometimes you need to limit who can see what data, but you don't want to change table names
If your using a SQL Server backend with Microsoft Access, you can use SQL Server views to customize what users can see, but using the same table names and Access code. Below I outline what was done to accomplish a great feat of Access programming, and at the same time provide my customer with a way to share data with their customers.
Use the same Access file with different user types
One of my best clients asked me if there is a way to share a limited set of data with one of their clients, but using the same Access file for every user, regardless if they were an employee or not. The system needed to startup, detect that the user was a client, then limit their choices to the applications menu, along with limiting their view of what data they can see. The code below could also work if you needed to limit data exposure to multiple department's in the same company.
SQL Server Views to the Rescue
The client would login via remote desktop to the network and launch the same Access file used by employees, so I needed to make sure anything I did worked for all users, including the outside ones. Here's how I made it happen:
- The client got a NetworkID and password to login via remote desktop connection
- We placed them in a new Active Directory Security Group, separate from any employees
- I created SQL Server views that limited access to the data they could see
- We created a new table that would associate the login ID with the particular group of views, in case they wanted to provide access to more customers in the future
- I added code on startup to relink tables to the set of views, thus keeping the table names intact and not having to worry about changing my program to use multiple version names of the tables
The code would be too much to display here, but a great Access programmer would be able to take the concepts discussed above and make it happen.
|
