[AccessD] Thanks for the help

John,

<<If anyone has code that they are willing to share that executes a stored
procedure in SQL Server , passing in a parameter, executed from VBA out in
Access I would be most appreciative.>>

  Below is a ADO and a DAO sample of calling of a stored proc.  Also a
little function I've found that helps tremendously in figuring out exactly
what a SP is looking for from the Access point of view.

Jim.

Function GetSecurityGroupsADO() As String

    ' Returns a string of the security groups to which the current user
belongs.
    ' ADO Version for use with custom apps.
    Const RoutineName = "GetSecurityGroupsADO"
    Const Version = "1.0"

    Dim strConnect As String
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter

    On Error GoTo GetSecurityGroupsADO_Error

    Set cnn = New ADODB.Connection
    cnn.Open "DSN=SYS"

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandText = "trvSp_CurrentGroup"
    cmd.CommandType = adCmdStoredProc

    Set prm = cmd.CreateParameter("GroupID", adVarChar, adParamOutput, 1000)

    cmd.Parameters.Append prm

    cmd.Execute

    GetSecurityGroupsADO = cmd.Parameters("GroupID")

GetSecurityGroupsADO_Exit:
    On Error Resume Next

    Set prm = Nothing
    Set cmd = Nothing
    cnn.Close
    Set cnn = Nothing

    Exit Function

GetSecurityGroupsADO_Error:
    UnexpectedError ModuleName, RoutineName, Version, Err.Number,
Err.Description, Err.Source, VBA.Erl
    GetSecurityGroupsADO = ""
    Resume GetSecurityGroupsADO_Exit

End Function
=====================================

Function GetUserSecurityGroupsDAO() As String

    ' Special version of GetUserSecurityGroups for use with Traverse.
    ' NOTE: Does not use normal error handling as this is
    ' for use with Traverse ONLY
    
    Dim qdf As QueryDef
    Dim sql As String
    
    On Error GoTo GetUserSecurityGroupsDAO_Error
    
    sql = "{? = call trvsp_CurrentGroup (?)}"
    Set qdf = gConSQLSys.CreateQueryDef("", sql)
    qdf.Parameters(0).Direction = dbParamReturnValue
    qdf.Parameters("@Groups").Direction = dbParamOutput
    qdf.Execute
    If qdf.Parameters(0) = 0 Then
        sql = qdf.Parameters("@Groups")
    Else
        sql = ""
    End If
    
    GetUserSecurityGroupsDAO = sql

GetUserSecurityGroupsDAO_Exit:
    On Error Resume Next
    
    Set qdf = Nothing
    
    Exit Function
    
GetUserSecurityGroupsDAO_Error:
    GetUserSecurityGroupsDAO = ""
    Resume GetUserSecurityGroupsDAO_Exit
    
End Function

=====================================

Sub GetSPParameters(strSPName As String)

          ' Return the attributes of the parameters of a stored procedure.
          ' ie. from the debug window:
          ' Call GetSPParameters("qrySMGetPeriod")

          Dim strConnect As String
          Dim cnn As ADODB.Connection
          Dim cmd As ADODB.Command
          Dim prm As ADODB.Parameter

10        Set cnn = New ADODB.Connection
20        cnn.Open "DSN=SYS"

30        Set cmd = New ADODB.Command
40        cmd.ActiveConnection = cnn
50        cmd.CommandText = strSPName
60        cmd.CommandType = adCmdStoredProc

70        cmd.Parameters.Refresh
80        For i = 0 To cmd.Parameters.count - 1
90            Debug.Print "Parameter: " & i
100           Debug.Print "     Name: " & cmd.Parameters(i).Name
110           Debug.Print "     Type: " & cmd.Parameters(i).Type
120           Debug.Print "Direction: " & cmd.Parameters(i).Direction
130           Debug.Print "     Size: " & cmd.Parameters(i).Size
140           Debug.Print "   Attrib: " & cmd.Parameters(i).Attributes
150           Debug.Print "    Value: " & cmd.Parameters(i).Value
155           Debug.Print ""
160       Next i
          
170       cnn.Close
180       Set cnn = Nothing
          
End Sub 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, May 07, 2007 10:06 AM
To: 'Access Developers discussion and problem solving';
dba-sqlserver at databaseadvisors.com
Subject: [AccessD] Thanks for the help

<<snip>>