[AccessD] Access 2000 Pass-Through Query Reports

I'm having the same problem. 
Since pass-through queries send the unparsed SQL directly to the ODBC
driver, how is it possible to use a custom VBA function in them? 
I'm confused.

 -----Original Message-----
From: KSEABOLT at parkcenter.org [mailto:KSEABOLT at parkcenter.org]
Sent: Tuesday, September 09, 2003 12:25 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Access 2000 Pass-Through Query Reports




OK.  What am I doing wrong?   When I try to set the passthru SQL this way I
get an error.   "Incorrect syntax near '&'. (#170) 

Seems like that's why we abandoned this approach before - because all we
could get to work in passthru was a literal string containing syntactically
correct T-SQL.   Is there a setting I can toggle somewhere or some other
syntax I can try? 

Thanks. 

- Ken 





	"Arthur Fuller" <artful at rogers.com> 
Sent by: accessd-bounces at databaseadvisors.com 


09/09/2003 02:55 PM 
Please respond to Access Developers discussion and problem solving         


        
        To:        "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com> 
        cc:         
        Subject:        RE: [AccessD] Access 2000 Pass-Through Query Reports



My fave way to do this is to use a static function that sets/gets the parm
of interest. (God I must sound like a very squeaky wheel on this subject :-)

  
Assuming a get/set func that sets and gets a parm of interest, your code
reduces to: 
  
EXEC "mySproc " & MyStaticValue() 
  
Here is the template for my static funcs: 
  
Static Function CurrentX(Optional lngNew As Long) As Long
'Copy this template each time you need a new set/get function
'Then Replace "X" with the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done. 
  
    Dim lngCurrent As Long
   If lngNew <> 0 Then lngCurrent = lngNew
   CurrentX = lngCurrent
   #If conDebug = 1 Then
       Debug.Print "Current X: ", CurrentX
   #End If
End Function 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brett Barabash
Sent: Tuesday, September 09, 2003 8:52 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Access 2000 Pass-Through Query Reports

What is the best approach to using a parameterized SQL Server stored
procedure as a recordsource for an A2k report? 
I was thinking of basing the report on a pass-through query (containing an
EXEC statement), but it appears that I would need to modify the pass-through
SQL each time the report is run, to set the parameter values. 

Brett Barabash, MCP 
Tappe Construction, Co. 
Eagan, MN 
bbarabash at tappeconstruction.com 
(651) 256-6831 


"One thing a computer can do that most humans can't is be sealed up in a
cardboard box and sit in a warehouse."  -Jack Handey 





----------------------------------------------------------------------------
----------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software._______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030909/41ebf4f6/attachment-0001.html>