Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

MS Access Pasthru Query to Execute SQL Stored procedure

I developing my first access application with a SQL Server back end. I've not used SQL before and I'm exploring the functionalities that come with the SQL backend.  With help from EE I developed a VBA & ADO approach to execute SQL Stored Procedures from Access.

I have also created some simple pass thru queries in Access (Select field1, field2.. etc.) to retrieve information from SQL.  I have read that it's also possible to execute stored procedures from a pass thru query but don't know how to do that, or if its any better/more efficient than executing them in VBA using ADO.

Is it possible to create a pass thru query that executes a stored procedure that returns values?  How would that look?

What would a pass thru query to perform the same functionality as my VBA/ADO routine below look like?  This one only passed parameters to a stored procedure that creates a record in a SQL Table.

Public Sub writeComment_Sproc(passedPropertyID As Long, _
                              passedComment As String, _
                     Optional passedTaxAuthorityID As Long = 0, _
                     Optional passedTaxTypeID As Long = 0, _
                     Optional passedUserName As String = "", _
                     Optional passedCommentTypeID As Long = 1, _
                     Optional passedDate As Variant = Null)
'
wkDateTime = Now
wkUser = GimmeUserName

If Len(Trim(passedUserName)) > 0 Then
    wkUser = passedUserName
Else
    wkUser = GimmeUserName
End If
'
If IsValidSQLDate(passedDate) Then
    wkDateTime = passedDate
End If

'
Dim adCn As ADODB.Connection
Dim adCmd As ADODB.Command
'Dim adRs As ADODB.Recordset
Dim sConn As String

Const spName = "dbo.sptblCommentsAdd"

setSQLConnection
sConn = gConnection
'
Set adCn = New ADODB.Connection
adCn.Open sConn

Set adCmd = New ADODB.Command
adCmd.ActiveConnection = adCn
adCmd.CommandText = spName
adCmd.CommandType = adCmdStoredProc
adCmd.Parameters.Refresh
adCmd.Parameters(1).Value = passedPropertyID
adCmd.Parameters(2).Value = passedComment
adCmd.Parameters(3).Value = passedTaxAuthorityID
adCmd.Parameters(4).Value = passedTaxTypeID
adCmd.Parameters(5).Value = passedCommentTypeID
adCmd.Parameters(6).Value = wkUser
adCmd.Parameters(7).Value = wkDateTime

adCmd.Execute Options:=adExecuteNoRecords

'
'adRs.Close     ' no recordset on this one
adCn.Close
'
'Set adRs = Nothing
Set adCmd = Nothing
Set adCn = Nothing
'
End Sub
'/////////////////////////////////////////////// Stored Procedure

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[sptblCommentsAdd]    Script Date: 4/19/2017 2:13:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sptblCommentsAdd]
      @PropertyID             int,
    @Comment                        nvarchar(MAX) = '',
      @TaxAuthorityID                  int = 0,
      @TaxTypeID                      int = 0,
    @CommentTypeID                  int = 0,
    @UserAdded                        nvarchar(100) = '',
    @DateAdded                        datetime = null
As

Begin

Set NOCOUNT on;

SET @DateAdded = COALESCE(@DateAdded, GETDATE())

Insert dbo.tblComments (
    PropertyRecID,
      TaxAuthorityID,
      TaxTypeID,
      CommentTypeID,
      Comment,
      DateAdded,
      UserAdded)
      Values (
      @PropertyID,
      @TaxAuthorityID,
      @TaxTypeID,
      @CommentTypeID,
      @Comment,
      ISNULL(@DateAdded, GETDATE()),
      @UserAdded)


END
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

RE: "DO approach to execute SQL Stored Procedures from Access."
FYI ... no need for ADO  .... LOTS of misleading info about that.
VBA/DAO works just fine. I know because I am currently going through this.

"I have read that it's also possible to execute stored procedures from a pass thru query"
That is exactly what I am doing.  And it way less code that ADO.

"Is it possible to create a pass thru query that executes a stored procedure that returns values?  "
I'm sure it is ... haven't done that ... yet :-)
Avatar of mlcktmguy

ASKER

DatabaseMX: Thank you, do you have any examples of you pass thru queries calling a stored procedure?
Yes ... you are in luck. I will post in a bit ...
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, at this point I don't exactly understand it but I'll try to figure it out and get back to you.
Then don't EVEN think about using ADO lol !!
I figured all that out over the last week or so ... and there is a lot more to the story ... regrading 'permissions' required to run SPs and so on.
Here is a very tiny sample.  You are in for some serious Google research ... just like I'm doing.

https://msdn.microsoft.com/en-us/library/bb669058(v=vs.110).aspx

And don't believe everything you read ...  and note that many other sites have better examples than Microsoft :-)
Thanks very much.  I was to use the code you posted (modified for my specifics) to execute the pass thru passing parameters.
I'm going to post some followup questions about this.