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

[Pat Hartman]

As if I needed another reason not to use reports.

Sounds like you shouldn't be using Access at all

 

[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

 

[cheekybuddha]

Gosh, you're sharp!

 

[MajP]

I think most would agree the temp table would be the easiest. However, my first guess without knowing the details is do away with Access all together. Since you can pull your data in via vba from an external source then just do this in Word and use Word as your reporting source. Even if you need a disconnected recordset, which you may not you can still do that in word. This then gives you a lot of flexibility of reading the recordset and writing to Word. Unless you need complicated reporting features such as sorting and grouping. You can build and ADO recordset as needed, but you may be able to simply write to the document as data arrives.

These read and write to Access, but you could do away with Access

Copying currency values from Access to paste into Word

Our computer system has a variety of Word templates with prompts for currency values to be entered one at a time to populate a grid within the letter. The users have been using an Excel spreadsheet to calculate the values and then selected the group of cells and pasted them into the Word...

www.access-programmers.co.uk

get data from word table

Hello friends I need to get a word table to access tables I can browse the open file dialog I need to import the data only from this file word the problem I face when importing the file Enter (Newline) between and above paragraph the application converts it to a new row my data is grouped...

www.access-programmers.co.uk

 

[Pat Hartman]

But that's not the solution to the question asked

You have been told multiple times that your solution is not possible. You can keep arguing or you can consider an alternative.

If you are dealing with ONE record at a time, use the tempVars suggestion. If you are dealing with multiple records, make a temp table.
we are 49 posts into what is essentially a simple thread.
-Why doesn't this code work
-Recordsets are not supported
-various alternatives depending on how many records you are receiving at one time.

- so may be 10 posts because there are several solutions you can consider.

At some point, the experts will simply stop trying to help.

 

[amorosik]

You have been told multiple times that your solution is not possible. You can keep arguing or you can consider an alternative.

If you are dealing with ONE record at a time, use the tempVars suggestion. If you are dealing with multiple records, make a temp table.
we are 49 posts into what is essentially a simple thread.
-Why doesn't this code work
-Recordsets are not supported
-various alternatives depending on how many records you are receiving at one time.

- so may be 10 posts because there are several solutions you can consider.

At some point, the experts will simply stop trying to help.

"...that your solution is not possible.." ???
I not have a solution

"...Why doesn't this code work.."
I have never written that the proposed code does not work

"..Recordsets are not supported.."
Ok, this is the correct anwer to my question

"..-various alternatives depending on how many records you are receiving at one time...."
I've never asked for an alternative way

 

[Pat Hartman]

Then please close the thread.

 

[amorosik]

Then please close the thread.

The thread closes if nobody writes in it

 

[The_Doc_Man]

Actually, not true. You, as thread starter, can close it, or a moderator can close it for you. Otherwise it stays open.

 

[561414]

Sounds like you shouldn't be using Access at all

I've said it in the past and I will say it again. You have issues, and they're not related to Access.

 

[The_Doc_Man]

Per the request of the OP, re-opening this thread.

 

[Gasman]

Can you also restore the missing posts, as not getting the full picture here?

 

[Pat Hartman]

I don't think there are any missing posts. No moderator deleted anything that I am aware of. If something has been deleted, i was deleted by the OP.

 

[The_Doc_Man]

Can you also restore the missing posts, as not getting the full picture here?

I have reviewed the posts and they are numbered consecutively. As a moderator, I would have the ability to see any post that was hidden, and none are hidden. I do not believe anything is missing unless a moderator performed a silent edit. I don't claim an eidetic memory here, but this thread is pretty much as I recall it.

The summary is, as far as I can tell:

The OP asked if an in-memory ADO recordset could be used as a .Recordsource for an Access report. The answer, from MS Online documentation, seems to be no, although at first a couple of people were unsure of that answer.

During the discussion, we learned that amorosik uses WinSock and a network "Data Arrived" event behind the scenes in a form. The event in question is not a form event but a network event that awakens the code behind the form that was waiting for data. Once that data has arrived and has been delivered, that appears to be the source of the data for the ADO in-memory recordset. This network discussion was actually a diversion that confused matters.

Questions arose regarding why a temporary table was not acceptable. The OP says it is not but no further detail was given. The use of a set of TempVars was mentioned. This offered solution also was not acceptable.

@Gasman, you now have it - in summary - to the best of my ability to summarize it.