Question Pass-through query vs stored procedure vs SQL view

[MS1234]

Hi, I've been programming in Access for umpteen years but have never looked at pass-through queries before. I want to see if it will optimize my db performance in any way.

I have multiple SQL tables/views linked via ODBC to my front end Access db.

As a typical example:

SQL view written to pull data from multiple SQL tables.
Link SQL view via ODBC to Access db.
Query written in Access using the linked SQL view, with additional manipulation on the Access query to restrict records returned (eg Vendor = forms!Vendors.VendorID).

This is how I have the majority of my Access queries - pulling a SQL view then restricting data output based on values selected on forms.

So my question is - would Pass-Through queries be a better option for these, and if so how would it deal with the form based parameters.

Alternatively would stored procedures be a better bet for me? I've never used these either but I would imagine you can write the stored procedure, pass the parameter to it from access (eg Vendor = forms!Vendors.VendorID), the stored procedure returns the relevant data as the result in Access.

Thanks

 

[BigHappyDaddy]

Access attempts to "pass through" every query so although pass-through queries are slightly more efficient because Jet/ACE doesn't have to process them first, the data retrieval should be pretty much the same.

I was surprised to read this only because in my previous workplace, pass through select queries were MUCH faster than the same query in Jet/ACE using linked tables. Especially on larger tables (100,000+ records per table).

Am I misunderstanding something when comparing pass through queries versus "local" queries on licked tables???

I created pass through queries for either SQL Server or AS400 databases.