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 Simulate Lag an...
How to Filter by Month in MS Access Queries, Forms, and Reports
• How to Filter by Month...
How to Use Reference Expressions to Get Form Values in MS Access
• How to Use Reference E...
How to Create String Aggregates in Microsoft Access
• How to Create String A...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
For developers looking for a new role, check out:
www.toptal.com...
Want my team to do a project for you? Let's get to it!
system.billzon...
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
• How to Use a Pass Thro...

[@seanmackenziedataengineering]

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

[@HeyTezza]

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

[@seanmackenziedataengineering]

Glad you enjoy it!

[@07Tiger1050]

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

[@seanmackenziedataengineering]

Awesome! Yes, a pass-through will simply get the result-set and leave all the rest of the table on the server.. as it should be!

[@abderazzaq.es-seddyqy]

This is amazing, exactly what I needed. Thank you

[@seanmackenziedataengineering]

Awesome, glad to hear 🛠

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

[@seanmackenziedataengineering]

Great question - I don't think I ever used Pass Through queries for SP output parameters. Since pass-throughs are generally targeted at a result-set output and you might have several output parameters, I'm going to guess that the best way is to use an ADODB command. Good topic for a video, thanks!

[@chuncanisima]

Thank you!!! if you do a video on the topic I will be the first to watch it and like it!!

[@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

[@seanmackenziedataengineering]

Interesting problem! You can set these programmatically, so just make yourself a nice module with a Sub in it where you set the odbc connection in one variable and then set the querydef sql for all of your queries. This might help: kzbin.info/www/bejne/l4Suip2NhJiNgLM

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

[@seanmackenziedataengineering]

You bet! Check it out: kzbin.info/www/bejne/f3mVg4aIncmXjNE

[@KuldeepSingh-nq1vi]

Very Nice Explanation..

[@seanmackenziedataengineering]

Thanks and welcome!

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

[@seanmackenziedataengineering]

Good question. I assume you're setting the query in the passthrough on-the-fly. If you're using a datetime, I would probably convert it to ISO, like '2024-07-25': Exec MyStoredProc 'ABC', 123, '2024-07-25', '2024-09-30'; I haven't tested that in so long - let me know how it goes!

[@megamedia2827]

im create passthrough for SP delete, but for trial record range 10.000 and 15.000 data error , any solution

[@seanmackenziedataengineering]

You may need to grant your user permissions to run the SP. Grant Execute on sp_delete_stuff to MyUser

[@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)

[@seanmackenziedataengineering]

Interesting. If you run the query in SQL (SSMS) at different times, does it always have all of the entries? If you put the query into a recordset and then cycle through it, that will tell you more about it. When you just double-click to open and view a query, Access only loads a page. You can also put your cursor into the open datasheet and scroll down using the down arrow to "access" each record. Just do something like this, but open your pass through instead: kzbin.info/www/bejne/bXnGnIygfcSqnZY

[@E-TicketingMultan]

excellent work

[@seanmackenziedataengineering]

Thank you! Cheers!

[@robertjones9067]

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

[@robertjones9067]

Actually, I just found another comment that addresses the issue, thank you very much for this video. Very helpful.

[@seanmackenziedataengineering]

Cool - I saw you found a solution. Basically if you're migrating Access databases to SQL Server, you don't need to change (most of) the record sources for the forms. Just convert the DAO tables to linked tables. ODBC is pretty smart and it will generally do a lot of the "efficiency" stuff for you. Only in some cases do you need to use pass-throughs for migration.

[@josepmirimorales687]

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.

[@seanmackenziedataengineering]

Great idea for a video! I'll take a look.

[@josepmirimorales687]

@@seanmackenziedataengineering , I am looking forward, thank you for your help

[@josepmirimorales687]

@@seanmackenziedataengineering , I have a problem with the order of columns from a pass trouhgt query that executes an sql-server stored procedure, the best solution would be to kill the query and create it again with vba, but I do not know how to creat a pass trough query in VBA

[@seanmackenziedataengineering]

@@josepmirimorales687 I’m gonna take a look tomorrow!

[@seanmackenziedataengineering]

@@josepmirimorales687 You can set a new connection in the Immediate Window (or in code). You can find your connection by going: ?Currentdb.QueryDefs("MyQuery").Connect then change it using: Currentdb.QueryDefs("MyQuery").Connect = "ODBC;Driver... etc" You can refresh columns by re-running the query against the server, and set the SQL to a different stored procedure using: Currentdb.QueryDefs("MyQuery").SQL = "Exec sp_mystoredprocedure"

[@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?!

[@seanmackenziedataengineering]

The situation you describe sounds like the design uses DAO, which includes typical MS Access table objects etc. This is actually a good thing. If you migrate the tables to SQL Server or Azure SQL, you can create Linked Tables in the front-end with exactly the same names as the old tables, and the application should function exactly as before, with (almost) no changes to the front-end forms etc. DAO + ODBC will handle the parameterization of the form's source over ODBC to the SQL Server, like you say, select * from table_x where column_y = Forms!form_z!textBox_t See these two, DAO + ODBC: kzbin.info/www/bejne/i3-5qIKZhZmpgNU Query Migration: kzbin.info/www/bejne/jqqbgIesbc1rqZY The Azure migration is identical to the SQL Server migration.

[@aramgharib]

@@seanmackenziedataengineering Thank you for your feedback. And, yes... but!... I simplified the situation: in reality many reports are based on rather complicated queries that result in large amounts of data. What would be nice could be that the restricting where-clause be applied at the sources- i.e. at SQL Server level; exactly as you do with CountryName and SeriesCode in your example. But the values being bound to dynamic containers in Access runtime space (e.g. Forms!form_x!textbox_y), the automatic "injection" by Access doesn't seem doable. Fortunately most of these cases occur in queries that are used as RecordSource of Reports/Forms so that I might be able to (inspired by your example) call some InjectDynamicParameters(queryName, dynamicParameters()) subroutine in an OnOpen Event Handler in order to replace placeholders in the sql string of the passthrough query with the actual values. Same might be done for OnGotFocus Handler of Combo/ListBoxes using such queries as RowSource... I was hoping that some magical work-around did exist!

[@seanmackenziedataengineering]

@@aramgharib Interesting issue! Good work - it sounds like you have an approach. Many ways to attack one problem!

[@claudiosilva7697]

Excelent explanaion. Thanks

[@seanmackenziedataengineering]

Glad it was helpful!

[@AndreaRodriguez-mj2os]

awesome video!

[@seanmackenziedataengineering]

Glad you enjoyed it! Thanks for watching, cheers

[@huyute3636]

thanks

[@seanmackenziedataengineering]

You're welcome!

[@tangan3893]

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

[@seanmackenziedataengineering]

If you don't have authority to create a procedure, try giving your Create Procedure SQL to a database admin to run. After they create it (and perform GRANT EXECUTE for you), you can use it in your pass through. If this is denied, you will have to create your logic in Access. This can be done; depending on the complexity, you may need some local temp tables, or maybe just a query string (for easier procedures).