0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Baibhav Ghimire
  • 23
  • 1
  • 5
  • What part(s) of this do you need help with? – Sean Lange Oct 10 '17 at 21:15
  • Possible duplicate of [MS Access call SQL Server stored procedure](https://stackoverflow.com/questions/18800727/ms-access-call-sql-server-stored-procedure) – Ousmane D. Oct 10 '17 at 21:16
  • EXEC usp_createRecord '2017-02-01','2017-03-01' When I run this as pass through query I get result But I want the dates to be passed dynamically from table 'MyReport' Thank you for your time. – Baibhav Ghimire Oct 10 '17 at 21:26
  • 1
    OK. We would need to see some code in order to help. Just stating you want this dynamic does let anybody help you write the code. – Sean Lange Oct 10 '17 at 21:29
  • I usually do this in VBA. It sounds like you want the results of each row of a query like `select start_date, end_date from MyReport` passed into a pass-through query like `exec usp_createRecord qry!start_date, qry!end_date`, but pass-through queries don't reference Access queries like that. – Beth Oct 10 '17 at 21:34
  • I suppose not. Thanks anyways Beth and Sean for your comments. – Baibhav Ghimire Oct 10 '17 at 21:54

1 Answers1

0

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
Beth
  • 9,531
  • 1
  • 24
  • 43