Sql Security vs Access Security.......

[Dale DeWitt]

I am trying to determine how to make use of the security
I have already set up on my SQL 2000 Database. I have all
my users assigned to a SQL Role. I am wondering.... is
there a way to make use of that, or do I need to
implement Access security as well....

My idea was that I would maybe query the databse using
the "IS_Member" function to determine what role the user
belongs to. Admin users would have full rights to modify
the data on the forms. Users with view only rights would
be assigned to a different ROLE, and I would lock down
the form and not allow changes, and I have another ROLE
that can only change a select list of fields/columns. I
know I can do this in tha form activate event, but I am
having a disconnect in my brain as to how to make this
come together..... Would I run the "IS_MEMBER" at system
startup, and then pass it to all my other forms... Is
there a global data area in ACCESS ??

I think I am on the right track, but I am not connecting
the VB side with the SQL side is all, and need some
advise...

Thanks.

 

[Scott McDaniel]

You can create a global variable in Access by declaring a variable with
Public scope in any standard module:

Public gYourVariable As Boolean

On startup of your Access frontend (usually done through a startup form) you
can then write the value of this variable based on the return of your
Is_Member function:

Function GetMemberStatus()
gYourVariable = Is_Member
End Function

To set your startup form, click Tools - Startup from the main access menu