MS Access, Pass through query with complex criteria. Criteria include Select statments and vba functions.

I currently have multiple queries that query data from a few tables linked through ODBC, and some temporary tables that are edited through the user interface. I have complex criteria in my queries such as:
SELECT * from ThingsData WHERE (Thing In(SELECT Thing from ListOfThings) AND getThingFlag() = True); In this case Thing is a field and ListOfThings is a temporary table that the user defines from the user interface. Basically, the user puts together a list of the field Thing that he/she wants to filter the data based on and I want to query only the data that matches the Thing values that the user adds to his/her list. Currently, the data I am querying is in the linked ODBC table, and the temp table ListOfThings is just a regular, local table and everything works peachy. I want to get rid of the linked table and use a pass through query instead. However, when i do that, unless the criteria is incredibly simplistic, i get an error:

"ODBC--Call Failed. Invalid object name ListOfThings."

If I dont have any criteria it works fine.

Long story short: In a pass through query, how do I apply criterias that include SELECTs and functions from my modules and just basically filter the pass through table based on data from my local tables?

Im not quite sure what the server syntax is, but your comment got me thinking. Is it true to say that anything in this query will be executed on the server side and then "passed through" (now understanding the name) to my database? This would suggest that i cant even use my vba module functions or even access my local tables in a pass through. Is this correct?

Matt 2009-11-10 19:37:24

btw, right now I am working on possibly just querying * from the tables as pass throughs and then making a temp table of the query and then going from there. this is giving me lots of problems too and it seems like weak quick fix

Matt 2009-11-10 19:38:46

It's the other way around. The query is passed through to the server and executed there. Data always comes from the server to the client, regardless of where the query is executed. The advantage of a pass-through query is that less data has to be sent to the client and the server has full information and can make the most efficient query execution plan.

Michael Dillon 2009-11-10 19:57:09

You can use the VBA functions if you open the query object that contains the pass-through query. In that case, your VBA code will only see the result table sent back through ODBC.

Michael Dillon 2009-11-10 19:58:40

so does that mean i need to run the pass through query with everything but my vba and then run another query on that data to apply all the vba type stuff?

Matt 2009-11-10 20:24:25

If you need to use VBA functions in your queries then you can't put that in the pass-through query so you would have to do that kind of processing later. However, some VBA functions will have equivalents in the database server's SQL dialect. Of course, getThingFlag() seems like your own function so you would have to filter that after the pass-through like so SELECT * from MyPassThroughQueryWHERE getThingFlag() = True;

Michael Dillon 2009-11-11 01:10:57

ansaurus

tags:

views:

answers:

MS Access, Pass through query with complex criteria. Criteria include Select statments and vba functions.

related questions