asked on

Executing Stored Procedure with optional parameteres, returning a recordset From MS Access 2013

I am new to SQL Server but have been using MS Access for over 10 years. This is my first application with a SQL backend and I want to take advantage of the SQL abilities, in this case Stored Procedures.

In my front end Access 2013 application I have payment inquiry screen. At the top of the screen are possible filters, Muni (Long), LB (String), Payee(String), From Pay Date(ShortDate), Thru Pay Date(ShortDate).

The user must complete at least one of the filters then press the ‘Apply Filter ‘ button. In prior application I have written, I then construct a Select statement based on the filters filled and use the constructed select as the recordset of the form If multiple filters are selected the individual field selections are combined with an ‘Add’. Here’s some example Access code to build a ‘Where’ condition behind one of these forms.

anyFilter = False
strFilter = " Where "
'
' Build filter
'
' Muni
If Nz(Me.txtMuni_Filter, 0) = 0 Then
Else
    If anyFilter Then
        strFilter = strFilter & " And [Muni] = " & Me.txtMuni_Filter
    Else
        anyFilter = True
        strFilter = strFilter & " [Muni] = " & Me.txtMuni_Filter
    End If
End If
' Lot Block
If Len(Trim(Nz(Me.txtLotBlock_Filter, ""))) = 0 Then
Else
    If anyFilter Then
        strFilter = strFilter & " And [LotBlock] Like " & Chr(34) & "%" & Trim(txtLotBlock_Filter) & "%" & Chr(34)
    Else
        anyFilter = True
        strFilter = strFilter & " [LotBlock] Like " & Chr(34) & "%" & Trim(txtLotBlock_Filter) & "%" & Chr(34)
    End If
End If
'  Name
If Len(Trim(Nz(Me.txtPayee_Filter, ""))) = 0 Then
Else
    If anyFilter Then
        strFilter = strFilter & " And [Payee] Like " & Chr(34) & "%" & Trim(txtPayee_Filter) & "%" & Chr(34)
    Else
        anyFilter = True
        strFilter = strFilter & " [Payee] Like " & Chr(34) & "%" & Trim(txtPayee_Filter) & "%" & Chr(34)
    End If
End If
'
If Len(Trim(Nz(Me.txtFromPayDate_filter, ""))) = 0 Then
Else
    If anyFilter Then
        strFilter = strFilter & " And [PaymentDate] >=  " & Chr(35) & txtFromPayDate_filter & Chr(35)
    Else
        anyFilter = True
        strFilter = strFilter & " [PaymentDate]  >=  " & Chr(35) & txtFromPayDate_filter & Chr(35)
    End If

End If
'
If Len(Trim(Nz(Me.txtThruPayDate_filter, ""))) = 0 Then
Else
    If anyFilter Then
        strFilter = strFilter & " And [PaymentDate] <=  " & Chr(35) & txtThruPayDate_filter & Chr(35)
    Else
        anyFilter = True
        strFilter = strFilter & " [PaymentDate]  <=  " & Chr(35) & txtThruPayDate_filter & Chr(35)
    End If

End If
'
If anyFilter = False Then
    strFilter = ""
Else
    DoAReQuery
End If

Open in new window

In my current version of the Access application I create the ‘where’ string and use it to a select from a linked Sql View I created called ‘dbo_vtblPaymentTaxAuthority’, which contains all of the fields I want to pull back. That select is then used as the new recordsource of the form. It works perfectly but has to pull all of the records in ‘dbo_vtblPaymentTaxAuthority’ back to Access before doing the filtering, which is not the most efficient way of doing this with the SQL backend.

I would like to change the process to transfer selection of the records to SQL Server, returning a recordset of just the selected records to be used as the recordsource of my Access form.

I created a stored procedure that create a filtered set of records based on the parameters passed. The selection is based only on parameters that are filled by the user, the rest are ignored. The SP works fine and I have tested it on the SQL side with varying combinations of parameters and it does the filtering properly. This is the SP:

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[sptblPaymentsPreSelect]    Script Date: 4/30/2017 9:19:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptblPaymentsPreSelect] 
	-- Add the parameters for the stored procedure here
	@MuniCode		int = null, 
	@Payee			nVarchar(30) = null,
	@LB				nVarchar(30) = null, 
	@FromPayDate	dateTime = null,   
	@ThruPayDate	dateTime = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

SELECT [Muni], [LotBlock] , [TieBreaker], [PayHdrID], [PayTaxAuthID], [PaymentSourceID], [TransactionTypeID], [TransactionType], 
       [PaymentDate], [CheckNum] ,[PayTypeID], [FullOrPartialPayID], [PayType], [VoucherNum], [VoucherAlpha], [PayerID], 
	   [Payee], [TaxAuthorityID], [PropertyID], [TaxTypeID], [TaxType], [DepositNum], [DepositDate], [TAReceipt], [TAReceiptSeq],
	    [PayAmt], [PostedStatusID], [PostedStatus], [MasterReceiptNum]
	From dbo.vtblPaymentHeaderAndTA_Inquiry
	WHERE [Muni]         =  ISNULL(@MuniCode , Muni) and 
		  [Payee]        =  ISNULL(@Payee , [Payee]) and 
		  [LotBlock]     =  ISNULL(@LB , [LotBlock]) and 
		  [PaymentDate] >=  ISNULL(@FromPayDate , [PaymentDate]) and
		  [PaymentDate] <=  ISNULL(@ThruPayDate , [PaymentDate]) 


	
END

Open in new window

I would now like to replace the VB code shown at the beginning of the post with a call to the SP, using the returned recordset as the recordsource of the form. I have coded some calls to SP's from Access but none had a variable number of passed parameters and none returned a recordset. This is an example of something I've coded already which hs both input and output fields, not a recordset. Also, all the parameters are required. In my new scenario all of the parameters are not required.

Public Sub getTASummary_SPOutParms(passedTaxAuthorityID As Long, _
                                   Optional passedFromYear As Long = 0, _
                                   Optional passedThruYear As Long)
'
Dim wkTaxAuthorityID As Long
Dim wkFromYear As Long
Dim wkThruYear As Long
'
Dim returnFaceBal As Double
Dim returnPenaltyBal As Double
Dim returnInterestBal As Double
Dim returnLienBal As Double
Dim returnSvcChgBal As Double
'
wkTaxAuthorityID = passedTaxAuthorityID
wkFromYear = passedFromYear
wkThruYear = passedThruYear
'
returnFaceBal = 0
returnPenaltyBal = 0
returnInterestBal = 0
returnLienBal = 0
returnSvcChgBal = 0
'
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
Dim param3 As New ADODB.Parameter

Dim param4 As New ADODB.Parameter
Dim param5 As New ADODB.Parameter
Dim param6 As New ADODB.Parameter
Dim param7 As New ADODB.Parameter
Dim param8 As New ADODB.Parameter

With cmd
    .CommandText = "spTAYearSummary"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    Set param1 = .CreateParameter("passedTaxAuthorityID", adBigInt, adParamInput, , wkTaxAuthorityID)
    .Parameters.Append param1
    '
    Set param2 = .CreateParameter("passedFromYear", adBigInt, adParamInput, , wkFromYear)
    .Parameters.Append param2
    '
    Set param3 = .CreateParameter("passedThruYear", adBigInt, adParamInput, , wkThruYear)
    .Parameters.Append param3
    '
    ' Output Params
    '
    Set param4 = .CreateParameter("returnFaceSum", adDouble, adParamOutput)
    .Parameters.Append param4
    '
    Set param5 = .CreateParameter("returnPenaltySum", adDouble, adParamOutput)
    .Parameters.Append param5
    '
    Set param6 = .CreateParameter("returnInterestSum", adDouble, adParamOutput)
    .Parameters.Append param6
    '
    Set param7 = .CreateParameter("returnLienSum", adDouble, adParamOutput)
    .Parameters.Append param7
    '
    Set param8 = .CreateParameter("returnSvcChgSum", adDouble, adParamOutput)
    .Parameters.Append param8
    
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
    Set param1 = Nothing
    Set param2 = Nothing
    Set param3 = Nothing
End With
'
returnFaceBal = cmd.Parameters("returnFaceSum")
returnPenaltyBal = cmd.Parameters("returnPenaltySum")
returnInterestBal = cmd.Parameters("returnInterestSum")
returnLienBal = cmd.Parameters("returnLienSum")
returnSvcChgBal = cmd.Parameters("returnSvcChgSum")

'MsgBox FormatCurrency(param2.Value, 2)
'MsgBox FormatCurrency(cmd.Parameters("TotalDollar"), 2)

Set param4 = Nothing
Set param5 = Nothing
Set param6 = Nothing
Set param7 = Nothing
Set param8 = Nothing
'
Set cmd = Nothing
End Sub

Open in new window

Does anyone have an example of how I could accomplish my objective. I know it's possible but have no idea how to pull it off.

UnifiedIS

Here is an example to assign the parameter values conditionally. Pass dbnull.value when the parameter wasn't chosen by the user.

Dim cmd As New SqlCommand("EXECUTE Reports.dbo.RR_Schedule_UpdateParameterValue " & _
"@ScheduleID = @pScheduleID" & _
",@ParameterID = @pParameterID" & _
",@Value = @pValue" & _
",@CalculatedValueTypeID = @pCalculatedValueTypeID")

With cmd.Parameters
.AddWithValue("@pScheduleID", ScheduleID)
.AddWithValue("@pParameterID", ParameterID)
If Value Is Nothing Then
.AddWithValue("@pValue", DBNull.Value)
Else
.AddWithValue("@pValue", Value)
End If
.AddWithValue("@pCalculatedValueTypeID", CalculatedValueTypeID)
End With

Dale Fye

Or, you could create a saved query that is a pass-through query (you need to save your connection string in the query properties and set the ReturnsRecords property to Yes) and do something like:

strSQL = "exec sptblPaymentsPreSelect "
strSQL = strSQL & IIF(Nz(Me.txtMuni_Filter, 0) = 0,  "NULL", Me.txtMuni_Filter) & ", "
strSQL = strSQL & IIF(Trim(Nz(Me.txtLotBlock_Filter, ""))="", "NULL", chr(34) & Trim(me.txtLotBlock_Filter & chr(34))) & ", "
strSQL = strSQL & IIF(Trim(Nz(Me.txtPayee_Filter, "")) = "", "NULL", Chr(34) & Trim(me.txtPayee_Filter) & Chr(34)) & ", "
strSQL = strSQL & IIF(Trim(Nz(Me.txtFromPayDate_filter, "")) = "", "NULL", Chr(35) & Format(me.txtFromPayDate_filter, "yyyy-mm-dd") & Chr(35))
strSQL = strSQL & IIF(Trim(Nz(Me.txtThruPayDate_filter, "")) = "", "NULL", Chr(35) & Format(txtThruPayDate_filter, "yyyy-mm-dd") & Chr(35))
debug.print strSQL
Currentdb.YourPassThroughQueryName.SQL = strSQL

Open in new window

This should store the results, which would look something like:

exec sptblPaymentsPreSelect NULL, 'MuniFilterValue', NULL, '2017-01-01', NULL

into the SQL property of your query and then you could set the RecordSource of the form to the query name and simply requery the form.

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks for the responses. I am not ignoring the question. I got pulled into a client emergency situation and have not had time to review these yet. I should be able to soon.

mlcktmguy

ASKER

Christian,
I am starting simple on this. NO passed parameters to the routine that executes the SP. For the time being I've hard coded them in the routine 'getPaymentPreRecordset', which looks like this:

Public Sub getPaymentPreRecordset(ByRef frm As Form)
'
Dim passedMuniCode As Long
Dim passedPayee As String
Dim passedLotBlock As String
Dim passedFromPayDate As Date
Dim passedThruPayDate As Date
'
passedMuniCode = 810
'

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "sptblPaymentsPreSelect"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    .Parameters.Append .CreateParameter("passedMuniCode", adBigInt, adParamInput, , passedMuniCode)
    '
    .Parameters.Append .CreateParameter("passedPayee", adVarChar, adParamInput, 30, passedPayee)
    '
    .Parameters.Append .CreateParameter("passedLotBlock", adVarChar, adParamInput, 30, passedLotBlock)
    '
    .Parameters.Append .CreateParameter("passedFromPayDate", adDBTime, adParamInput, , passedFromPayDate)
    '
    .Parameters.Append .CreateParameter("passedThruPayDate", adDBTime, adParamInput, , passedThruPayDate)
    
    Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
    
    
    
    ' You could now assign it to a form
    Set frm.Recordset = rs
    Set .ActiveConnection = Nothing
End With
'
Set cmd = Nothing
End Sub

Open in new window

As my first step I revised my form to call this routine 'getPaymentPreRecordset Me', trying to get it to update the recordset of the form

I am getting a "runtime error -2147217887 [Microsoft][ODBC SQL Server Driver], Optional featrues not implemented." ] on '.Open cmd' within:

        With rs
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With

Open in new window

Any ideas?

Also since returned recordset is not updateable. Which .CursorType and .LockType would be best?

SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks you. I tried aDBTime, aDBDate, adDBTimeStamp, adDate without luck. Not sure if it's correct but 'adDBTimeStamp' got past that compile error and got me further.

Now I'm getting an error "Object does not support automation or does not support expected interface"

Set frm.Recordset = rs

Any ideas?

Bitsqueezer

Hi,

did you try to set a breakpoint and look into the created recordset?
Is "frm" a valid reference to an existing and opened form?

The "Locals" window in VBA is your friend...:-)

Cheers,

Christian

mlcktmguy

ASKER

Thanks for your follow up Christian.

Worked with this all morning and I'm still getting the same error. Here's what I did.

1. I modified the routine, putting a loop to count returned records in prior to trying to set the frm.recordset

Public Sub getPaymentPreRecordset(ByRef frm As Form, _
                                  Optional passedMuniCode As Variant = Null, _
                                  Optional passedPayee As Variant = Null, _
                                  Optional passedLotBlock As Variant = Null, _
                                  Optional passedFromPayDate As Variant = Null, _
                                  Optional passedThruPayDate As Variant = Null)

'
'
gConnection = "ODBC;Description=JTS Data;DRIVER=SQL Server;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'gConnection = "ODBC;Description=JTS Data;DRIVER=SQLNCLI;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "sptblPaymentsPreSelect"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    .Parameters.Append .CreateParameter("passedMuniCode", adBigInt, adParamInput, , passedMuniCode)
    '
    .Parameters.Append .CreateParameter("passedPayee", adVarChar, adParamInput, 30, passedPayee)
    '
    .Parameters.Append .CreateParameter("passedLotBlock", adVarChar, adParamInput, 30, passedLotBlock)
    '
    .Parameters.Append .CreateParameter("passedFromPayDate", adDBTimeStamp, adParamInput, , passedFromPayDate) 'aDBTime, aDBDate, adDBTimeStamp, adDate
    '
    .Parameters.Append .CreateParameter("passedThruPayDate", adDBTimeStamp, adParamInput, , passedThruPayDate)
    
    Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '///////////////////////////////////// Just to Check Results, Remove Later /////////////////////
        '        Dim wkRecCnt As Long
        '        If rs.EOF Then
        '            MsgBox "No Records Returned"
        '        Else
        '          '  If rs.RecordCount > 0 Then
        '                '
        '                wkRecCnt = 0
        '                '
        '                While Not rs.EOF
        '
        '                    wkRecCnt = wkRecCnt + 1
        '
        '                  '  rs![DateRevised] = Now
        '                  '  rs![UserRevised] = GimmeUserName
        '                  '  '
        '                  '  rs.Update
        '
        '                    '
        '                    rs.MoveNext
        '                Wend
        '                '
        '            MsgBox "Number Of Recs Returned: " & Trim(Str(wkRecCnt))
        '
        '          '  End If    'recordcount >0
        '        End If
        '
        '        '
        '        rs.Close
        '        Set rs = Nothing
        '///////////////////////////////////// Just to Check Results, Remove Later /////////////////////
        
    
    
    
    ' You could now assign it to a form
    Set frm.Recordset = rs
   
    Set .ActiveConnection = Nothing
End With
'
Set cmd = Nothing
End Sub

Open in new window

The loop showed that I always got records back matching the criterion that I passed.

When I commented the loop count routine and let it fall to 'Set frm.Recordset = rs', I got the same Automation error.

I checked the 'Locals' and they all looked good.

2. I put the lookup routine directly in the form that was previously calling it. I once again verified that the SPROC was returning the number of records it should. Since the routine was in the form I changed to 'Set Me.Recordset = rs' and got the same Automation error.

Private Sub DoaSPROCRequery(Optional passedMuniCode As Variant = Null, _
                            Optional passedPayee As Variant = Null, _
                            Optional passedLotBlock As Variant = Null, _
                            Optional passedFromPayDate As Variant = Null, _
                            Optional passedThruPayDate As Variant = Null)

'
'
gConnection = "ODBC;Description=JTS Data;DRIVER=SQL Server;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'gConnection = "ODBC;Description=JTS Data;DRIVER=SQLNCLI;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "sptblPaymentsPreSelect"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    .Parameters.Append .CreateParameter("passedMuniCode", adBigInt, adParamInput, , passedMuniCode)
    '
    .Parameters.Append .CreateParameter("passedPayee", adVarChar, adParamInput, 30, passedPayee)
    '
    .Parameters.Append .CreateParameter("passedLotBlock", adVarChar, adParamInput, 30, passedLotBlock)
    '
    .Parameters.Append .CreateParameter("passedFromPayDate", adDBTimeStamp, adParamInput, , passedFromPayDate) 'aDBTime, aDBDate, adDBTimeStamp, adDate
    '
    .Parameters.Append .CreateParameter("passedThruPayDate", adDBTimeStamp, adParamInput, , passedThruPayDate)
    
    Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        ''///////////////////////////////////// Just to Check Results, Remove Later /////////////////////
        '        Dim wkRecCnt As Long
        '        If rs.EOF Then
        '            MsgBox "No Records Returned"
        '        Else
        '          '  If rs.RecordCount > 0 Then
        '                '
        '                wkRecCnt = 0
        '                '
        '                While Not rs.EOF
        '
        '                    wkRecCnt = wkRecCnt + 1
        '
        '                  '  rs![DateRevised] = Now
        '                  '  rs![UserRevised] = GimmeUserName
        '                  '  '
        '                  '  rs.Update
        '
        '                     '
        '                    rs.MoveNext
        '                Wend
        '                '
        '            MsgBox "Number Of Recs Returned: " & Trim(Str(wkRecCnt))
        '
        '          '  End If    'recordcount >0
        '        End If
        '
        '        '
        '        rs.Close
        '        Set rs = Nothing
        ''///////////////////////////////////// Just to Check Results, Remove Later /////////////////////
        
    
    
    
    ' You could now assign it to a form
   ' Set frm.Recordset = rs
    Set Me.Recordset = rs
   
    Set .ActiveConnection = Nothing
End With
'
Set cmd = Nothing
End Sub

Open in new window

I got the error 'Class does not support Automation or does not support expected interface'.

It seems really close to working but I haven't been able to successfully set the returned recordset as the recordsource of the form. Doesn't seem to matter if I try to do it in an external routine, passing the form by reference, or by executing right in the form VBA.

Any ideas out there?

Bitsqueezer

Hi,

strange error. Seems as if your reference to ADO is not correct. Beginning with Windows Vista you need to use ADO 6.x, Windows XP and lower ADO 2.8 as maximum.

Cheers,

Christian

mlcktmguy

ASKER

I believe I am using 6.1. Here are my references:

mlcktmguy

ASKER

Christian, I re-read my original question and realized you have completely answered the original question about returning passing optional parameters and returning the results as a recordset.

I am going to post another question about set the recordsource of a query using the recordset returned by calling a stored procedure.