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

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

@seanmackenziedataengineering

Жыл бұрын

Glad you enjoy it!

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

5 ай бұрын

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!

Very Nice Explanation..

@seanmackenziedataengineering

Жыл бұрын

Thanks and welcome!

Excelent explanaion. Thanks

@seanmackenziedataengineering

3 жыл бұрын

Glad it was helpful!

awesome video!

@seanmackenziedataengineering

2 жыл бұрын

Glad you enjoyed it! Thanks for watching, cheers

excellent work

@seanmackenziedataengineering

Жыл бұрын

Thank you! Cheers!

thanks

@seanmackenziedataengineering

Жыл бұрын

You're welcome!

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: kzread.info/dash/bejne/e3xmtLWLmMfKhtI.html

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

11 ай бұрын

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

11 ай бұрын

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

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: kzread.info/dash/bejne/aXyXzbujeMLdl5c.html

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

2 жыл бұрын

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

@josepmirimorales687

2 жыл бұрын

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

@josepmirimorales687

2 жыл бұрын

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

2 жыл бұрын

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

@seanmackenziedataengineering

2 жыл бұрын

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

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: kzread.info/dash/bejne/h4KK2bGcgJfcetY.html Query Migration: kzread.info/dash/bejne/iq1ssbavaMueo5c.html 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!

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

11 ай бұрын

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

@seanmackenziedataengineering

11 ай бұрын

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.

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

@seanmackenziedataengineering

2 жыл бұрын

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