How to create a JET from a virtual ADO recordset ?

[Clipper2000]

Hi,

There is a discussion in the Access Reports forum on how to attach an ADO recordset to the Me.RecordSource property. So far attaching an ADO recordset to the RecordSource property doesn't seem possible in a regular report, it may be possible in ADP. This is because the Me.RecordSource only accepts a SQL string (the key word here is string).

To circumscribe this issue, I can make this work by an intermediary step via creating an Access SQL string first using perhaps the DAO object. But this also seems to present a challenge because the JET SQL only works on tables and JET SQL definitions, not on a virtual ADO recordset. Can JET SQL query an ADO recordset? I would be very much appreciated if someone can show me the codes here.

 

[PHV]

And what about something like this ?
Set Me.Recordset = theADOrecorsetObject

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GHolden]

You can get a report to run from an ADO recordset by allocating the Source property of the recordset to the recordsource property of the report....

An example of the code on the report...

Private rst As New ADODB.Recordset
Private cnndb As New ADODB.Connection

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

cnndb.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=Z:\Sample Access DB\Test.mdb;" & _
           "Uid=admin;" & _
           "Pwd="

strSQL = "SELECT * FROM tblName"

rst.Open strSQL, cnndb, adOpenKeyset, adLockOptimistic
Me.RecordSource = rst.Source

rst.Close
Set rst = Nothing

End Sub

There are two ways to write error-free programs; only the third one works.

 

[cmmrfrds]

You don't say what version of Access. In Access 2000 the Report does not have a recordset object, but apparently the later versions do. For a later version the suggestion of PHV should work for you.

If an older version of Access, why does an sql string not work for you?

 

[Clipper2000]

Hi,

Thanks gentlemen for your excellent answers !

The Me.RecordSource = rst.Source will return a SQL string and as a result, it worked, and this is exactly what I need !

 

[Clipper2000]

Hi cmmrfrds,

I used Access 2000, I have 2002 but never try it. You are right that in Access 2000 the Report does not have a recordset object. However, I should have thought of Me.RecordSource = rst.Source, its my mistake . Personally I prefer PHV's version.

 

[Clipper2000]

Hi again,

By the way, there is a big limitation to attach only a SQL string to the record source such as: Me.RecordSource = rst.Source

This is because the source never change, even if you manipulate the ADO afterward, ie, filtering it etc.

As a result, PHV's method of attaching a true ADO set to Me.RecordSource is the only way to go. But this is not possible in Access 2000 and prior. I think I will install my Access 2002 and give this a try.