We help IT Professionals succeed at work.
Get Started

MS Access Pasthru Query to Execute SQL Stored procedure

250 Views
Last Modified: 2017-04-21
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
Comment
Watch Question
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE