Pass-through query Question

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.

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 :)

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.

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.

filters make me cringe

Some combo box data is static and some dynamic - when records are added, etc. All forms have record selection combo-boxes on them...

you can alter the sql  of the PT query to include the parameter values using vba codes.

see similar thread

https://www.experts-exchange.com/questions/26679480/Help-needed-with-a-Pass-Through-Query.html?anchorAnswerId=34346825#a34346825

... and to populate the combo boxes??

Ei0914
<... and to populate the combo boxes??>

you just alter or add filter to the rowsource of the combo box

>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.    

how many comboboxes? its just a select statement per. and why do you want to use pass through queries other than the obvious?

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...

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.

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.





   

 

Yes, you can do it.

Select Fields From (Select * From Table) T Where T.Fields = 'Test'

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.

not quite; the passthrough reads the form field and uses it as a parameter or at least it does the way i do it.

the querydef gets rewritten using vba with the data from the form fields

with dynamic parameters, you have to use vba to alter the querydef of the PT.
PT query works with hard coded values parameters.

>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?

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?