Limit Data to Multiple Users Based on Their Department

[Steve]

I have a database (it’s called master db) which will contain about 4k
records. There are 20 departments that need access to the database; however,
I only want them to have limited access to the data/records that is specific
to their department.

Currently, we shared the information with each department by creating a
Make-Table Query that creates a table with their specific data in a new
database. This gives them the ability to extract/query the data to meet
their specific needs. Since there are 20 departments, we have 20 make-table
queries to create the tables in 20 separate databases.

The master db is consistently being updated, so the user’s db/tables are
only current each time we run the make-table query for the different
departments.

I have a working knowledge of Access, but I do not consider myself an expert.

I’m looking for suggestions on how I can streamline the process to share the
information in the master db with each department that they need, without
having to run the 20 Make-Table Queries several times a day.

Thanks for your help.

 

[Mark Andrews]

For a light security system you could just use one database (well front-end
db on each users computer and back-end on server, assuming you are on a
LAN?) and keep all 4K records in the same table and have a simple
username/password form which then controls the group access priveledges.

For example if I login as someone in the marketing dept, code would then
change the recordsource of the form used to only query the appropriate
records.
Keep it so users only use forms, they could have MDE front-ends.

Of course this is "light security", if anyone could get to the tables
directly they would bypass the security.

You could see a quick example of something similar on my CRM Template at
http://www.rptsoftware.com/products/crmtemplate/

Hope that helped?
Mark

 

[Paul Shapiro]

If the users just need read-only access to the live data, maybe a website
would give them the views they need without letting them get directly to the
data? Or you could write VBA code to do the data extractions and put the
updated departmental databases onto the file server without any interaction.

If the security is important, this is an area where SQL Server offers better
capability. If you only need one table per department, I assume it's
something like:
Select * From MainTable Where departmentCode = ThisUsersDeptCode

This would be very simple in SQL Server, and by only giving the users read
permissions to that query, security would be quite good. You would need one
extra table of the users' Windows logins and their deptCode. Or you could
write 20 separate queries hard-coding the deptCode into each one, and just
giving each user permission to their own dept's query.

 

[Steve]

Thanks for the suggestions.

If the users just need read-only access to the live data, maybe a website
would give them the views they need without letting them get directly to the
data? Or you could write VBA code to do the data extractions and put the
updated departmental databases onto the file server without any interaction.

If the security is important, this is an area where SQL Server offers better
capability. If you only need one table per department, I assume it's
something like:
Select * From MainTable Where departmentCode = ThisUsersDeptCode

This would be very simple in SQL Server, and by only giving the users read
permissions to that query, security would be quite good. You would need one
extra table of the users' Windows logins and their deptCode. Or you could
write 20 separate queries hard-coding the deptCode into each one, and just
giving each user permission to their own dept's query.