[AccessD] Access 2000 Pass-Through Query Reports

It isn't. You have to resolve the UDF first and then pass its result
through. For example:

Dim strSQL as String
strSQL = "SELECT * FROM myTables WHERE PK = " & myFuncThatReturnsAPK()

Then you can pass the result string. The key to making this work is resolve
all the values first.
  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Brett Barabash
  Sent: Tuesday, September 09, 2003 1:52 PM
  To: 'Access Developers discussion and problem solving'
  Subject: RE: [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.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030909/e991df3b/attachment-0001.html>