UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pass-through Query Vs Linked SQL View - Help, Access 2016    
 
   
MS123
post Mar 2 2018, 11:01 PM
Post#1



Posts: 53
Joined: 28-November 16



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
This post has been edited by MS123: Mar 2 2018, 11:26 PM
Go to the top of the page
 
MadPiet
post Mar 3 2018, 12:59 AM
Post#2



Posts: 2,421
Joined: 27-February 09



Would probably work best if you wrote a simple stored procedure in SQL Server, and added parameters, so that you return the fewest records possible to Access. This is especially true if you are connecting to a SQL Server instance that is not on your local machine. (Because any data you retrieve will have to travel across the network).

Here's a good article on creating passthrough queries from MSSQLTips
https://www.mssqltips.com/sqlservertip/1482...-to-SQL-server/

This is a handy article too:
https://support.microsoft.com/en-us/help/30...query-in-access

You can create a simple stored procedure in SQL Server (if you have permissions to do it), and then call that.

Here's a really simple example of a stored procedure against the Movies database that WiseOwl use for their SQL Server tutorials (they're all on youtube)
https://support.microsoft.com/en-us/help/30...query-in-access

Here's a simple stored procedure to get all movies longer than @MinRunTimeMinutes minutes:

CODE
CREATE PROC [dbo].[getFilmsLongerThanXMinutes]
    @MinRunTimeMinutes INT
AS
SELECT FilmID
    , Title
    , Review
    , RunTimeMinutes
FROM dbo.Film
WHERE RunTimeMinutes>=@MinRunTimeMinutes;


Then you can create your pass-through in Access, and change the code to something like this:

CODE
EXEC [dbo].[getFilmsLongerThanXMinutes]
        @MinRunTimeMinutes = 150;


Go to the top of the page
 
MS123
post Mar 3 2018, 01:08 AM
Post#3



Posts: 53
Joined: 28-November 16



That is excellent information, thank you, I will study it. And yes SQL is on a different server to where the front end Access db is.

So for my understanding using a combination of stored procedure and pass through query would:
Pass form-based parameter from Access to SQL SP via Access PTQ, retrieve results into Access from the SP.
And even though this is passing data from Access to SQL and back to Access again it would give better performance than only passing data from SQL to Access via SQL view?
Go to the top of the page
 
GroverParkGeorge
post Mar 3 2018, 08:04 AM
Post#4


UA Admin
Posts: 32,834
Joined: 20-June 02
From: Newcastle, WA


I would have to say that, like so many things, it may depend on specifics that are hard to judge in the abstract.

A parameterized Passthru calling a stored proc is likely to be a good choice because of the ability to limit returned recordsets. The drawback to that approach is that the resulting dataset is ALWAYS non-updatable. So, if you must have a read/write recordset, it won't do.

I've found that up to a certain point, I really can't tell much of a difference, but for very large recordsets, performance is likely to become a factor.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 08:29 PM