Setting recordset/recordsource runtime

[Trygve Lorentzen]

Hi,

I have an Access mdb with tables linked to a MySQL db through ODBC. Since
the Jet DB engine is extremely slow with these data I would like to use a
pass-through query in my report. The code to open the report from a
commandbutton on another form is:

sqlWhere_One = " aktiviteter.AKTIVITETSID = " &
Me.ds_Aktiviteter.Form.AKTIVITETSID & " AND personer.PERSONID = " &
Me.ds_Aktiviteter.Form.personid

DoCmd.OpenReport strDocName, acPreview, , sqlWhere_One

The report uses the pass-through query. Ok, while this work it is still
very, very slow since it obviously first opens the whole recordset (~15000
records) and THEN sets the where condition. If I code the where condition
directly in the SQL query it works fast, but then the where condition set by
OpenReport has no effect (naturally).

My next approach was to build up the SQL string runtime and create a
ADODB.RecordSet, open the report with no recordsource set and then set the
reports RecordSet propery to the already openede RecordSet. All I get is a
message that ".mdb does not support this propery" or something similar.

Please help!

Cheers,
Trygve

 

[Duane Hookom]

I would create a generic pass-through query to MySQL. Then prior to opening
the report, use DAO to change the sql of the pass-through.
CurrentDb.QueryDefs("qsptMyReport").SQL = "SELECT... WHERE " & _
"aktiviteter.AKTIVITETSID = " & Me.ds_Aktiviteter.Form.AKTIVITETSID
& _
" AND personer.PERSONID = " & Me.ds_Aktiviteter.Form.personid
DoCmd.OpenReport strDocName, acPreview

 

[Trygve Lorentzen]

Thank you so much, it works really well!

Cheers,
Trygve