[AccessD] Execute stored procedures from Access

  BTW, if you ever want to know what parameters a store procedure is looking
for, you can use the procedure below.

Jim.


Sub GetSPParameters(strSPName As String)

          ' Return the attributes of the parameters of a stored procedure.
          ' 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: Wednesday, January 21, 2009 11:47 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Execute stored procedures from Access

Does anyone have commented code to execute SQL Server stored 
procedures with parameters from Access code?

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com