ADODB RecordSet as Access Report RecordSource

Question

I have a simple form, a query and a report in Access 2003. I have to manipulate the results from the query in a recordset using VBA and then pass it on to the report as its RecordSource.

If I declare the recordset as RecordSet and use its Name property as the RecordSource of the report then it is working. However, because I need to edit the recordset, I though it would be easier to use an ADODB RecordSet as below.

The records set is declared as Dim rs As ADODB.RecordSet in a global module. The rest of the code is;

Dim db As Database
Set db = CurrentDb
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = con
rs.Source = "Select * from XXX"
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset
rs.Open

'manipulate rs here....'

I used to pass the RecordSource of the report as myReport.RecordSource = rs.Name. But the ADODB doesn't have a Name property.

How can I pass this recordset to the report as its RecordSource?

Thanks

Address

Why do you think you have to do this? Why can't you just set the Recordsource in the report's OnOpen event? – Highpriced

I am actually setting the RecordSource property of the report at the report's open event, but it requires the name of the record set and ADODB Recordset doesn't have a name property. – Address

I'm saying don't use a recordset -- just set the SQL of the Recordsource property to be the SQL you're using to open your recordset. If there's some reason that won't work, I don't see it. That's the standard way to do this, instead of setting the report's Recordset (which is something I've never needed to do in all my years of programming Access for a living). – Highpriced

@Highpriced for what it's worth there are times you might like to do this - if you need to build a TSQL pass through query (to call a stored procedure) and set the recordset of the report to that. – Gynoecium

Answer 1

You cannot bind a report to an ADO recordset in an mdb, only in an adp: http://support.microsoft.com/?id=287437

Soppy

Yep, not possible with an ADODB. So I just created a make table query and manipulated the data within that. Then just bound the report to that table. – Address

Answer 2

I don’t have a copy of access 2003 to hand but from memory you just do

Set Me.Recordset = rs

Just had a look on the Microsoft KB and it looks like my memory is still working!

http://support.microsoft.com/kb/281998

Stanch