Am a Newbie to this forum.

Am modifying some adp's with a SQL Server 2000 backend. The adp's are available on our intranet in two versions of an ade files, one for read only, the other read and write.

I'd like to use just one ade file that programatically makes the write controls visible depending on the User's membership to a SQL Server Role. The following is a working Stored Procedure that has an output of either 1 or 0 to determine if the User is a member of the required Role:

Create proc sprocIsMember
    @intAnswer int output
as

if is_member('MandMWriter') = 1
    begin
        select @intanswer = 1
    end
else
    begin
        select @intanswer = 0
    end

GO

Does anyone know how I can get the output from Access VBA? I've found examples of how to get a recordset, when one is created in a stored procedure, but not an output.

I will also need to find a way of getting the User's name to use as a prefix when creating and deleting some temporary files in a common folder.

Am wondering if it might be better to kill two birds with one stone and create in SQL Server a temporary table (I'll would have to study that) that contains one record and two fields: "fldIsMember" and "fldUserName"

Perhaps there is a way I can get this information from Windows Security directly through Access VBA and not bother with SQL Server.

Any help or ideas would be appreciated!

__________________
Technical Support Electrician

The way I usually handle this is to create user tables and refer to them to determine the user's level of access in the SQL server database. I do this so that the readers can log in using one reader account, and writers using a writers account. Then I give them access to different buttons etc based on their membership.

Store username and access level in a table in SQL.

Then when the user logs in using either account, I run this module from Access:

Function sU()
sU = (Environ$("Username"))
End Function

Then I use sU to check the user table, and post the results to a hidden form. I refer to that hidden form for the user group when the user opens forms. Based on their group, a button will appear or not.

Did that help any?

mmcdonal

mmcdonal:

Thanks for the quick reply. Yes that does help, in a way I wasn't looking for. First, let me explain my position. You may be able to help me in other ways I'm not looking for.

I'm in the Maintenance Dept. The IT Dept have resourses to help the Business and Production Depts, but have little for the Maintenance Dept. We also get little help from Engineering. It might be best this way. So, my position is Technical Suppport Electrician. I keep the Autocad drawings updated and organized and provide a database of the electrical loads, mostly motors. I get what general knowledge I can, but mostly concentrate on what I need to know for a particular need.

The IT Dept gave me owner privelages to a SQL Server Database with the understanding that no support would be provided. Everything I do, I try to imagine what would happen if I "Won The Lottery."

I'm grateful that you mentioned the Environ$() function. I didn't know about it before, and plan on using the "Username" and "Temp" parameters.

I had considered doing as you suggest and having a table of user names to determine the the privelages of the user. I'm reluctant to do so, because if I "Win The Lottery" the table won't be maintained. I would rather rely on the User Group that is assigned by IT. This would give the the user privelages already set up in the Database and would be the same privelages granted by making controls visible.

Since I wrote the first post. I discovered that I could use a variable with the Table data type as an output parameter on the stored procedure. This could then be read into a Recordset in the adp/ade program. The Is_Member() SQL Server function would then be available to determine the privaleges of the user and make cetain controls visible.

What are your or anyone elses thoughts on this?

For those interested:

Could not find a way to output anything but a Table from an SqlServer to an Access VBA project. So, created a stored procedure that creates and returns a Table with one field and one record. The table only exists in the stored procedure, not the database:


CREATE proc dbo.sprocIsMember
as
declare @vtblUser TABLE (fldIsMember int null)
begin
set nocount on
insert @vtblUser values (is_member('MandMWriter'))
select * from @vtblUser
end
GO


Would have probably been better to pass 'MandMWriter' as a parameter rather than script it in the stored procedure.

The following VBA script (some variables are declared globally) calls the stored procedure:



Public Sub procIsMember()

'************************************************* ********************

'Calls stored procedure and sets variable to show if user has
'write privileges

On Error Resume Next

Set cnnCurrent = CurrentProject.Connection
Set rstRecordset = New Recordset
Set rstRecordset = cnnCurrent.Execute("sprocIsMember")

If rstRecordset!fldIsMember = 1 Then
    blnIsMember = True
    Me.Caption = Me.Caption & " Write"
Else
    blnIsMember = False
    Me.Caption = Me.Caption & " Read"
End If

End Sub



blnIsMember is used elsewhere to make controls visible.