How to create an Access SubReport which uses as SQL Server Stored Procedure as its data

I am porting an Access Front End/Access Back End application into an Access Front End/SQL Server Back End application.

One thing that got me stumped for a while was a report which had an embedded query as a subreport. This query was complicated in that it had many joins, group by clauses on inner selects that were parametrised and quite complicated functions to work out what to display. It took a long time to run.

Ran a check on the SQL being sent to the server and soon realised that in order to conduct the joins the enquiry was returning all the records in one of our major tables. This was to produce an output of just a few lines long. There was only one thing for it, this query would have to be executed on the server.

Because the query was parametrised and these parameters were used to limit records in a GROUP BY inner select, a simple View would not work. I would have to implement it via a Stored Procedure. After a few false starts failing to convert Access SQL to SQL Server SQL I soon had a Stored Procedure that produced the correct results and ran a lot faster than the query before.

The question now became – how do I embed the results from this query. This, it turns out is harder than I thought. I had to dynamically create the query because I needed to set the parameters for the Stored Procedure before it was executed. But when I tried to attach to it to the recordsource of the sub-report I got an error message saying that this was only supported for ADP’s, and that is NOT what I have here.

But there is a way round it – here is what I did

  1. Create a form which is only displayable in datasheet mode.
  2. Create controls on the form to display each of the fields returned by the stored procedure
  3. Create an “onLoad” event procedure to connect the results of the stored procedure to the forms recordset property
  4. Embed the form within the report in place of the previously embedded query.

It works great.

For those following this, the only slightly complex piece is the code to run in the forms onLoad event.  Here it is (frmStatsByDate is a form holding to date parameters, getStrConn is a function that returns my current connection string to the backend database)

Private Sub Form_Load()
    Dim sd As String, ed As String
    Dim conn As ADODB.Connection
    If CurrentProject.AllForms("frmStatsByDate").IsLoaded Then
        sd = Replace([Forms]![frmStatsByDate]![start_date],"'","")
        ed = Replace([Forms]![frmStatsByDate]![end_date],"'","")
        sd = "1/1/2011"
        ed = "31/3/2011"
    End If
    Set conn = New ADODB.Connection
    conn.Open = getStrConn
    Set Me.Recordset = conn.Execute("EXEC dbo.qryCostPerWidget '" & sd & "','" & ed & "';")
    Set conn = nothing
End Sub

Author: Alan

I am Alan Chandler.

4 thoughts on “How to create an Access SubReport which uses as SQL Server Stored Procedure as its data”

  1. I would be very happy, if you can tell more about this tip. I understand that your sub report is a form in Access that u drag into your main report, but how does the sub report LinkMasterFields and LinkChildFields properties working when the main report have several records and for each of them there is a sub report with its own records, like a one-many relationship, where I normally write the primary key in LinkMasterFields and the foreign key in LinkChildFields. Thank you for making this tip solve my problem because a Pass-through query in sub report dont work together with LinkMasterFields and LinkChildFields.

    1. It was over three years ago that I wrote this. I just checked back with the application I was working on at the time, and it has moved on. I have replaced the sub form with a direct linkage to a pass through query that I created dynamically with the "Exec dbo.qryCostPerWidget ..." as its source. Since I am regularly dynamically creating past through queries with new source sql I created a little routine to do it

      Public Sub CreateQuery(qName As String, SQL As String)
      Dim db As DAO.Database
      Dim qd As DAO.QueryDef

      Set db = CurrentDb()

      On Error Resume Next
      Set qd = db.QueryDefs(qName)
      If Not (qd Is Nothing) Then
      ' So it exists, we must recreate it because the SQL might be different
      db.QueryDefs.Delete qName
      End If

      Set qd = db.CreateQueryDef(qName)
      Set db = Nothing
      qd.Connect = getStrConnDAO
      qd.SQL = SQL
      qd.ReturnsRecords = True
      Set qd = Nothing
      End Sub

      Where getStrConnDAO gets me the connection string for the pass through query that is needed for the database I am currently attached to (I can dynamically change that)

      So what you get now is a report with the table like results of the running of the query.

      That said, there never was a master child relationship - the report itself never had any record source, and was just a page to store several different sub reports on.

      That said, the even my subquery does have the potential of linking master/child fields in the properties section of the top level report. So I don't see why you can't use it, and expect the same sort of output as you would get if you did the same thing with two forms.

  2. Hi there, just a bit of feedback as this is No#1 result in Google & others are likely using it 🙂

    This method works great, but is open to SQL injection (both malicious and accidental).

    i.e. if a user was to enter the following in the StartDate box:

    ‘; DROP PROCEDURE dbo.qryCostPerWidget —

    They could delete the procedure – well, they could essentially do whatever they want.

    Its important that you use parameterised SQL whenever your dealing with adodb, or, at the very least, replace single quotes with double quotes on anything user imputable.

    1. J, thanks. I had forgotten this post. I have long since tidied this up in my app by adding a replace of single quote with null on the input. I’ll update my post

Leave a Reply

Your email address will not be published. Required fields are marked *