Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Built-in Functions _ Access Parameters -> SQL Pass Through Query

Posted by: Sean-AUS-WA Jul 11 2019, 09:29 PM

Good Morning everyone!

Unfortunately I have come to another dead end even after trawling through a significant amount of google searches and how to on the Microsoft website. After using Access as a development platform now for quite a significant amount of time, this should now be a relatively simple task but for some weird reason I just can't seem to find the proper syntax (brain block?). Its either that or this functionality probably doesn't exist (which in my experience 'everything should be possible'). Below is an illustration of what I am trying to achieve and yes I have achieved the desirable outcome BUT the current method is more cumbersome than I desire. I will note how this task is currently being executed and illustrate how it can be more efficient with your assistance! Many Many thanks in advance to whomever may know the answer to this twisty riddle!

As the topic title suggest I am trying to pass a parameter to the SQL server to a store procedure that I wrote to retrieve a record set. The stored procedure is working as intended. In the SQL server the TSQL execution code is as follow:

exec dbo.SP_Trial_Get_Data '12345678901'

Within Access I created the Pass through query using the query creator pass through Icon.. (Attached Pic 'Query Creation Pass Through'), completed the ODBC Connection String, entered the stored procedure execution string (Attached Pic: Pass through example) and saved the Query (Attached Pic: Pass through query icon). When I double click or view the data sheet on this query with a static parameter it works!! However I needed the pass through query to be more dynamic and use whichever ID that is currently in focus from the main form. So digging through my past experience and from sources online I have tried the follow codes to no avail crazy.gif If anyone can assist me with the syntax in referencing a control/field on another form within this pass through query, I will be eternally grateful!! notworthy.gif

I have tried the following:
exec dbo.SP_Trial_Get_Data '" & [Forms]![Test MAIN]![ID] & "'

exec dbo.SP_Trial_Get_Data " & [Forms]![Test MAIN]![ID] & "

exec dbo.SP_Trial_Get_Data "'" &  [Forms]![Test MAIN]![ID] & "'"

exec & "dbo.SP_Trial_Get_Data" & [Forms]![Test MAIN]![ID]

exec dbo.SP_Trial_Get_Data """ & [Forms]![Test MAIN]![ID] & """

Yes.... I have tried quite a few combination. In Access when your code is errors it usually won't even execute, some of the combination I tried does execute but retrieves no data (I am using an ID that does have data).

If there is a method for the pass through query to refer to a field within the Main form, it will save me from cycling through the following VBA every time. Which is literally checking if the Query exist and if it does, delete it and recreate it using a new query definition on every execution! Granted that this automation works but it just isn't very efficient!!! pullhair.gif

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(CurrentDb.QueryDefs("Qry_Trial").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "Qry_Trial"
End If

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("Qry_Trial")
qdf.Connect = "ODBC;DRIVER={SQL Server};,1234;DATABASE=Trial;Regional=Yes;APP=Microsoft Office 2010;Trusted_Connection=Yes;Description=TRIALSP"

qdf.SQL = "exec dbo.SP_Trial_Get_Data @CID='" & [Forms]![Test MAIN]![ID] & "'"

qdf.ReturnsRecords = True

Me.RecordSource = "Qry_Trial"

End Sub

My sincere appreciation and thanks to the expert who can assist me over this hurdle! pompom.gif



Posted by: WildBird Jul 11 2019, 10:27 PM

I dont have SQL here now, but have done this before.

You should be able to save the pass through query, and then just change the SQL property.

I would write a function, like you have, but instead of deleting the qdf, just change the SQL property. I would pass the form ID as a parameter, rather than read it from the form.


On opening of form, call this. Note Air code only. May need tweaking

ExceSQLPTH([Forms]![Test MAIN]![ID])

Function ExecSQLPTH (ByVal lngID as Long) as Boolean

dim db as dao.database
dim qdf as dao.querydef
dim strSQL as string

set db = currentdb

set qdf = db.querydefs("Qry_Trial")

strSQL = "exec dbo.SP_Trial_Get_Data '" & lngID & "'"'

qdf.SQL = strsql

set db = nothing
set qdf = nothing

End function

Posted by: AlbertKallal Jul 11 2019, 10:48 PM

Just keep in mind that ANY text in the PT query is passed 100% raw and un-changed.

So, in your test and examples, that SQL you have will NOT evaluable anything and the Forms! part you have will ALSO be sent to the server.

In a simple nutshell, this means you have to evaluate the string before you execute the query.

And also, in you case, is the ID on the SQL server table a string, or number type value?

Anyway, this code will work:

With CurrentDb.QueryDefs("qryPassR")
      .SQL = exec dbo.SP_Trial_Get_Data '" & _
      [Forms]![Test MAIN]![ID] & "'"
End With

Ok, at this point, you can now use the query, or open a form or report based on this query, or whatever.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: Sean-AUS-WA Jul 11 2019, 11:05 PM

Good afternoon WildBird and AlbertKallal,

Thanks for the feedback! Both your suggestion works wonders on this execution! Both your suggestions have made the check query delete/recreate process redundant and as such I have removed those codes. cheers.gif

That is already such a significant improvement on my laggy multilayered code from before, if anyone knows of a way to make the Pass Through Query dynamic as oppose to static, I'm sure we will all be really interested!

But as AlbertKallal has already commented, the PT query might not be alterable at all and everything within will be passed with no exception.

Thanks for your assistance guys! much appreciated.. and I hope this post will go on to helping someone else in the future! cheers!

Posted by: WildBird Jul 11 2019, 11:06 PM

I just realised I left an equal sign out of my code. Also, as Albert said, depends if it is a number or text if you need quotes around the value of ID.

Posted by: WildBird Jul 11 2019, 11:17 PM

When you say dynamic, what is changing? You can save 1 query with all connection info, and then just pass it parameters like what I have done and build SQL dynamically.

Posted by: Sean-AUS-WA Jul 12 2019, 05:38 AM

Hi WildBird,

In terms of 'Dynamic' I mean to have the PT Query referring to a control on the form without having to continuously update the query definition. The way you have both suggested has already made half my code redundant and is already quite efficient smile.gif

If there is a way to refer to a control within the PT query that would also remove the need to update Querydef prior to re-querying.

As always thank you both very much for the assistance!

Posted by: GroverParkGeorge Jul 12 2019, 09:46 AM


The answer is "no".

A passthru query is called "Pass-through" because it passes the SQL directly to the server to be processed there, exactly as it is. That means these queries CAN'T refer to controls on an Access form or report. Why? Because the Server database has no way to get to that Access form or report nor the controls on it.

You sacrifice ease (i.e. the ability to put control references in the query's WHERE clause) and update-ability of the query to gain the greater speed and power of the server-side execution. If that trade-off isn't worth it, then passthrus aren't the right approach.

Posted by: WildBird Jul 14 2019, 07:45 PM

George is right, however I see no reason why you couldnt code it to change on any change of a control. On the After_Update, you could have code that updates the query def. Would happen behind the scenes, and wouldnt notice it.

Posted by: GroverParkGeorge Jul 15 2019, 06:58 AM

Yes, I agree. I recently updated an existing personal Access Relational Database Application along those lines. I had several cascading combo boxes on it that relied on TempVars to filter down-stream combo boxes in the AfterUpdate event of the up-stream combo box. It was slow in many cases. I finally rewrote it to use Stored Procs in the server to retrieve the filtered recordsets for the down-stream combo boxes and called those Stored Procs using the method Wildbird describes. The improvement in speed was significant. Still, it is only useful because the rowsource for a combobox doesn't need to be updateable. It wouldn't be so handy in a data entry situation where the resulting recordset would need to be updateable.

Posted by: Sean-AUS-WA Jul 15 2019, 11:16 PM

Good afternoon gentlemen,

I haven't given up the hope that the possibility may exist where the pass through query can refer to a control on a form (please refer to my signature). The Pass Through Query IS maintained on access side after all and maybe it is just a feature that the developer hasn't thought of or implemented yet.

I have taken all the feedback onboard and currently updating the querydef on form open works a lot more efficiently than 'check if exist -> if yes then delete -> create new pass through -> requery'. I have also reviewed and updated the rest of the access file that was using the archaic method.

This has been quite a fruitful exercise for myself and my users and as always I appreciate everyone's input! yayhandclap.gif

Posted by: GroverParkGeorge Jul 16 2019, 08:25 AM

"I haven't given up the hope that the possibility may exist where the pass through query can refer to a control on a form (please refer to my signature). The Pass Through Query IS maintained on access side after all and maybe it is just a feature that the developer hasn't thought of or implemented yet. "

Nope, not going to happen, for the reason I previously explained. And no, the people who own SQL Server have no incentive to try something like that. It's not worthwhile. I'm sure they've heard questions many, many times about it. Access has been around since 1993 and the predecessor to SQL Server even longer. There's not much they haven't thought about or been asked about.

Although we are Access-centric here at UA, there are many, many other front end interface development environments which need to pull data from SQL Server (and multiple other RDBMSs) and they all follow the same model. Access is, in fact, somewhat unique in even offering the bound form interface. Most of the time, all data handling has to be done independently of presentation in an interface, using mechanisms similar to the pass-thru.

We did have, in fact, something similar in the past with the ADP. Unfortunately, they were dropped a couple of versions back.

In short, this might be a case where the more productive use of resources is to pursue, and become proficient with, the methods that do work.