Set Report Recordsource to ADODB Query (1 Viewer)

[RossWindows]

I have the following code on a report.
When the report opens, I would like to set the recordsource to that of the ADODB query, however I am encountering the error: "This feature is not available in an MDB." I understand why the error is appearing, but I also know there has to be a way around it and I've seen examples using DAO, but this is ADODB...

The following is just one variation of the code I have tried and I feel like I've tried almost everything. An help would be greatly appreciated!

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

    Dim strSql As String
    Dim rs As ADODB.Recordset

        If Not IsNull(Me.OpenArgs) And Me.OpenArgs <> "" Then
            strSql = "exec dbo.spCoachingPrintOut " & _
                "@pkCoachingID = N'" & Me.OpenArgs & "'"
            Set rs = ExecuteADODBQuery("COACHING", strSql)
            Me.RecordSource = Recordset.Open(rs)
        Else
            DoCmd.Close acReport, Me.Name, acSaveNo
        End If

Exit_Report_Open:
    Exit Sub
Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

 

[vbaInet]

The problem here is not with ADODB, you just can't bind reports to recordsets.

Perhaps a stored proc is the way to go with this.

 

[RossWindows]

Essentially, that's what I'm trying to do.

I have a form that lists records with two or three fields.
I have a Print All button on that form's footer.

What I want to happen is when the button is clicked, it loops through each record and puts it on a report along with 10 other fields and prints it.
Then the report closes, and the loop continues to the next record on the form etc.

What if I pulled all the data into a temporary table and set the report's recordsource to that table and then deleted the table when complete?

I hope I'm just overthinking this?

 

[vbaInet]

Temp table would be another way and you can set the report's Record Source to that temp table. My preference would be a stored proc.

 

[RossWindows]

I was originally trying to use a stored procedure that resides on our SQL Server.
How would you go about using the sp identified in my original post?

("dbo.spCoachingPrintOut...")

 

[Galaxiom]

You need to set the Recordset property of the report rather than the RecordSource.

Set Me.Recordset = rs

 

[RossWindows]

That may be true with newer versions of Access but with '03, there is no recordset property on reports.

 

[vbaInet]

I'm not a SQL Server man but the way I would do it is using pass through queries which calls the stored proc. I know you wanted the ADO way but this would have to be in DAO. I will give you some aircode:

dim qdf as dao.querydef

set qdf = currentdb.querydefs("QueryName")

qdf.sql = "exec dbo.spCoachingPrintOut @pkCoachingID = N'" & SomeVariable & "'"

set qdf = nothing

docmd.openreport "ReportName", acPreview

The example assumes three things:

1. You have already set up a pass through query with the right connection strings.
2. You have successfully established a connection to the server.
3. The report's Record Source is the query.

However, if the number of returned records is quite minimal you could just go with the temp table idea.

 

[Galaxiom]

That may be true with newer versions of Access but with '03, there is no recordset property on reports.

Fair enough. I don't use reports much and when I have I have not found a need to load it with a recordset. I was just going on setting the recordset property of a form which I have used in this way many times and had expected to be the same for a Report.

However setting the RecordSource property (a string) to a recordset is not going to work either.

 

[RossWindows]

Exactly; I figured a report would have a recordset property too, but came to find out that isn't true.

Well, I ended up getting it to work by creating a passthrough querydef object on the fly like vbaInet suggested.
The report's recordsource property was set to the same name as the querydef object being created, so when it opens, the new querydef is used. After the report is closed, the querydef object is deleted. I can't post code right now because it's on a server at work but I'l post it on Monday.

Thanks for the help everyone!

 

[vbaInet]

Why create and later delete a querydef object anyway? You might as well keep it and re-use as needed.

But here's an fyi, if you don't want to create a temp querydef don't give it a name:

dim db as dao.database
dim qdf as dao.querydef

set db = currentdb
set qdf = db.createquerydef("")

And when you clean up or when the qdf object goes out of scope the querydef is no more. That is, no need to explicitly delete it.

 

[mdlueck]

The way I have been developing reports is to do as much as possible in a Stored Procedure. Execute that, downloading records into an FE temp table. Then have the report bound to the FE temp table. Further query type work is done for presentation in the report... such as group by, and sums in each subgroup.

I chose not to bind directly to a DAO.QueryDef which drives the Stored Procedure as I did not want Access tinkering around re-running the Stored Procedure over and over again. Thus I chose a FE temp table.

I use a nested DAO.QueryDef configuration to download the records into the FE temp table:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

 

[RossWindows]

...do as much as possible in a Stored Procedure. Execute that, downloading records into an FE temp table. Then have the report bound to the FE temp table...

That's exactly what I did, except I used a querydef instead of a tabledef.

Here's the code on my subform.
The subform is populated based on date range and other criteria entered by the user on the parent form. It's probably not perfect, but it's the closest thing I've got.

Option Compare Database
Option Explicit

Private Sub btnPrintAll_Click()
On Error GoTo Err_btnPrintAll_Click

    Dim db As Database
    Dim qdfpt As QueryDef

        With Me.Recordset
            .MoveFirst
            Do Until .EOF
                Set db = CurrentDb
                Set qdfpt = db.CreateQueryDef("qryCoachingPrintOut")
                qdfpt.Connect = "ODBC;DRIVER=SQL Server;SERVER=SERVERNAME;UID=USERNAME;PWD=PASSWORD;DATABASE=DBNAME;"
                qdfpt.SQL = "exec dbo.spCoachingPrintOut @pkCoachingID = N'" & Me.Coaching_ID & "'"
                DoCmd.OpenReport "rptCoachingPrintOut", acViewPreview
                DoCmd.Close acReport, "rptCoachingPrintOut", acSaveNo
                db.QueryDefs.Delete "qryCoachingPrintOut"
                db.Close
            .MoveNext
            Loop
        End With

Exit_btnPrintAll_Click:
    Exit Sub
Err_btnPrintAll_Click:
    MsgBox Err.Description
    Resume Exit_btnPrintAll_Click
End Sub

This code loops through each record on the subform and downloads the supporting data for each record, opens the report, prints, closes, repeats. This way I have complete control over each record in the batch.