Pass Access table's date value as a parameter to SQL Server's stored procedure

Question

I have a stored procedure in SQL Server 2016 named usp_createRecord that takes in 2 parameters start_date and end_date.

I have a table in MS Access named MyReport that has 8 columns- 2 of them being startDate and endDate.

I need to pass the date value from MS Access as a parameter to SQL Server's stored procedure. Execute the stored procedure and display it in the MS Access workbook.

Hope I was clear.

Answer 1

I think you'll need a query with a field calling a VBA function passing your parameters which returns the value from a pass-through query whose SQL is determined at runtime. If you're calling a function like that for a large number of rows, it will be slow.

For example, you need a pass-through query with your connection to your SQL Server. I'll call that qry_PT_createRecord.

Then you need a public function in VBA with your two date parameters passed in which modifies the pass-through query's sql property, like:

Public Function g_createRecord(startDate As Date, endDate As Date) As Integer
    Dim db As Database
    Dim qdef As QueryDef
    Dim sql As String

    Set db = CurrentDb()
    Set qdef = db.QueryDef("qry_PT_createRecord")
    qdef.sql = "exec usp_createRecord " & startDate & "," & endDate
    g_createRecord = qdef.Execute
End Function

Then you need a query to display your fields from MyReport, call the function, and return the sp's value, if there is one. I'll call that query qry_createRecord. The SQL will look like:

Select 
 ID, 
 startDate, 
 endDate, 
 g_createRecord(startdate,enddate)
from 
 myReport