Dynamic Parameters on Stored Procedures in MS Access query (1 Viewer)

[jco23]

I read that we cannot reference a field from a form to enter a parameter within a pass-through query that references a stored procedure.

However, could one of those parameters be a dynamic date?

For example, below is what I have

{call dbo.RPT_storedproc1 (
'code1;code2',
NULL,
NULL,
'1234;9999;8888',
'2022-12-31',
'2023-01-23',
1,
1,
1,
NULL,
NULL,
'A',
'DATE',
'SYSTEM')}

this logic works fine, but I'd like to have the dates be dynamic rather than hardcoded.

I've tried replacing '2022-12-31' with, dateadd(month,-1,convert(date,getdate())), but then I get the error, "Invalid character value for cast specification". I get the same error even when I use just getdate().

any help would be greatly appreciated.

thanks!

 

[theDBguy]

You may have to use a QueryDef object and modify the date using VBA.

 

[Pat Hartman]

You need to end up with the calculated date enclosed in single quotes.

 

[Minty]

I use a generic pass-through routine for this type of process. I have a saved query (qpt_Generic) that I modify the sql for, with a switch for returning records or not.

Simply create your SQL statement as a string and pass it to it.

Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean)  
    Dim db As Database
    Dim qDef As QueryDef
   
    Set db = CurrentDb()
   
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("ChangeThisToALinkedTableNameInYouDB").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
   
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close  'db.Execute "qPT_Generic", dbOpenSnapshot
    End If
   
    Set qDef = Nothing
    Set db = Nothing

End Sub

To use it simply call it from any routine something like

    strSQL = "EXEC dbo.sp_zAdmin_CopyAllData @dNewDate = " & SERVERDate(Me.txtDestinationDate) & ", @dOldDate =" & SERVERDate(Me.txtSourceDate) & ","
    strSQL = strSQL & " @sNetworkList = '" & strWhere & "', @iMovedBy = " & glEmpID & ", @dMoved = " & SERVERDate(Now())
      
    sSendToPT_Generic strSQL, False

I use named parameters for ease of troubleshooting, and I have a function SERVERDate() that formats Access dates correctly for SQL server.

This routine runs a stored procedure that bulk copies data (and all the child records) from one date to another, therefore it's not returning results.
You can equally use it to provide parameters to a procedure that does return records, and then use the stored PT query to display on a form etc.

 

[jco23]

I use a generic pass-through routine for this type of process. I have a saved query (qpt_Generic) that I modify the sql for, with a switch for returning records or not.

Simply create your SQL statement as a string and pass it to it.

Public Sub sSendToPT_Generic(strQuery As String, bRetRecs As Boolean) 
    Dim db As Database
    Dim qDef As QueryDef
  
    Set db = CurrentDb()
  
    Set qDef = db.QueryDefs("qPT_Generic")
    qDef.Connect = db.TableDefs("ChangeThisToALinkedTableNameInYouDB").Connect
    qDef.SQL = strQuery
    qDef.ReturnsRecords = bRetRecs
  
    If Not bRetRecs Then
        db.Execute "qPT_Generic", dbSeeChanges
    Else
        qDef.Close  'db.Execute "qPT_Generic", dbOpenSnapshot
    End If
  
    Set qDef = Nothing
    Set db = Nothing

End Sub

To use it simply call it from any routine something like

    strSQL = "EXEC dbo.sp_zAdmin_CopyAllData @dNewDate = " & SERVERDate(Me.txtDestinationDate) & ", @dOldDate =" & SERVERDate(Me.txtSourceDate) & ","
    strSQL = strSQL & " @sNetworkList = '" & strWhere & "', @iMovedBy = " & glEmpID & ", @dMoved = " & SERVERDate(Now())
     
    sSendToPT_Generic strSQL, False

I use named parameters for ease of troubleshooting, and I have a function SERVERDate() that formats Access dates correctly for SQL server.

This routine runs a stored procedure that bulk copies data (and all the child records) from one date to another, therefore it's not returning results.
You can equally use it to provide parameters to a procedure that does return records, and then use the stored PT query to display on a form etc.

i'll give that a shot, thanks!