Question How to ensure user only sees their data (1 Viewer)

[rik59]

Hi, I have just created a database that is intended to sit on a shared network drive and can be accessed by up 70 memebers of staff. Each member of staff has personal data stored in the database. One of the options on the user form is to search for and allow editing of their details. Clicking on a button brings up a search box where they can enter their surname and see their details. However, there is nothing stopping them from entering someone else's surname and finding their details. How can I restrict it so that they can only search for themselves.
Different staff have different network security rights and anyone can access the shared drive from any pc on the network
The database is designed in Access 2010 and is not split into a front end and back end.

 

[vbaInet]

Different staff have different network security rights and anyone can access the shared drive from any pc on the network
The database is designed in Access 2010 and is not split into a front end and back end.

Sounds like you know the right thing to do (i.e. splitting the db) but you've chosen to go the other way. You've obviously got your reasons for this setup?

 

[Frothingslosh]

1. Split the database.
2. Use a SQL Server back end. The Access backend is simply not secure enough for what you want, as it has virtually no security built into it.
3. Include a user's Username in their personal info, and then use VBA to ensure that the only data that comes up is the data that matches the username they are logged in as. (You can code in exceptions for HR and the like.)

 

[rik59]

Wow, thanks for the prompt responses.
Splitting the database is no problem but I have no experience of setting up the SQL server back end - yet! From what I have read I think you are right so I will just have to continue working through it and talk to my techie team to get access to the SQL server.

 

[vbaInet]

Hi, I have just created a database that is intended to sit on a shared network drive and can be accessed by up 70 memebers of staff. Each member of staff has personal data stored in the database.

You will probably find problems if you end up having 70 concurrent users using Access, although Microsoft says it should support 250/255 users (don't remember the exact number) but I think this is just for read-only access. Access tends to do ok with 30 or so users but then again it can be more, it just depends on the quality of the build.

My advice is considering the number of users and the sensitive nature of your data your'e better off seeking a more robust solution, i.e. getting a professional to build a .NET/Java with SQL Server/Oracle (or similar) solution.

 

[rik59]

I think you're probably right to say look at getting one built. Although the staff won't be accessing the database at the same time it is something that has to be stable and has to last over time.

Thanks for taking the time to consider this.