Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 24th, 2007, 11:07 AM
Authorized User
Join Date: May 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 1 Time in 1 Post
Default Get Stored Procedure Output using Access VBA

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

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


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
Reply With Quote
  #2 (permalink)  
Old May 25th, 2007, 06:43 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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?

Reply With Quote
  #3 (permalink)  
Old May 25th, 2007, 07:54 AM
Authorized User
Join Date: May 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 1 Time in 1 Post


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?

Reply With Quote
  #4 (permalink)  
Old June 20th, 2007, 11:20 AM
Authorized User
Join Date: May 2007
Location: , , .
Posts: 11
Thanks: 0
Thanked 1 Time in 1 Post

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
declare @vtblUser TABLE (fldIsMember int null)
set nocount on
insert @vtblUser values (is_member('MandMWriter'))
select * from @vtblUser

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"
    blnIsMember = False
    Me.Caption = Me.Caption & " Read"
End If

End Sub

blnIsMember is used elsewhere to make controls visible.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored procedure and VBA jesseleon Access VBA 7 September 15th, 2011 12:46 PM
XML Output from stored procedure NigelBronson ASP.NET 2.0 Basics 0 October 15th, 2006 06:24 PM
Running stored procedure in Access VBA Bryon Burbage Access VBA 0 July 21st, 2006 09:14 AM
output parameter from MS SQL stored procedure nav1 VB How-To 0 March 14th, 2005 05:00 PM
Calling stored procedure with output parameters bansalh SQL Server 2000 2 November 10th, 2004 11:00 AM

All times are GMT -4. The time now is 11:30 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.