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
> Issue With Pass Through Query, Access 2016    
 
   
ordnance1
post Aug 14 2019, 03:13 PM
Post#1



Posts: 668
Joined: 7-May 11



Cannot get my pass through query to work and the issue is with the where statement. If I substitute the TempVars!UserName with a value it works as expected. Ran a debug.print on the TempVar and it returns the proper value.

CODE
Select Password  from Users WHERE Users.UserName = "& TempVars!UserName &"
Go to the top of the page
 
GroverParkGeorge
post Aug 14 2019, 03:19 PM
Post#2


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


A passthru query runs ONLY in the server to which it is passed (hence the name pass through, or passthru). Access doesn't try to optimize that SQL. It just sends it. The server, on the other hand, has no way to know what Access means because it can't see that tempvar in Access.

Tempvars are ACCESS ONLY. They do not exist in the SQL Server world (I assume your passthrus are going to SQL Server, but the same would hold for any server based database).

So, you will have to dynamically rewrite the SQL in the passthru to include the value of the tempvar. This is done in VBA.

Do you need help doing that?
This post has been edited by GroverParkGeorge: Aug 14 2019, 03:21 PM

--------------------
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
 
ordnance1
post Aug 14 2019, 04:43 PM
Post#3



Posts: 668
Joined: 7-May 11



I appreciate your reply. You are correct it I am interacting with SQL Server.

I probably will need help but should at lease try spending some time in google before bothering you or others.
Go to the top of the page
 
theDBguy
post Aug 14 2019, 04:54 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,315
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just as a hint, try Googling "QueryDef" objects. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
AlbertKallal
post Aug 14 2019, 06:27 PM
Post#5


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


All you have to do is “evaluate” the result into a valid SQL string and it will work fine.

Eg this:
CODE
   With CurrentDb.QueryDefs("qryPassR")
      .SQL = "Select Password  from Users WHERE Users.UserName = '" & TempVars!UserName & "'"
   End With
  
  ' now launch a report, form  based on above query will work fine.


At this point, you can open the query in VBA, or launch a report based on at that PT query.

You can open a recordset like this:

CODE
   Dim rst        As DAO.Recordset
  
   With CurrentDb.QueryDefs("qryPassR")
      
      .SQL = "Select Password  from Users WHERE Users.UserName = '" & TempVars!UserName & "'"
      Set rst = .OpenRecordset

   End With


So, you have to “build” or evaluate the expression from that variable (tempvar) before you send it to SQL server.


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

Go to the top of the page
 
ordnance1
post Aug 15 2019, 09:39 AM
Post#6



Posts: 668
Joined: 7-May 11



Thanks to all for your help. It was like a trip to the dentist, but is working now.
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 09:47 AM
Post#7


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


Glad to hear you resolved the problem. Now that you've done this once, the next one will be easier.

Continued success with your project.

--------------------
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    19th September 2019 - 04:10 PM