Using an ADO recordset as the recordsource for a report

[mike777]

Is this possible, or is it a requirement to have an Access object (i.e., table or query)?
Many thanks.
-Mike Kemp

 

[cmmrfrds]

I have done it on a Form in Access 2000, but not a report. I don't believe the report had a recordset object. This may be possible in later versions of Access.

 

[mike777]

cmmrfrds,
Thanks for the help.
The report object does have a record source property.
When you did it with a form, did you use unbound controls, or did you simply put the name of the ADO recordset in the Record Source property of the form?
Thanks again.
-Mike

 

[cmmrfrds]

The RecordSource takes an SQL statement, a stored procedure, or a table name which can be turned into an SQL statement. This is not a recordset object, but the Form has a recordset object.
Dim rs as New ADODB.Recordset
etc.......
Me.Recordset = rs

This will create a read only Form Recordset depending on the OLE-DB provider, in order, to have an updateable recordset in Access 2000 you must use the MSDATASHAPE provider. In later version you may be able to use any provider - I don't know.

 

[mike777]

Cmmrfrds,
For what it's worth, the report object has a Recordset property as well. I learned, however, that you cannot use this property in an MDB. I assume it would work with an ADP (or whatever the file extension is for an Access project...I think it's adp...).
-mike

 

[cmmrfrds]

You must have a newer version of Access. Since the Report has a recordset object use it to contain the recordset that you build.

In the OnOpen event of the Report.

Set Me.Recordset = yourrecordset

 

[mike777]

True, yes, I forgot to mention I have Office XP.
However, if you try to use it in and MDB, it gives you an error message that is actually helpful, in that it says, "Cannot use this feature in an MDB..". I assume you can use it in a project (adp or whatever.).
-Mike

 

[TimKThailand]

Yes, it's available only in ADP.