jwcolby
jwcolby at colbyconsulting.com
Sun Jun 19 07:05:26 CDT 2011
Thanks Darrell, I am collecting this stuff now, as I am starting to get involved in it.
John W. Colby
www.ColbyConsulting.com
On 6/18/2011 10:40 PM, Darrell Burns wrote:
> 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?
>