Using a temporary ADO recordset as recordsource for a report

[spluess]

Hi
I have created a temporary ado recordset (I had to add some empty rows into and existing recordset). The recordset has now the correct number of rows and I can navigate through them just fine.
But so far I have been out-of-luck trying to get a report to use that recordset as its recordsource. Here is some of my code (I tried to have the code on a click of a button on a form and I also tried to have it in the report_open event. I also tried to open a form instead of a report and I also tried to save the recordset with adPersistADTG and loading it again):

Dim db As DAO.Database
Dim rso As DAO.Recordset
Dim rst As ADODB.Recordset
Dim i As Integer
Dim rcount As Integer
Dim membercount As Integer
Dim CalRec As Integer
Dim sqlstr As String
Dim startDate As Date

startDate = DateValue("05/14/01&quot //Just an example date
Set db = CurrentDb()
Set rso = db.OpenRecordset("members", dbOpenTable)
membercount = rso.RecordCount
rso.Close

Set rst = New ADODB.Recordset

rst.Fields.Append "name", adVarChar, 60
rst.Fields.Append "monday", adVarChar, 15
rst.Fields.Append "tuesday", adVarChar, 15
rst.Fields.Append "wednesday", adVarChar, 15
rst.Fields.Append "thursday", adVarChar, 15
rst.Fields.Append "friday", adVarChar, 15
rst.Fields.Append "BeginDate", adDate
rst.Fields.Append "Enddate", adDate

rst.Open

For i = 1 To judgecount
sqlstr = "SELECT members.*, calnew.* FROM calnew RIGHT JOIN members ON calnew.memberid = members.memberid WHERE calnew.memberid = " _
& i & " AND calnew.weekid = DateValue('" & startDate & "')"
Set rso = db.OpenRecordset(sqlstr)
If rso.BOF And rso.EOF Then
rcount = 0
rso.Close
Set rso = db.OpenRecordset("SELECT members.* FROM members WHERE members.memberid = " & i)
Else
rso.MoveLast
rcount = rso.RecordCount
rso.MoveFirst
End If

If rcount > 0 Then
For CalRec = 1 To rcount
rst.AddNew Array("name", "monday", "tuesday", "wednesday", "thursday", "friday", "Begindate", "Enddate&quot, _
Array(Trim(rso![last]) & " " & Trim(rso![middle]) & " " & Trim(rso![first]), IIf(IsNull(rso![monday]), "", rso![monday]), IIf(IsNull(rso![tuesday]), "", rso![tuesday]), _
IIf(IsNull(rso![wednesday]), "", rso![wednesday]), IIf(IsNull(rso![thursday]), "", rso![thursday]), IIf(IsNull(rso![friday]), "", rso![friday]), DateValue(startDate), DateAdd("y", 4, DateValue(startDate)))
rso.MoveNext
Next CalRec
rso.MoveFirst
End If

If rcount < 5 Then
For CalRec = 1 To (5 - rcount)
rst.AddNew Array(&quot;name&quot;, &quot;monday&quot;, &quot;tuesday&quot;, &quot;wednesday&quot;, &quot;thursday&quot;, &quot;friday&quot;, &quot;Begindate&quot;, &quot;Enddate&quot, _
Array(Trim(rso![last]) & &quot; &quot; & Trim(rso![middle]) & &quot; &quot; & Trim(rso![first]), &quot;&quot;, &quot;&quot;, &quot;&quot;, &quot;&quot;, &quot;&quot;, DateValue(startDate), _
DateAdd(&quot;y&quot;, 4, DateValue(startDate)))
Next CalRec
End If

rso.Close
Next i

rst.UpdateBatch
rst.MoveFirst

DoCmd.OpenReport &quot;Report5&quot;, acViewPreview //In this case I declared rst as a global variable in a standard class module
'rst.Close


'Me.RecordSource = rst //This I used in the report_open event

' rst.Save &quot;c:\temp\cal.adtg&quot;, adPersistADTG //Trying to save it as a file for later use
' rst.Close

' DoCmd.OpenForm &quot;Form1&quot;, windowmode:=acHidden //Trying to open a form instead of a report
' With Forms(&quot;Form1&quot
' Set .Recordset = rst
' .Visible = True
' End With

rst.Close
Set rst = Nothing
Set rso = Nothing
Set db = Nothing

======================================================
No matter which one of the above I try I always get the error:

Run-Time Error 7965: The object your entered is not a valid Recordset Property.
The debugger will be on the line that I try to assign the recordset to the recordsource/recordset of the report or form.

Am I just missing a simple thing or is this just not feasable this way? The recordset has all the rows I need and as this is going to be used from more than one person I didn't want to create physical tables for this very temporary data.

Thanks in advance for any hint/suggestions

Serge

 

[Francescina]

ACCESS ONLINE ENCYCLOPEDIA Article Code: R20

This is what I found in the ACCESS ONLINE ENCYCLOPEDIA (

http://www.Unsoftwareag.com):

How can I use a recordset as recordsource for a report ? ?
Reports (and forms) recordsources have to be tables or queries. You can create a temporary table or query if you want to use a recordset as RECORDSOURCE of a form or report. Much easier and faster is the usage of the NAME property of the recordset. This returns the name of the table or query or the SQL string that builds the recordset.

If you define the recordset as a global var then you can simply use he following code in the report's OPEN event:


me.recordsource=globalRS.Name

 

[spluess]

Thank you Francescina

I had already looked at that, but I can't get the .Name property to show up or work.I defined the recordset with:

Global rst As ADODB.Recordset

then I used me.recordsoure=rst.Name

and got an error message

 

[spluess]

The error message is:

Compile Error:
Method or data member not found

Me.RecourdSource = rst.Name

 

[Francescina]

How could I be so blind (and why are there so many undocumented differences between ADO and DAO?):

The NAME property of the recordset does only refer to DAO-recordsets, for ADO-recordsets it does no longer exist!

If you use a DAO recordset its NAME property will return the first 256 characters of its underlying SQL string.

 

[spluess]

Hi, and yes that has been my findings as well and as the temporary feature is a new item for ADO (and it seems not part of DAO) I'm stuck.

Thanks for your info

Serge

 

[mcneillrh]

I'm having the exact same problem.
Did you find a solution to this ADO/report problem?

 

[rbowes]

This is one of the most maddening things about Access 2000. Microsoft takes DAO away and replaces it with ADO, which works very well, but when they made Forms work better with ADO recordsets, why not Reports too?

Here is my solution (it is not one that I'm at all happy with but it works):

Public Sub MakeTempTable(rs As ADODB.Recordset)
Dim ADOcon As New ADODB.Connection
Dim ADOCmd As New ADODB.Command
Dim adoRS As ADODB.Recordset
Dim MySQL As String
Dim i As Integer
Dim strfields As String
Dim intCount As Integer
' Use active Access connection.

Set ADOcon = CurrentProject.Connection

' Drop Tables if they exists
On Error Resume Next
ADOcon.Execute &quot;DROP TABLE tblReportTemp&quot;

'Create New Tables
strfields = &quot;&quot;
strfields = &quot;([&quot;
For i = 0 To rs.Fields.Count - 1
strfields = strfields & rs.Fields(i).Name & &quot;] CHAR, [&quot;
Next
strfields = Left(strfields, Len(strfields) - 3) & &quot&quot;

ADOcon.Execute &quot;CREATE TABLE tblReportTemp &quot; & strfields
RefreshDatabaseWindow

Set adoRS = New ADODB.Recordset
adoRS.Open &quot;SELECT * FROM tblReportTemp Where 2 = 1&quot;, ADOcon, adOpenKeyset, adLockOptimistic
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
adoRS.AddNew
For intCount = 0 To rs.Fields.Count - 1
adoRS.Fields(intCount) = rs.Fields(intCount).Value
Next
adoRS.Update
rs.MoveNext
Next

set adoRS = nothing
set ADOcon = nothing

DoCmd.OpenReport rptMyTemp, acViewPreview
End Sub

Set the Recordsource of the report to tblReportTemp and that should work. Essentially, your using Jet to do the work for you. It isn't pretty by any means, and I usually delete the table on Report_Close (or the Calling Form's Close event) and Compact on Close as well.

 

[spluess]

Yes, I had that too. But that doesn't work well if you have the database on a shared drive accessed by multiple users.

My solution to this now has been to have a physical temporary table and have users log-in to the database. Now I fill in data into the temporary table inlcuding the userid (so I can first delete all entries for that user before starting re-populating it).

Have just done it all with ADO now to keep things cleaner.

Serge

 

[mcneillrh]

The other way around this which might be faster is to create a temporary table with one field (call it whatever you want), go through the ADO recordset and add a blank record for each record in the ADO recordset. Then, set the report recordsource to the name of this table. (in other words, you don't need the actual data from the table, you just need a local table with that many records)

 

[Francescina]

You should be aware of a big disadvantage
in using temporary tables:
Once you delete them the disk space is still
occupied (only compacting will help). This leads in
certain cases to an extreme bloating of the MDB.

In general I create external files (ASCII or pre-linked CSV) if I ever need temporary tables.

 

[Harshu]

Hi,
got to this forum for first time. Even i have the same problem. How do i pass the recordset to the cr 8.5. I will explain my problem.

I have designed the report using More Data Sources -> Active Data Ado and have selected various tables to be used.
Now i am trying to use only one report (rpt file) for printing multiple reports using various different tables. My requirement is i want to pass the recordset from the form where i accept from the user various parameters and then make a temperory table and then i make the recordset. i want to pass this recordset to the report. Now i have put the parameter fields in the details section thinking that i will be able to pass the values from the recordset to these parametr fields. Am i thinking wierd if i am then i am sorry as i am new to cr.

can anybody tell me how to achieve this.

Thanks,
Harshu.