For reasons of privacy of employee files (PDFs), which may contain very sensitive data and which can only be seen by the direct colleague who has to deal with that specific employee, and therefore not allow the rest of the sector (other colleagues) to see all PDFs of all the other employees.
Since object-level security was removed from Access maybe in the Ac2003/2007 time range, there is no way to stop someone from getting to those files - if they are determined to do so. Access can be hacked even if you take a great deal of effort that in the process would make the DB nearly useless because of difficulty to use. There is a trade-off here - ease of use vs. protection of data.
Understand that old adage that says that anything Man can do, Man can undo.
You might also consider this scenario: You have an employee PDF. So one of your colleagues works with that employee. A little time passes and the colleague is discovered to have committed a major violation against the company, so gets fired. Now you have this scheme in place that would block others from accessing that file... but SOMEONE has to take over responsibility for processing that employee's issues. So NOW what? Try to not paint yourself into an inescapable corner. The more you overthink this, the more inescapable the dilemma when you discover that a particular colleague was more than slightly fallible.
Or suppose the colleague gets pregnant and eventually takes a 3-month maternity leave, just as the employee needs whatever support colleagues give. NOW what?
Or suppose the employee files a complaint against the colleague and requests a new point of contact. Now what?
Beware of absolute protections because the world in which they are found is NOT absolutely predictable. At some point, you have to be able to trust someone - some PERSON - to make a decision, because programming a ton of logic is a LOT harder than having someone who can just make the decision to override the restriction.
Here is one approach (certainly NOT the only possible approach):
1. Block the place where you put the folders so that only the colleagues can see that. Use Windows group permissions to do this. Talk to your SA or IT admin about setting up a group ID for the colleagues and make the folder for the PDFs be restricted to the colleagues, no access to the general public. This involves NO CODE WHATSOEVER on your part and actually for most security managers is a valid approach.
2. In the code of this DB, you have a field somewhere that holds the ID of the colleague assigned to the case, Then you have the code that would open the PDF just disallow access to that file to any other colleague. This ID would probably be associated with the employee table and could be a small field like an integer ID number. The key is that your code doesn't allow a colleague to set that value.
3. Have a "super-colleague" who can override assignments or make new ones. So... get a new employee, the supervisor assigns a colleague to the case and you're off to the races. Get a new colleague, the supervisor assigns this person some employees just to give them something to do.
There is one more reason and that is if you think the db might ever be converted to SQL Server, the Hyperlink data type is not supported and so you would have to convert the hyperlink to text and change all your code. Since most of my apps get upsized or even start out that way, I can never use the abomination data types even if I think they might be easier.
)
