Setting a report's data source to be an ADO Recordset

[mincefish]

I have recently converted an Access 97 front end, which has tables that are linked into an Access 97 back end, to be a 'free standing' database, where I use an ADO connection to obtain all my data. The idea behind this is that we will be moving onto SQL Server 7 back end pretty soon, and I figured that the practice would be good, and I could have alot of the 'ADO code' already in place. It would be a question of setting the connection function to point at the SQL Server Db, rather than the Access Db.

My problem lies in the fact that my Reports run from queries...and without the tables, queries are nothing!! I want to have the data source as a recordset, but I'm not really sure how to go about this. I've scoured tek-tips for previous threads, but to little avail. I've even tried to monkey about with the code, but if I do, for example:

me.recordsource = rstMainAgentReport
[\code]
(where [code]rstMainAgentReport[\code] is all the data I want to show in my report)...I get "Invalid Use Of Property", and [code].Recordsource[\code] is highlighted...

Please help!!! This is driving me nuts! X-)

Tom

 

[HenryAnthony]

Hey Fish,

You need to search help for "createquerydef method".

Here is some code I was helped with on this forum that enabled me to send email to a list of recipients.

Hope you can puzzle through this.

Private Sub Command6_Click()

Dim dbs As Database
Dim strSQL As String
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdfTemp As QueryDef



Set dbs = CurrentDb
strSQL = "SELECT TblJunction.ConID, TblCon.ConName, TblJunction.ProID, TblPro.ProName, TblCon.ConEmail FROM TblPro INNER JOIN (TblCon INNER JOIN TblJunction ON TblCon.ConID = TblJunction.ConID) ON TblPro.ProID = TblJunction.ProID WHERE (((TblJunction.ProID) = [ID]))ORDER BY TblJunction.ProID;"
Set qdf = dbs.CreateQueryDef("", strSQL)

qdf.Parameters!ID = Me!ProID 'fills the parameter

Set rst = qdf.OpenRecordset

rst.MoveFirst

Do Until rst.EOF

DoCmd.SendObject acSendReport, "Report1", acFormatRTF, _
rst!conemail, "", , _
"Project Status Report", , True, False

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing

End Sub