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
> Access Parameters -> SQL Pass Through Query, Access 2016    
 
   
Sean-AUS-WA
post Jul 11 2019, 09:29 PM
Post#1



Posts: 55
Joined: 21-April 16
From: Perth, Australia


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:

CODE
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:
CODE
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

CODE
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};SERVER=RandomServer.au,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


Attached File(s)
Attached File  Pass_Through_Query_Icon.JPG ( 8.3K )Number of downloads: 0
Attached File  Pass_through_Example.JPG ( 12.18K )Number of downloads: 0
Attached File  Query_Creation_Pass_Through.jpg ( 44.38K )Number of downloads: 0
 

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
WildBird
post Jul 11 2019, 10:27 PM
Post#2


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


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.

e.g.

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

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

CODE
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

db.close
set db = nothing
set qdf = nothing

End function




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
AlbertKallal
post Jul 11 2019, 10:48 PM
Post#3


UtterAccess VIP
Posts: 2,836
Joined: 12-April 07
From: Edmonton, Alberta Canada


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:

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

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com


Go to the top of the page
 
Sean-AUS-WA
post Jul 11 2019, 11:05 PM
Post#4



Posts: 55
Joined: 21-April 16
From: Perth, Australia


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!

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
WildBird
post Jul 11 2019, 11:06 PM
Post#5


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


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.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jul 11 2019, 11:17 PM
Post#6


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


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.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
Sean-AUS-WA
post Jul 12 2019, 05:38 AM
Post#7



Posts: 55
Joined: 21-April 16
From: Perth, Australia


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!

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 09:46 AM
Post#8


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


PMFJI.

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.
This post has been edited by GroverParkGeorge: Jul 12 2019, 09:47 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Jul 14 2019, 07:45 PM
Post#9


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


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.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
GroverParkGeorge
post Jul 15 2019, 06:58 AM
Post#10


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


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.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Sean-AUS-WA
post Jul 15 2019, 11:16 PM
Post#11



Posts: 55
Joined: 21-April 16
From: Perth, Australia


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
This post has been edited by Sean-AUS-WA: Jul 15 2019, 11:17 PM

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
GroverParkGeorge
post Jul 16 2019, 08:25 AM
Post#12


UA Admin
Posts: 35,534
Joined: 20-June 02
From: Newcastle, WA


"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.
This post has been edited by GroverParkGeorge: Jul 16 2019, 10:01 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 11:42 PM