Darrell Burns
dhb at flsi.com
Sat Jun 18 21:40:50 CDT 2011
John,
Not sure if this is more than what you're looking for, but I created a
function that executes SQL procs thru ADO. Based on parameters passed to the
function (called ExecuteSQL), it can retrieve a return value or recordset.
HTH,
Darrell
****************************************************************************
***************
There's a table that goes along with this code called SQL_Procs, which
contains the rules for each stored procedure that will be called.
It has these fields:
sp_Seq (int), sp_Name (text), sp_Args(text), sp_ReturnStatus (bit),
sp_ReturnsRecordset (bit), sp_Output_Table (text - optional)
****************************************************************************
**************
Public Function ExecuteSQL(spName As String, Optional InputVals As Variant,
Optional SQLdatabase As String) As Variant
' Run a SQL Server pass-through function or stored procedure
' Prefix stored procedure names with 'sp' and functions with 'fn'
' Returns a value or status as specified in SQLProcs. If status, returns
true or false.
' Example call string:
' SQLOutParm = "@InsertOK OUT" .....optional. Must be prefixed with
@ and suffixed with OUT
' qryName = "sp_Insert_Collection"
' qryParms = NewEntry & ", " & Scope & ", " & CollRank & ", " & ID &
", " & NewParentID & ", " & AddLevel & ", " & SQLOutParm
' If ExecuteSQL(qryName, qryParms) = "Return_Error" Then
' Status_Msg "ERROR: Couldn't execute stored procedure " + qryName,
stMsgForm
' Exit Sub
' End If
Dim InputString As String, arParse() As String, SQLOutParm As String,
Word As String, SQLParmNames() As String, spArgs As String
Dim ParseVal As Variant, retval As Variant
Dim NextID As Long
Dim n As Integer, numArgs As Integer, ReturnStatus As Integer,
ReturnsStatus As Integer, ReturnsRecordset As Integer, isFunction As Integer
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.command
isFunction = False
ReturnsStatus = False
ReturnsRecordset = False
If SQLdatabase = "" Then
DSN = "Master"
SQLDB = "Master"
Else
DSN = SQLDB
SQLDB = SQLdatabase
End If
ConnectString = "ODBC;DRIVER=SQL Server Native Client
10.0;SERVER=T61XP;UID=Darrell;PWD=;Trusted_Connection=Yes;" & _
"DSN=" + DSN + ";Description=SQL Master
database;UID=Darrell;Trusted_Connection=Yes;DATABASE=" + SQLDB + ";"
arSQLReturns(1) = ConnectString
ReturnsStatus = DLookup("sp_ReturnStatus", "SQL_Procs", "sp_Name = '" +
spName + "'")
ReturnsRecordset = DLookup("sp_ReturnsRecordset", "SQL_Procs", "sp_Name
= '" + spName + "'")
If Left(spName, 2) = "fn" Then '?????how do I set a return parameter for
a function????
isFunction = True
Dim ADOParmsReturnValue As New ADODB.parameter
Set ADOParmsReturnValue = cmd.CreateParameter(, adVarChar,
adParamReturnValue, 200)
cmd.Parameters.Append ADOParmsReturnValue
ReturnsStatus = False
End If
ExecuteSQL = "Error" 'default return value
cnn.Open ConnectString
cmd.ActiveConnection = cnn
cmd.CommandText = spName
cmd.CommandType = adCmdStoredProc
retval = DLookup("sp_Name", "SQL_Procs", "sp_Name = '" + spName + "'")
If IsNull(retval) Then
MsgBox "No SQL_Proc entry has been defined for stored procedure " +
spName, vbCritical, "ExecuteSQL"
Exit Function
End If
'If this is a stored proc and there is a ReturnStatus, it has to be the
first parameter. Result will be returned later in cmd.Parameters(0)!
Dim ADOParmsReturnStatus As New ADODB.parameter
If ReturnsStatus Then
Set ADOParmsReturnStatus = cmd.CreateParameter(, adInteger,
adParamReturnValue)
cmd.Parameters.Append ADOParmsReturnStatus
End If
'Get the sproc parameters from the SQL_Procs table...
retval = DLookup("sp_Args", "SQL_Procs", "sp_Name = '" + spName + "'")
If IsNull(retval) Then
GoTo ExecSproc 'if proc has no arguments, ready to execute
End If
'This section executed only if there are args...
spArgs = retval 'spArgs are the specified argument names from SQL_Procs
numArgs = 0
arParse() = Split(spArgs, ",") 'args are delimited by commas
For Each ParseVal In arParse 'parse out the args
numArgs = numArgs + 1
ReDim Preserve SQLParmNames(numArgs)
SQLParmNames(n) = IIf(Left(ParseVal, 1) = "@", "", "@") + ParseVal
Next ParseVal
'Error if args were specified and not supplied...
If IsMissing(InputVals) Then
MsgBox "ERROR: " + Trim(str(numArgs)) + " arguments were specified,
and none were supplied.", vbCritical, "ExecuteSQL"
Exit Function
End If
'Now parse out InputVals and make sure we have an input arg for each
specified arg...
If Not IsNull(InputVals) Then
InputString = InputVals
arParse() = Split(InputString, ",")
n = 0
For Each ParseVal In arParse
n = n + 1
Next ParseVal
If n <> numArgs Then
MsgBox "ERROR: " & numArgs & " arguments were specified, and " &
n & " were supplied.", vbCritical, "ExecuteSQL"
Exit Function
End If
'The number of args matches up; now insert them into an ADO
parameter object...
For Each ParseVal In arParse
If IsNumeric(ParseVal) Then
Word = Trim(str(ParseVal))
Else
Word = Trim(ParseVal)
End If
Dim ADOParms As New ADODB.parameter 'define the ADO parameter
object
If Left(Word, 1) <> "@" Then '@ indicates an output variable
Set ADOParms = cmd.CreateParameter(SQLParmNames(n),
adVarChar, adParamInput, 200, Word) 'OK to pass integer values as nvarchar
cmd.Parameters.Append ADOParms
Else
If InStr(Word, "OUT") = 0 Then
SQLOutParm = Word
Else
SQLOutParm = Trim(Left(Word, InStr(Word, "OUT") - 1))
End If
Set ADOParms = cmd.CreateParameter(SQLOutParm, adVarChar,
adParamOutput, 200, SQLOutParm)
cmd.Parameters.Append ADOParms
End If
If n = 1 Then
arSQLReturns(2) = "ADO parameters: " & ADOParms
Else
arSQLReturns(2) = arSQLReturns(2) & "; " + ADOParms
End If
Next ParseVal
End If
'Ready to pass it thru to SQLServer...
ExecSproc:
arSQLReturns(3) = Null 'default
arSQLReturns(4) = Null 'default
If ReturnsRecordset Then
'Save Recordset Results in the array arSQLOutput...
Dim fldloop As ADODB.Field
Set rSQLOutput = cmd.Execute '******here's where the stored proc is
executed and returns the results in a recordset******
If rSQLOutput.RecordCount <> 0 Then
Dim iRow As Integer, iCol As Integer
iRow = 0
While Not rSQLOutput.EOF
iRow = iRow + 1
For Each fldloop In rSQLOutput.Fields 'load results into the
array
ReDim Preserve arSQLOutput(iCol)
arSQLOutput(iCol) = rSQLOutput.Fields(iCol)
' Debug.Print "OUT " & iCol & " = " & arSQLOutput(iCol)
iCol = iCol + 1
Next fldloop
iCol = 0
rSQLOutput.MoveNext
Wend
End If
rSQLOutput.Close 'Need to close recordset before getting return
parameter
Else
cmd.Execute
End If
ResultsSP:
If SQLOutParm > "" Then
arSQLReturns(3) = cmd.Parameters(SQLOutParm).Value 'RetVal
' Debug.Print "OUT = " & arSQLReturns(3)
End If
If isFunction Then
arSQLReturns(3) = cmd.Parameters(0)
' Debug.Print "ReturnStatus = " & arSQLReturns(4)
End If
If ReturnsStatus Then
arSQLReturns(4) = cmd.Parameters(0)
' Debug.Print "ReturnStatus = " & arSQLReturns(4)
If IsNull(arSQLReturns(3)) Then
arSQLReturns(3) = arSQLReturns(4)
End If
End If
ExecuteSQL = arSQLReturns(3)
cnn.Close
Set cmd = Nothing
Set cnn = Nothing
End Function
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, June 17, 2011 7:51 PM
To: Access Developers discussion and problem solving; Sqlserver-Dba
Subject: [AccessD] Execute permission denied
I am trying to figure out how to use stored procedures to return recordsets
to Access. I created a
stored procedure that accepts a parameter, the stored procedure pulls a
recordset and if used like so:
exec usp_MySP 1
returns a recordset to the query window in SQL Server.
So, now how to get it to work in Access. I thought I was going to create a
pass through query
dynamically, save it and then "open" the query to get the recordset.
The query is a passthrough query
usp_InmatesForVolunteers 1
The ODBC connect string is:
ODBC;DRIVER=SQL
Server;UID=MyUser;PWD=MyPassword;SERVER=5.58.170.179;DATABASE=InmateCheckout
;
All of this (except for the database part) comes directly out of a DSN file
which works to link
tables and views into Access. Tested and working for that purpose.
So the passthrough query fails. Error:
[Microsoft][ODBC Sql Server Driver][SQL Server]The execute permission was
denied on the object
'usp_InmatesForVolunteers', database 'InmateCheckout', schema 'dbo'. (#229)
Interestingly if I remove the database part of the connection string it
returns exactly the same
error message, even mentioning that database. Thus the user itself being
mapped to that database
seems to be working as well.
As I said, this all works for linking SQL Server tables and views, just not
executing the sp.
Any ideas?
--
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com