MS Access Report Connection to Recordset

Question

I understand the report recordset property cant be connected too.

However I want/need to do something like:

Private Sub testLoad()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

      cmd.ActiveConnection = gcn
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "mysp_ProofofJobScott"

      'Your parameters
      cmd.Parameters("@StartJob").value = 1234
      cmd.Parameters("@Cmonth").value = "February 2020"

      Set Me.Recordset = cmd.Execute
End Sub

Which would work fine...if I could bind a report to a recordset.

I am already using a variation of this for all my forms, and would like to be consistent in my approach.

I got thinking is there a way to use the cmd.execute to populate a temporary table, become a source for the recordsource or some other work around?

If I understand your question, you want to create a [Report](https://support.office.com/en-us/article/introduction-to-reports-in-access-e0869f59-7536-4d19-8e05-7158dcd3681c) in MS-Access that fetches data from a an MS SQL Server database, correct? Q: Would [SSRS](https://learn.microsoft.com/en-us/sql/reporting-services/report-data/query-design-tools-ssrs?view=sql-server-2014) be a viable solution for you? — FoggyDay, Jan 24 '20 at 23:14
I am not sure it would. The overall plan, and this is a way off yet, is some sites we work on will have untrained people using the database. I am better in Access than SQL, so been trying to keep the interface in Access. Will look at this though its an area I haven't explored. — David Watson, Jan 24 '20 at 23:20
Option 1: Use a Dao-Passthrough query, but that lacks parameters. Option 2 store result in a temp table and link that to report. — ComputerVersteher, Jan 25 '20 at 00:42
Best option: use this as an opportunity to learn a bit more about SSRS. Whether or not you use it for this particular project, it's important to know in order to best serve "the sites you work on". — FoggyDay, Jan 25 '20 at 02:37
Two different points of view here...Wonder which is both fastest to pursue and gives the best return for future...Oh well got to go and change brake pads on the car. Something to ponder whilst I do this. — David Watson, Jan 25 '20 at 22:35
https://stackoverflow.com/questions/20262266/configuring-an-access-report-to-use-a-sql-server-stored-procedure-as-its-record — lptr, Jan 27 '20 at 16:22
Try to read this [Using a Stored Procedure as a MS Access form Recordsource](https://stackoverflow.com/questions/38620119/using-a-stored-procedure-as-a-ms-access-form-recordsource) — Vlado, Jan 29 '20 at 13:39

ComputerVersteher · Answer 1 · 2020-01-31T05:29:05.223

Easy and fast is the Passthrough query, but you have to take care on passed arguments to be valid as you just concat sp name with them.

Consider change cMonth to a date, then evaluate month in sp.

Code to create PT query:

Private Sub CreatePTProofofJobScott(StartJob As Long, cMonth As String)
Const QueryDefName As String = "PTProofofJobScott"
With CurrentDb
    Dim QdfExists as Boolean
    Dim qdf As DAO.QueryDef
    For Each qdf In .QueryDefs
        If qdf.Name = QueryDefName Then
             QdfExists = True
             Exit For
        End If
    Next
    If Not QdfExists Then
        .CreateQueryDef(QueryDefName)
    End If
    With .QueryDefs(QueryDefName)
        .Connect = "ODBC;DSN=yourDsnToSqlServer" 'or Conn-String https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/
        .SQL = "EXEC mysp_ProofofJobScott " & StartJob & ", '" & cMonth & "'"
    End With
End With
End Sub

Use in Report_Open event with wanted args:

Private Sub Report_Open(Cancel As Integer)
CreatePTProofofJobScott Split(Me.OpenArgs,";")(0), Split(Me.OpenArgs,";")(1)
Me.RecordSource = "PTProofofJobScott"
End Sub

Pass args to OpenArgs on open cmd

DoCmd.OpenReport "yourReport",acViewPreview,,,,"1234;February 2020"

For future development, you should have a look at SSRS as FoggyDay noted

MS Access Report Connection to Recordset

1 Answers1