hide tables and queries - use access

[tracktraining]

Hi All,

I am able to code user access level at the form level, but can i also allow
user access based on their level for tables and queries?

I have three levels of access:
1. Superuser = allow access to tables, forms, reports, codes.. everything
2. Admin = allow access to forms, reports
3. user = allow access to only the SEARCH form and reports.

So, I need help in hiding tables and queries from user and admin.

Please help with advice or suggestion.

Thanks,
Tracktraining.

 

[Chris O'C via AccessMonster.com]

Study the security faq and practice on a copy of your db. Don't skip any
steps.

http://support.microsoft.com/kb/207793

Chris
Microsoft MVP

 

[tracktraining]

Hi Chris,

I looked over the document on the website. I notices that there is no
mention of Windows log-in. I want to use the Windows login information
(username) to provide access level.

I am able to obtain the username of Windows. In my db, i have a table that
has the username and one of the following is linked to it (Superuser, admin,
and user). So if the username read is linked to USER, then the user is unable
to open the form. Now I want to do the same with the tables. If the username
is linked to USER, then block the user from accessing the tables.

code for checking user access level in form:

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strGetLev As String

strGetLev = "SELECT * FROM EmpInfo WHERE EmpInfo.EmpEmail = '" +
GetComputerName + "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strGetLev)

MsgBox GetComputerName
MsgBox rs!AccessLev

If rs!AccessLev = "User" Then
MsgBox "Access Denied"
Cancel = True
End If

End Sub

Can something like this be done for tables?

Thanks,
Tracktraining

 

[Chris O'C via AccessMonster.com]

You have two choices:

1 - implement user level security and assign the user to the group who has
permissions to open tables and/or queries.

2 - upsize the tables to SQL Server and apply security to the tables from
there.

I know you don't want to go to the trouble to add security, but without
security *everybody* can open the tables and queries, link to them or run
them from other programs, etc.

Chris
Microsoft MVP

 

[tracktraining]

Thank you for clearing that up for me.