[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Pass-through query Question

I have a passthrough query which in order to work needs a parameter sent to it to filter criteria based on value(s) on various forms.

I got the passthrough query to work with no criteria --- and then I created a second query pulling the passthrough in as the table/source for the second query and applied the criteria that way.

Do you see any problem with that? I want to filter the contents of combo boxes based on values in other fields and thought a passthrough query would be the way to go -- I know I can create code to create the queryDef -- but wondered if what I'm proposing makes sense.

It works, but just wondered if it's an acceptable method.

Thank you.
0
Eileen Murphy
Asked:
Eileen Murphy
  • 6
  • 6
  • 5
  • +3
1 Solution
 
jmoss111Commented:
makes more sense to parameterize on the server than returning all the rows and filtering. its not really a big deal to build a sql statement for dynamic  or to supply a param for a sp.
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
You're right -- that makes sense. So, to dynamically populate all the combo boxes I need to write code to do that instead. I think I'll be writing a lot of code - unless there is a shortcut you know about :)
0
 
dqmqCommented:
Any reason why you don't link to the backend tables?  Then you can reference them directly in your comboboxes and you can filter one combo box based on the selection in the other.   Passthru queries is the long way around the block.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
jmoss111Commented:
pass through queries are the quick way around the block; is the info for the combo boxes fast or slow changing data? give us a little more info. I can give you some good examples of using forms to build dynamic sql or to populate params for sp's.
0
 
jmoss111Commented:
filters make me cringe
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Some combo box data is static and some dynamic - when records are added, etc. All forms have record selection combo-boxes on them...
0
 
Rey Obrero (Capricorn1)Commented:
you can alter the sql  of the PT query to include the parameter values using vba codes.

see similar thread

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26679480.html#a34346825
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
... and to populate the combo boxes??
0
 
Rey Obrero (Capricorn1)Commented:
Ei0914
<... and to populate the combo boxes??>

you just alter or add filter to the rowsource of the combo box
0
 
dqmqCommented:
>you can alter the sql  of the PT query to include the parameter values using vba codes.

Yeah, but who wants to populate the combo boxes in code...very messy.  

Any reason you can't link to the backend tables; that's much easier than the passthrough route.    



0
 
jmoss111Commented:
how many comboboxes? its just a select statement per. and why do you want to use pass through queries other than the obvious?
0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Here's the deal: I have a client with a FE/BE Access app -- running on a Terminal Server -- and they've experienced significant slow-downs -- when more than 2 users log in.  I am thinking maybe SQL Server will help with that.

We've researched the potential problems with 64-bit Windows Server 2008 - but are at a loss as to what to do -- I thought that pass-through queries would push much of the processing to the SQL Server itself and perhaps improve things...

All the combos are Select statements -- and most have filters based on values in other form fields throughout the application. Where possible I created temp tables for the data that was pretty static to eliminate that traffic across the network  -- much of the data is dynamic and multiple users are updating data simultaneously.

I've posted a few questions about this problem over the past couple of months -- and by making those temp tables have improved things a little -- not enough to make them happy.

So -- I guess I need to know -- are pass-throughs a good way to go?-- and if so -- does anyone have  reusable code I can use ?? -- I am working with some of the samples you've sent so far -- none seem to be the best fit --

I'm working to make them work...
0
 
Anthony PerkinsCommented:
Your best bet, if you are going to use SQL Server appropriately is to create Stored Procedures and then write ADO calls to retrieve the data using these Stored Procedures.  That should prove the fastest approach.
0
 
dqmqCommented:
There is no doubt that passthru queries push dbms processing back to the server.  You benefit performance-wise because the server is more powerful; you benefit network-wise because less data is pushed to the client.  

But there are disadvantages, as well. Foremost, they are not easily parameterized. They are very good for populating recordsets; they are not so good for interfacing with the controls on an Access form.   Combo boxes come to mind.   Combo boxes generally return small amounts of data and don't require significant amounts of client processing.  Suppose you link to a backend table and make that the rowsource of a combo box.  That's slightly less overhead than a passthru query which returns the same table.  Now let's say we want to parameterize that rowsource query so that it only returns some of the rows based on the value of another combobox.  With linked tables, the query parameters are resolved on the client and the query is sent to the backend for processing.   Filtering occurs server-side and only the selected rows are returned to the client.  

Now if you have a query that joins a server table to a local table; that's another matter.  The entire server table must be forwarded to the client and the join performed there.  As undesireable as that may be, there is no way to even accomplish the same thing using a passthru query.





   

 
0
 
Alpesh PatelAssistant ConsultantCommented:
Yes, you can do it.

Select Fields From (Select * From Table) T Where T.Fields = 'Test'
0
 
dqmqCommented:
But, if the passthru query is:
 Select * from Table

And the rowsource is:
  Select Fields from PassthruQuery where Fields='Test'


Then the entire Table is returned over the network and filtered on the client.

However if the table is linked and the rowsource is:
  Select Fields from LinkedTable where Fields='Test'

Then Table is filtered on the server and only the requested rows are returned over the network.
0
 
jmoss111Commented:
not quite; the passthrough reads the form field and uses it as a parameter or at least it does the way i do it.
0
 
jmoss111Commented:
the querydef gets rewritten using vba with the data from the form fields
0
 
Rey Obrero (Capricorn1)Commented:
with dynamic parameters, you have to use vba to alter the querydef of the PT.
PT query works with hard coded values parameters.
0
 
dqmqCommented:
>not quite; the passthrough reads the form field and uses it as a parameter or at least it does the way i do it.

I would like to know how you do it then, because AFAIK passthru queries do not support parameters.

I understand that the SQL of a passthru query can be modified on-the-fly, but that is not the same thing as a parameterized query.  And it's not a particularily good practice either, as the querydef is then constantly changing and not reusable in the sense most queries and other database objects are reusable.

But my main point is this: why go through all the coding gymnastics of dynamic passthru queries when you can get the same result far easier with parameterized queries against linked tables?

0
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
I think I'm more confused that I was before - and that never usually happens when I post questions on here -- Can someone maybe clear some of this up for me?
0
 
dqmqCommented:
Sorry to get you confused, if I contributed to that.  Passthru queries run exclusively on the database server. As a result there are limitations to how well they interface with an Access form.  You can do some amazing things, for sure, but there is extra work involved.

In your case, you want combo boxes that are "filtered" based on other combo boxes.  Further, you want the filtering to occur on the server for performance reasons. That's a very common drill down design in access and it's very easy to implement with Access tables.  It can also be done with passthru techniques, just not as simple.

With linked tables:
  The rowsource of the combobox1 is something like this:

     select * from mytable where myfield = forms!myform!ComboBox2

  In essence, comboBox1 has a parameterized query based on the value selected in combobox2

  In theafter_update event of combobox1 you issue this command:
     
          me.combobox2.requery


With passthru queries;
  The rowsoure of combobox1 is the name of a passthru query.   Take care because you cannot use that passthru query for anything else.
  In the form load event you need to dynamically build your SQL and put it in the passthru query.
  Then you need to requery the combobox
  In the after_update event of combobox1, you then modify the SQL in the passthru query before requerying combobox2.  In this case, you are using dynamic SQL, which is a little more work than paramerized SQL:

    dim db as database
    dim qd as querydef
    set db = currentdb()
    set qd = db.querydefs("yourqueryname")
    qd.SQL = "Select blah, blah, blah...where yourfield ="  me.ComboBox1
    me.Combobox2.requery
 













 









 
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now