Parameters in pass-through query

[roberto21]

I am trying to pass parameters to a stored proc on sql server via a pass-through query.
The stored procedure is defined on sql server with two parameters, @startDate and @endDate ,both of DATE type.


The code to invoke the query with the parameters is this, where Query4 is the pass-through query and spStoredProc is the stored procedure

  Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim qdf As DAO.QueryDef
     ...
     Dim data1 As Date, data2 As Date
     
    strFilePath = "C:\TestHyp4"
    
    Set dbs = DBEngine.OpenDatabase(strFilePath & "\nuovodbricevute.accdb")


    dbs.QueryDefs.Refresh


    Set qdf = dbs.QueryDefs("Query4")


    data1 = #12/31/2023#
    data2 = #1/1/2025#


    
    qdf.Sql = "exec spStoredProc @startDate='" & data1 & "', @endDate='" & data2 & "'"
    Set rst = qdf.OpenRecordset("Query4")

No matter how hard I tried, I could not go beyond the openrecordset step, that fails invariably with runtime error 3421: data type conversion error. By the way, this error seems to come from MS Visual Basic, I was convinced that the contents of the query got passed "as is" to the server.
I tried a lot of variations for passing the parameters, to no avail. Is there something obvious that I am missing? Thank you.

[June7]

Review Albert Kallal's answer in https://stackoverflow.com/questions/...-to-sql-server

Note the structure of date values.

[Welshgasman]

Perhaps review this thread?
https://www.accessforums.net/showthr...104#post532104

[roberto21]

Code modified as per suggestion (but I believe I had already tried that)

     Dim dbs As DAO.Database
     Dim rst As DAO.Recordset
     Dim qdf As DAO.QueryDef


     Dim data1 As Date, data2 As Date
     Dim strStartDate As String, strEndDate As String
    
    strFilePath = "C:\TestHyp4"
    
    Set dbs = DBEngine.OpenDatabase(strFilePath & "\nuovodbricevute.accdb")
   
    dbs.QueryDefs.Refresh
    Set qdf = dbs.QueryDefs("Query4")
    
    data1 = #12/31/2023#
    data2 = #1/1/2025#
  
    strStartDate = "'" & Format(data1, "yyyy-mm-dd") & "'"
    strEndDate = "'" & Format(data2, "yyyy-mm-dd") & "'"
    
    
    qdf.Sql = "exec spStoredProc " & strStartDate & "," & strEndDate
 
    Set rst = qdf.OpenRecordset("Query4")

No difference, same error. What
What I find strange is this. Using the code of my previous post, I end up with a value for the qdf.sql string (checked with the debugger):

?qdf.SQL
exec spStoredProc @data1='2023/12/31', @data2='2025/01/01'

that looks good, and is absolutely identical to the "dummy" contents of Query4 (the pass-through query, this contents gets replaced by the qdf,SQL instruction). Now, If I execute Query4 directly with the "dummy" contents, it works OK; if I execute the same query with (apparently) the same sql with my code, it fails with that error. Again this error comes from Visual basic, this is another mystery. Thank you for your interest.

[Minty]

Why are you opening a connection to a different database?
I use a routine to reuse a generic pass through query as below. Note that the table referenced must have the same connection string as the ODBC SQL database.

Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean)
    
    '   Description: A generic route to reuse a passthrough query for multiple purposes.
   '   The returns records flag you set dependant on whether is it an action or a select result.
    '   It requires qPT_Generic as a passthrough query to be set up.
    '   It also relies on glStrUserTable being set to determine a backend table name used to generate the connection string
    '   You can replace that with a name of any SQL backend table e.g. "tbl_MyLinkedTable" 
    
    Dim db As DAO.Database
    Dim qDef As QueryDef
    
    Set db = CurrentDb()
    
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs(glStrUserTable).Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
    
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close
    End If
    
    Set qDef = Nothing
    Set db = Nothing


End Sub

To use it, you would simply do

 
    strStartDate = "'" & Format(data1, "yyyy-mm-dd") & "'"    
    strEndDate = "'" & Format(data2, "yyyy-mm-dd") & "'"

    strSQL =     "exec spStoredProc " & strStartDate & "," & strEndDate

    sSendToPT_Generic strSQL, True
    
    Set rst = qdf.OpenRecordset("qPT_Generic")

I suspect your problem might be because you aren't closing the qdf object, and hence not saving the updated SQL statement.

[roberto21]

Closing qds did not change the error. Maybe the problem is that my code is excel vba, and what I am trying is simply not possible in this case, and I have to address the stored procedure directly, without passing through an access query pass-through (that's why I am opening the accdb database linked to the sql backend database, if this is what you are referring to).
Sorry if everything sounds so confused, I am still trying to learn.

[Minty]

It might have shed some light on your issues if you had mentioned running this from Excel beforehand. I don't think Excel can use a pass through query in that fashion.
In fact I'm quite surprised it allows you to manipulate the query object without complaining as I'm not sure it's a native function (the Query Object) of Excel.
TBH I had never even thought of doing things that way around. Happy to be proved wrong.

What happens if you run it from Access? If it all works I suspect you have your answer.

[Micron]

VBA written in Excel can use Access objects and vice versa. All you need is the appropriate reference(s). At one time, doing so was called Automation, but I think it might go by a different name now.

[June7]

Why is Excel involved?

My first use of AI generated code and it worked - virtually copy/paste/run (just had to change file path string)! Gave me Excel VBA procedure to get properties of Access TableDefs and QueryDefs. So, incorporating Albert Kallal suggestion, give this version a try:

Dim strFilePath As String
Dim conn As Object
Dim dbs As Object
Dim rst As Object
Dim qdf As Object

' Path to Access database
strFilePath = "C:\TestHyp4"
' Create a new ADODB connection
Set conn = CreateObject("ADODB.Connection")
' Open connection to Access database
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath & "\nuovodbricevute.accdb"
' Create a new DAO database object
Set dbs = CreateObject("DAO.DBEngine.120").OpenDatabase(strFilePath & "\nuovodbricevute.accdb")

Set qdf = dbs.QueryDefs("Query4")
qdf.sql = "exec spStoredProc '2023-12-31', '2025-1-1'"
Set rst = dbs.OpenRecordset("Query4")
Debug.Print rst(0)

[madpiet]

Pin the tail on the donkey is fun and all, but if you're just trying to return data to Excel from SQL Server parameterized stored procedure that returns records, why not just use PowerQuery?

https://excelmaverick.com/passing-pa...n-power-query/

Or you can watch a complete walk through here:
https://youtu.be/D1ax1WlkU_Y?si=nP02glDaDzAFRhOq

The second one is pretty close to what you're trying to do. There are a few gotchas in there... like creating the parameter tables and then drilling down so that the one column, one row table is converted into a value.

I think this one is the winner so far... I like this tutorial a lot. A bit longer, but he outlines all the steps. More of a hassle than setting up Access to do this, but you can do the whole thing in Excel. https://youtu.be/-ibpZWeqzTI?si=uqNCrx9NH9gjqIvI

[roberto21]

Thank you very much for your interest and help, there is a lot to be digested here for an amateur...

The code suggested by June7 fails with a runtime error 3146, odbc call failed, at the set rst= dbs.openrecordset("query4"). I can add that,if you have to make direct reference to the stored procedure by name, you can use this code bypassing Access completely

Dim cnn As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim rs As ADODB.Recordset
Dim data1 As Date, data2 As Date
Dim sQry As String, dim strFilePath as String


strFilePath = "C:\TestHyp4"


Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
'cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strFilePath & "\nuovodbricevute.accdb;"
cnn.Open "DSN=driver18NuovoDB32"  'connection dsn to sql server database


' Create Command Object.
Set Cmd1 = New ADODB.Command
Cmd1.CommandType = adCmdStoredProc
Cmd1.ActiveConnection = cnn
Cmd1.CommandText = "dbo.spPrimaNotaAP"  ' stored procedure name


data1 = #12/31/2023#
data2 = #1/1/2025#


' Create Parameter Object.
'Add parameters to stored proc
  Cmd1.Parameters.Append Cmd1.CreateParameter("data1", adDBDate, adParamInput, 0, Format(data1, "YYYY/MM/DD"))
  Cmd1.Parameters.Append Cmd1.CreateParameter("data2", adDBDate, adParamInput, 0, Format(data2, "YYYY/MM/DD"))




' Open Recordset Object.
rs.Open Cmd1


Set Cmd1 = Nothing
Set cnn = Nothing

This code works ok and does not use Access pass through query.
I will continue experimenting. Again thank you all

[June7]

Interesting, because the code I found works for me and possibly would for you if you used your stored procedure name instead of spStoredProc.

However, glad you have a solution, which is certainly better.

[roberto21]

You are right, of course. I am awfully sorry, I forgot to update then stored procedure name.
Code is better? I doubt it. I am just experimenting...