How to Use a Pass Through Query in MS Access - SQL Server Example

How to Use a Pass Through Query in MS Access - SQL Server Example
In this episode, we’ll look at how to create and use a Pass Through query in MS Access to run our query efficiently on the server, and to only receive the result set we want. We’ll look at how to use Pass Through queries with standard SQL and SQL stored procedures. These queries can help your Access project become more efficient, and able to connect to more diverse data sources.

Related Videos:
How to Use a Pass Through Query in MS Access - SQL Server Example
You are watching this video now!
How to Simulate Lag and Lead Window Functions
How to Filter by Month in MS Access Queries, Forms, and Reports
How to Use Reference Expressions to Get Form Values in MS Access
How to Create String Aggregates in Microsoft Access

Want to work with me 1:1? Contact me today and book your free 20 min consultation!

Follow me on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

For developers looking for a new role, check out:

Needing to hire technical resources for your project? Get the best:

ms access pass through query, pass through query, pass thru query, access odbc pass through, access sql server pass through query, vba pass through query

#PassThroughQuery #msAccessQuery #SQLpassthroughQuery

[seanmackenziedataengineering]

Just passing through? :-D So, what was it that made you choose a pass through query in your project? Performance bottleneck?

[terri]

Exactly what I was after, this channel is brilliant for those old access people moving to sql, thanks heaps :)

[Tiger]

Just what I needed! Pulling 22k rows of data (out of a 100K+ table) into Access from a read-only SQL Server connection is taking 18 minutes. I cannot wait to try this. 👍

[ermacheton]

Thank you so much for your video; The issue now is when I migrate the application to a new SQL server, I need to update the ODBC connection on every query to reflect the new SQL SERVER name. Any suggestions? thanks so much

[MrKenCollectibles]

I'm new to using MS Access and this information helped me tremendously. Do you have any videos about creating expressions specifically around date functions. I need to find a way to update a field with a new date when another field is changed.

[IsbAdmissions]

I have a pass through query which finds the opening balance from few SQL tables processed in SQL. but sometime it seems that the passthrough query is not showing all the balances hence when the query is run different times I get different results. please let me know if there is a way to see if the query has run or nut (VBACODE)

[chuncanisima]

Thanks you so much Sean!!! Is there anyway run a stored procedure, using a passthrough query, that has input and output parameters? if so, how can I retrieve the output parameter value in Ms access?

[doncomon]

Thanks for your videos Sean. I work in SQL a lot, not as much in access. I have a sql stored procedure call in access that is working fine but I need to make a change to it. I am adding date parameters in the call, but I need time included. I am going to use a form that they input the begin and end date and then want to pass that in the stored procedure call. I am amending the access program. I was not the original developer. When I try to pass the datetime value to then be used in the stored proc I get nothing on the sql side. From what I understand Access does not really have datetime values. I try to pass the date as #06/10/2024# or "06/10/2024" and I get Dec 30 1899 12:00AM in SQL. I can't pass '06/10/2024' it give me a compile error. Should I convert to string pass it and convert back in SQL Proc, or is there another way.

[robertjones]

I’m late to the party, but can this technique be used to set the record source property on a form?

I’ve just recently started migrating Access databases to SQL server

[aramgharib]

I am migrating a whole bunch of Access applications to SQL Server. Quite often the forms and reports of applications are (rather poorly, IMHO) designed based on RecordSources like "select * from table_x where column_y = Forms!form_z!textBox_t". Whatever solution I implement on the SQL Server side, I'd need to change the query on-the-fly, as you explain here, and requery, right?... No other ways to "dynamically inject" the Forms!... value, then?!

[tangan]

What if i don't have the authority to create procedure?

[josepmirimorales]

Is it possible to change o define the connection to a pass trought query with vba? If is possible, could you please make a video explaining how to do this? Thanks for your help.