Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002

    Unanswered: passthru query to sql server with parameters

    Since Access doesn't seem to support passthru queries to SQL server providing parameters. For example to passthru a sql statement to sql server to call a stored procedure requiring input parameters. What is a workaround to do this?

    This doesn't work, but it is my attempt to explain what I want to do:
    DoCmd.RunSQL "select * from [ODBC;DSN=Inftemp;Description=Inftemp;DATABASE=Infi nity-temp;TABLE=Shrinkage;Trusted_Connection=Yes]"

    Something similar to this (which actually works) would allow me to dynamically build the statement in access while providing the parameters from a dropdown box or some other source.

    How can I build a dynamic statement and get it to SQL server?


  2. #2
    Join Date
    Mar 2002
    Bielefeld, Germany
    You should open an ADODB connection to the server.

    Dim cnn as adodb.connection
    Set cnn = new adodb.connection
    cnn.connectionstring = <whatever appropriate>

    Then you have at least two options:
    1. using the adodb.command object

    Dim cmd as adodb.command
    set cmd = new adodb.command
    set cmd.connection = cnn
    cmd.commandtype = accmdstoredproc
    cmd.commandtext = "<name of sp>"
    cmd.parameters(1) = <first param>
    cmd.parameters(2) = <second param>
    cmd.execute '(in case of update/insert/delete) or

    dim rs as adodb.recordset
    set rs = cmd.execute '(in case of select)

    2. passthrough sql

    dim strSQL as string

    strSQL = "exec <name of sp> <param1>, <param2>, ... , <param n>"
    cnn.execute strSQL 'or

    set rs = new adodb.recordset strSQL, cnn, adopenstatic, adlock<whatever>

    This works also for passthrough sql without calling sps.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts