DAO recordset for reports ?

Z

Zlatko Matiæ

Hi.
Does anybody know how to assign DAO recordset to report, without using
queries or tables as Record Source ?
It seems that Me.recordset=rs doesn't work for reports ?
Thanks in advance.
 
J

Joerg Ackermann

Zlatko said:
Does anybody know how to assign DAO recordset to report, without using
queries or tables as Record Source ?
Click to expand...

DAO-Recordsets can't be disconnected like
ADO-Recordsets. They always base to a table
or query.
It seems that Me.recordset=rs doesn't work for reports ?
Click to expand...

A recordset is an object.
Use: SET Me.recordset = rs

Acki
 
J

Joerg Ackermann

Joerg said:
DAO-Recordsets can't be disconnected like
ADO-Recordsets. They always base to a table
or query.


A recordset is an object.
Use: SET Me.recordset = rs
Click to expand...

I think you are working in a MDB.
Then try this:

Private Sub Report_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("table", dbOpenDynaset)

Me.RecordSource = rs.Name

End Sub

Acki
 
L

Larry Linson

Does anybody know how to assign
DAO recordset to report, without using
queries or tables as Record Source ?
Click to expand...

What purpose is served by using the Recordset rather than the SQL that was
used to create the Recordset, other than, perhaps, saving some time and
machine usage in not having to retrieve the data again?

Yes, it can be done, as others have explained.

Larry Linson
Microsoft Access MVP
 
Z

Zlatko Matiæ

Hello, Larry.

The reason I intended to do that is the following:
Everybody can import my queries from MDE. Although VBA is secured, structure
of tables and queries are not.
So, I wated to preserve SQL string as string inside VBA function, rather
than in saved pass-through query.
VBA code placed On Open of report would create temporary QueryDef object
(pass-through query) using that SQL string and resulting recordset should be
passed to report. Everything shoul be done through code, without using any
saved queries or tables.
Only the last step is unclear to me, regarding passing recordset to
report...
Please, explain me how to do that, if you know.
Maybe you suggest to use db.OpenRecordset ("sqlstring").Name for
Me.RecordSource ?
Well, I'm aware that it works for JET queries, but how to implement the same
with pass-through query/statement ?

BAsically, my question is the following: How to assign temporary
pass-through query resulting recordset to a report ?

Thanks in advance.

Zlatko
 
Z

Zlatko Matic

Thanks for suggestion, but I have already tried something like that.
I tried with Me.RecordSource=db.OpenRecordset (strSQL).Name, where strSQL is
a PST sql string,
but the problem is that strSQL string is a pass-through statement, not JET,
so an error apears, because Access doesn't understand the syntax.
Then I tried with temporary pass-through QueryDef object, Set
Me.RecordSource=qdf.OpenRecordset ().Name, but it doesn't work for temporary
QueryDefs because Access can't find the query in collection, so the .Name
doesn't work...
Any other idea ?
 
Top