How to assign the data contained in a recordset in memory to a report? (1 Viewer)

[amorosik]

I hoped someone will ask. But seems everyone has the answer and I'm the only one who has no clue. Ok, I'll step forward and ask it.

How Sub tcpServer_DataArrival is executed? Do you have a timer in your form to fire it?

thanks

No, there is no need for timer
When data arrive 'from the outer space' tcpServer_DataArrival is triggered and code inside is executed
You can easily try using any client that allows socket connections, for example the Hercules program (hw-group.com)
After configuring the program, in the Tcp Client tab, enter the IP address of the PC where your Access program runs, enter the desired port (in this case 12345) and press 'Connect' to establish the connection
At this point, everything you type in 'Received/sent Data' will appear in the textbox of your Access form

But this is not the request
I remember is "if it is possible to pass a recordset of data in memory to a report to start a print"

 

[KitaYama]

But this is not the request
I remember is "if it is possible to pass a recordset of data in memory to a report to start a print"

I didn't try to answer your question. You submitted a code that I couldn't understand and I simply asked a question about your procedure.
Seems that you're quite comfortable to be aggressive toward others.
You may want to add a "Don't ask questions" to your signature.

Anyway, thanks for the reply.

 

[amorosik]

...Seems that you're quite comfortable to be aggressive toward others...

I'm sorry you write the above
I hope to be friendly, always
Maybe I don't always succeed, but that's the goal to strive for
Where exactly, in the response to your posts, did I seem aggressive?

 

[The_Doc_Man]

Ok, then it seem that is not possible

how to pass the recordset as created above to the report (the MyrsAdoDb to understand)?

You cannot use an ADO recordset as the source of a report. See this link below (which discusses report recordsets), find the REMARKS section, and check the purple-highlighted paragraph.

Report.Recordset property (Access)

Office VBA reference topic

learn.microsoft.com

Those responses that suggested the use of a temporary table to hold the network input for subsequent processing represent your best bet to get this job done.

There was some confusion here regarding your Data_Arrival event. It was an extraneous diversion that confused folks. Your WinSock routines support a WinSock Data_Arrival event. The Access FORM cannot directly process such an event - but the WinSock code called from that form could process it "behind the scenes." That diversion became a distraction. However, now you have the answer to your question, though I am sorry that it probably isn't the answer you wanted.

 

[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?

 

[Pat Hartman]

I've noticed a lot of bad help entries lately. I guess I should be thanking them for trying but instead, I keep sending negative feedback. I hope you sent feedback. I have a way of contacting the Access help team directly but I usually just use the feedback which I am told that they do read. PM me if you want me to send something directly to the team.

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?

The person who wrote the code for the report didn't get the memo about including support for recordsets and the testers didn't test it thoroughly The people who will read your feedback are not those who could actually change the Access code base but they could get it on the list of things to change, especially if 10 people from this forum went out to that entry and suggested the same change

@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

 

[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.

 

[Pat Hartman]

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