Can I supplied an ADO recordset as Me.RecordSource

[Clipper2000]

Hi,

In MS Access, we normally supply an Access SQL query in the Report_Open event such as:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "QryString"
End Sub

In VB6 and VB.NET, Crystal Reports etc, we can initiate an ADO object, fill the ADO recordset with records and manipulate the records until our heart's contend. Then we map the recordset object to the report template "on the fly"

Given that Access's Me.RecordSource only takes an Access SQL definition as the recordsource, is it possible to concoct a method to map an ADO recordset to an Access report "on the fly"?

 

[payback]

I am not sure about a Report but I have "so far" sucessfully used a virtual ADO recordset as the recordset for a Form which I am building for a completely unbound Form/Subform application.

In the Microsoft Knowledgebase have a look at "How to bind Microsoft Access forms to ADO recordsets". Although this does not give you the full information it might point you in the right direction.

 

[Clipper2000]

You are absolutely correct that there is a recordset property in the Form Class that allow the developer to hook a virtual ADO recordset to the form. However, I couldn't find a similar property for the Report class. I was hoping to find out whether anyone found a way around this limitation.

 

[payback]

I just copied some code from my form into a report and was able to produce the same result as my form.

My recordset can be completely manipulated without affecting actual data.

If you like I could post some sample code as how I achieve this but of course it might not be what you are looking for.

I am not familiar with VB6 etc., and last used Crystal Reports about 5 years ago, but must ask the question if you can not achieve what you want with Access reporting (so far it can do anything I have required)?

 

[RoyVidar]

I think binding a recordset to a report is available but only in ADP's, not mdb's. Not sure if it applied to the 2000 version, but at least 2002+ versions.

Roy-Vidar

 

[Clipper2000]

Hi Payback,

Now you have got me really interested. I am using Access 2000 most of the time, I have Access 2002 but didn't bother trying it, and I assume the reporting functions between the 2 versions are roughly the same.

Ok. with regards to Access reports, I can use ADO recordsets or ADO commands to return single values or recordsets within the virtual domain, and I can assign these values in memory variables etc. at the report's module level, ie, right below "Option Explicit" before any coding start.

Then of course I can attached these memory variables to fields in the report header, footer, etc.

My challenge is attaching an ADO recordsets to the report's detail section where more than one records are required.

If you can show me how to initiate an ADO object, filled it with data, then make recordset appear in the report's report's detail section, I will be very much appreciated.

 

[Clipper2000]

Hi Payback,

Sorry I type too fast, what I meant to say is if you can show me how to attach an ADO recordset in the detail section, I will be very much appreciated. The codes for setting up an ADO object is not required.

 

[payback]

This is the original code for a subform that is unbound and copied into the report module. I use it in an ADP attached to SQL Server.

I have to set NoOfLines because I can not add rows in a subform but you will no doubt be able to adapt it for your report detail by only adding each detail row as needed.

The original "virtual" concept was not mine but was posted in dbForums using specific text to populate the recordset (the author's comments were "Of course, I can't think of any particular use for this, but someone else may be able to."- if I knew who he/she was I would give them 10 stars), but I experimented with populating with live data and so far have found no issues. In the form, I then use SQL INSERTS,UPDATES and DELETES to manipulate the database.

Option Compare Database
Option Explicit

Dim rstf As Recordset
Dim rstd As Recordset
Private Const NoOfLines = 20

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

Dim fld As Field

Dim strCriteria As String
Dim strFieldName As String
Dim intNameID As Integer

Dim x As Integer

Set rstf = New ADODB.Recordset
Set rstd = New ADODB.Recordset

'Create the virtual recordset
With rstf
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic

.Fields.Append "LineNo", adSmallInt
.Fields.Append "NameLocationID", adInteger, , adFldMayBeNull
.Fields.Append "NameID", adInteger, , adFldMayBeNull
.Fields.Append "DeliveryName", adVarChar, 50
' etc, etc
.Open
End With

'Preload the number of maximum record lines for the virtual recordset
With rstf
For x = 1 To NoOfLines
.AddNew
.Collect(0) = x
.Update
Next x
'Move the cursor position back to the first line
.MoveFirst
End With

'Load the real data into the virtual recordset
'intNameID = Nz(Me.Parent.OpenArgs
intNameID = 4
If intNameID Then
strCriteria = "SELECT * FROM tblNameLocations " _
& "WHERE NameID = " & intNameID & " " _
& "ORDER BY DeliveryName;"
rstd.Open strCriteria, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

Do Until rstd.EOF

For Each fld In rstd.Fields
strFieldName = rstd(fld.Name).Name
rstf(strFieldName) = Nz(rstd(strFieldName))
Next fld

rstd.MoveNext
rstf.MoveNext
Loop
rstd.Close
End If

'Presumably here you would manipulate your recordset before binding to the report

Set Me.Recordset = rstf

Exit Sub

Errorhandler:
If Err = 2465 Or Err = 3265 Then
Resume Next
Else
Call Error_Display_Vars(Err, Application.CurrentObjectName)
End If

End Sub

Error_Display_Vars is a subroutine that displays and records in a table any user errors. Errors 2465 and 3265 trap for any fields brought over from the table that do not exist in my form/report so I can bring over only part of the table fields. Using a view as the table data should also work.

In the form (and also the report) I use the same field names from the table as the ControlSource and Name properties of the control so I do not have to type it all in manually hence the "For Each fld" code loop.

I would be very interested if it is successful in a report also, because I have never thought to use it that way and it could change a 2-pass report into a single pass report.

 

[payback]

Clipper2000 - did this idea work?

 

[Clipper2000]

Hi payback,

I'm assuming this works on ADP, but this doesn't seem to work on a regular Access report. The codes are fine, except that the "Set Me.Recordset = rstf" hangs. It appears that the "Set Me.Recordset = rstf" only works in a regular Form but not a regular report. Perhaps this is why the examples in the Help displays only show this property with an ADO recordset under the Form setting.