Passing Filters from Access Front-End is Slow. 1

[perrymans]

I have a Access front-end to a MySQL database over the internet.

I use a report system that allows users to define some custom query parameters.

These are Jet queries because of the need to allowing filtering like this. I have not been able to get this to work by using a Pass-Through query, which appears to need to be set.

SELECT * FROM SelQ_ChapterMailingList

SelQ_ChapterMailingList is the Jet query, is the recordsource of the report opening and the above is actually defined in a table and filters a report.

The query and the above run very quickly. However, the following does not:

SELECT * FROM SelQ_ChapterMailingList WHERE (((StatusID)=1) AND ((EmailPreferred)=False))

Suddenly, I go from a couple seconds to a couple of minutes!

Again, SelQ_ChapterMailingList is the reports recordsource, and I set the above to the Access FilterName property of the OpenReport command.

IF I REMOVE THE LEFT AND RIGHT JOINS FROM SELQ_CHAPTERMAILINGLIST, IT WORKS QUICKLY AGAIN (see bottom for that query).

Is there a better way to accomplish this with MySQL?

Thanks. Sean.



SelQ_ChapterMailing List looks like:

SELECT Tbl_Members.StatusID, Tbl_Members.ChapterNumID, [FirstName] & " " & [MiddleName] & ". " & [LastName] AS MemberName, Tbl_ListDuesYear.DuesYearSimple, Tbl_Members.MemberNr, Tbl_ListMemberType.MemberType, Tbl_Members.[Home-Address], Tbl_Members.[Home-City], Tbl_Members.[Home-State], Tbl_Members.[Home-Zip], Tbl_Members.LastName, Tbl_Members.EmailPreferred
FROM (Tbl_ListMemberType RIGHT JOIN Tbl_Members ON Tbl_ListMemberType.MemberTypeID = Tbl_Members.MemberTypeID) LEFT JOIN Tbl_ListDuesYear ON Tbl_Members.DuesYearID = Tbl_ListDuesYear.DuesYearID
ORDER BY Tbl_Members.LastName;

 

[perrymans]

Nevermind.

My problem was fundamental with adding List tables to my query to populate the report with the value.

Duh!

I just added a combobox to the report with a recordset of the list (like cboMemberTypeID pulling from Tbl_ListMemberType).

Now the underlying query is built from a single table and the reports are opening super fast!

Thanks. Sean.

 

[DonQuichote]

To make it a pass-through query:
save the query (that is, use an entry in the queries tab, not just SQL in the form or report), open that query in design mode and right click on the graphical representation. Now choose "pass-through query" from one of the submenues.

A query with out a where clause will not make much of a difference, but one with a where clause will: a pass-through query does all the filtering in the database server, reducing the network traffic needed to get the rows to your machine.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)