How to assign the data contained in a recordset in memory to a report?

[amorosik]

You cannot use an ADO recordset as the source of a report.

Report.Recordset property (Access)

Office VBA reference topic

learn.microsoft.com

Ok, this is THE answer to my question

 

[MajP]

You cannot bind reports to ADO recordsets. You must either use DAO or else dump the ADO recordset to a temporary table, and then bind the report to that temporary table.

That note is super confusing. That is on the "Report.Recordset" page but the conversation switches back and forth between a Form's recordset and a report recordset. The two capabilities are not the same AFAIK.

That note is incorrect or incomplete
If they are talking about a form it is wrong because both an ADO and DAO recordset can be assigned
If they are talking about a report they are wrong because neither ADO or DAO recordsets can be assigned
If they are talking about and ADP report it is correct, but that was deprecated like 100 years ago. I have never seen one.

	Bind ADO Recordset	Bind DAO Recordset
Access Form	Yes	Yes
Access Report	No	No
Access ADP Report	No but who cares	Yes but who cares

My question has always been why you cannot set the recordset of a report, but you can return the recordset built from a recordsource? Anyone know, or is there a trick to assign a recordset?

 

[561414]

As if I needed another reason not to use reports.

 

[amorosik]

@amorosik I have created VB programs using Winsock and similar programs for IBM mainframes using COBOL but never thought to do it with Access since Access isn't meant to be left open 24/7. So, thanks for the explanation of how to use it in Access.

Since you have to move the data somewhere, copy it to TempVars rather than creating a recordset. Then set the report controls to the Tempvars.

=TempVarABC

I simply answered a question you asked
"...How does it know when data is entered "via the network"? .."

Certainly Access is not the most suitable system to do these things, but that was just an example to make it clear that the information to be sent to the report can also reside only in memory
My goal was to understand if it is possible to pass an Ado recordset in memory to a report, and from Microsoft document it seem that it cannot be done

 

[amorosik]

As if I needed another reason not to use reports.

If you want to print and don't use reports, what would you use?

 

[561414]

If you want to print and don't use reports, what would you use?

A custom function that creates Word or Excel documents based on a template. Depending on the context of what I want to return. I would not use mail merge either, it sucks.

 

[amorosik]

A custom function that creates Word or Excel documents based on a template. Depending on the context of what I want to return. I would not use mail merge either, it sucks.

Interesting possibility
And on pc where Word / Excel is not present?

 

[561414]

Interesting possibility
And on pc where Word / Excel is not present?

I would check what else can be used, maybe HTML or bare txt file.

 

[arnelgp]

Ok, then it seem that is not possible

impossible? i gave you the code to Create the Local table based on your original code on the form
and still you find it impossible.

 

[amorosik]

impossible? i gave you the code to Create the Local table based on your original code on the form
and still you find it impossible.

Yes, is impossibile
Question is "how to pass in memory ado recordset to report"
Your answer, for wich i thank you, does not answer my question, because need a table on db
And this is what i wanted to understand if it could be avoided

 

[arnelgp]

"how to pass in memory ado recordset to report"

the answer, You Cant pass a Recordset to a Report.
maybe in the Future when MS deals with this.
but from past up to present time it is not possible to pass a Recordset
to be a Report and use it as it's source.

 

[amorosik]

"..but from past up to present time it IS NOT POSSIBLE to pass a Recordset..."
This is the answer to my question

 

[arnelgp]

another possibility is to "dump" your adodb.recordset to a textfile and use the textfile as your recordsouce:

Set MyrsAdoDb = New ADODB.Recordset
MyrsAdoDb.Fields.Append "read_number", adInteger
MyrsAdoDb.Fields.Append "table_name", adVarChar, 250
MyrsAdoDb.Open , , adOpenDynamic, adLockOptimistic
MyrsAdoDb.AddNew Array("read_number", "table_name"), Array(Count, tdf.Name)
MyrsAdoDb.Sort = "table_name asc"
Set Me.Recordset = MyrsAdoDb

'arnelgp
'write to textfile
Dim path As String
Dim filenum As Integer
path = CurrentProject.path & "\read.txt"
filenum = FreeFile
Open path For Output As filenum
With MyrsAdoDb
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        Print #filenum, !read_number & ", " & !table_name
        .MoveNext
    Loop
End With
Close #filenum

from your report's Open Event:

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = _
    "SELECT [Read#txt].F1 AS read_number,  [Read#txt].F2 AS table_name" & _
    " FROM [Read#txt] IN '" & CurrentProject.path & "'[Text;FMT=Delimited;HDR=NO;IMEX=2;]"
End Sub

 

[cheekybuddha]

The form is fed from a recordset that I loaded earlier with the following code

---------------------------------------
Set MyRsAdoDb = New ADODB.Recordset
MyRsAdoDb.Fields.Append "read_number", adInteger
MyRsAdoDb.Fields.Append "table_name", adVarChar,250
MyRsAdoDb.Open , , adOpenDynamic, adLockOptimistic
MyRsAdoDb.AddNew Array("read_number", "table_name"), Array(count, tdf.name)
MyRsAdoDb.Sort = "asc table_name"
Set Me.Recordset = MyRsAdoDb
---------------------------------------

So your recordset contains only a single record?

Or is there a loop that you haven't shown in the code that adds multiple records?

The above could be replaced with:

Me.RecordSource = "SELECT TOP 1 " & _   
                    "'" & count & "' AS read_number, " & _
                    "'" & tdf.name & "' AS table_name " & _
                  "FROM MSysObjects;"

Then you can either assign the same SQL to your report's RecordSource, or do something like (within your report's Open event):

Me.RecordSource = Forms("YourFormName").RecordSource

However, it seems strange to use a continuous form for just a single record.

 

[amorosik]

@amorosik I gave you a solution back in #27 which you totally ignored.

No, your answer has been read carefully
But that's not the solution to the question asked
If you see the initial post the question is whether it is possible to pass an Ado recordset in memory to a report
And this is not possible
Your suggestion works around the problem and goes so far as to feed the report with something that isn't an in-memory Ado recordset

 

[amorosik]

So your recordset contains only a single record?

Or is there a loop that you haven't shown in the code that adds multiple records?

The above could be replaced with:

Me.RecordSource = "SELECT TOP 1 " & _ 
                    "'" & count & "' AS read_number, " & _
                    "'" & tdf.name & "' AS table_name " & _
                  "FROM MSysObjects;"

Then you can either assign the same SQL to your report's RecordSource, or do something like (within your report's Open event):

Me.RecordSource = Forms("YourFormName").RecordSource

However, it seems strange to use a continuous form for just a single record.

I have indicated an example, the simplest to show how the recordset with the data was obtained
The question is not 'what the recordset contains', but whether it is possibile to pass it to the report for feeding and printing

 

[cheekybuddha]

The question is not 'what the recordset contains', but whether it is possibile to pass it to the report for feeding and printing

The question was answered: it's not possible.

So now the question becomes: do you want to get the data in to a report and, if so, what's the most effective way of doing so?

 

[amorosik]

The question was answered: it's not possible.

So now the question becomes: do you want to get the data in to a report and, if so, what's the most effective way of doing so?

Yes the question has to be anwered, "is impossible"
No, there is no next question
For those who need to print usin Access report, they can simply save to a temporary table and then pass to the report

 

[cheekybuddha]

... or perhaps there's a better way to gather the data than using an ADODB recordset in the first place?

 

[amorosik]

... or perhaps there's a better way to gather the data than using an ADODB recordset in the first place?

This is another question