troubleshooting Question

MS Access Pasthru Query to Execute SQL Stored procedure

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
8 Comments1 Solution251 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Database Architect / Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2007

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros