How to determine user role through Access front end??

[hblabonte]

Hello all,

I apologize upfront for the lengthy post.

I am using SQL Server as a backend, and Access 2000 as a front end. Research has indicated that "if you are using Access 2000/2002 Projects with the new MSDE or SQL Server 7, you are limited to data security only". Hmph.
What I need to do is determine a method of capturing a user's role through the front end to allow access to forms, reports, etc.

I can use the CURRENT_USER to capture the user ID, but what I really want is to check(using VBA) what role he/she falls into. If the user is in a particular role and should have access to form123, then allow them to open the form, otherwise do not.

I have also found the IS_MEMBER function which is great, but when I program it in the Access side, it is not a recognized function. Any suggestions are welcome!! Coding examples would be most helpful.

thanks in advance!

 

[tlbroadbent]

Here is one possibility.

1) Create a pass-through query that returns a record set contining the database roles for the current user. Save as qListRoles.

Select
system_user As LoginName,
user_name() As UserName,
r.Name As RoleName
From sysusers As u
Inner Join sysmembers As m
On u.uid=m.memberuid
Inner Join sysusers As r
On m.groupuid=r.uid
Where u.name=user_name()

2) Use DLookup to check the query result for a RoleName.

sRole=Dlookup("RoleName", "qListRoles", _
"[RoleName]='db_owner'&quot Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm