Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: Stored Procedures and Parameters

    Have looked everywhere and cannot find the answer! So perhaps someone here can answer.

    I have an Access 2000 front-end to a SQL Server 2000 database.

    I know how to create Stored Procedures that receive parameters; and also how to open a Stored Procedure in the query results window using the DoCmd object. For example,

    Application.DoCmd.OpenStoredProcedure "MyProc", acViewNormal, acReadOnly

    Does anyone know of a way to pass parameters to Stored Procedures & open the result using the DoCmd object? Without getting a parm input dialog?

    Alternatively, does anyone know of a way to open a Stored Procedure in the query results view without using the DoCmd object?

    Will appreciate any guidance you can provide! Thanks!

    CarlR

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Have you tried DoCmd.RunSQL(SQLStatement, [UseTransaction])

  3. #3
    Join Date
    Dec 2003
    Posts
    15
    Without using the DoCMD object you can create a pass-through query in access by creating a new query, getting rid of the table selection dialog, and then selecting SQL Specific -> Pass-Through form the query menu. It's then as easy as entering the text of the stored proc.

    Now, one problem with that method (and I really hope someone has an answer because I don't) is that you cannot create the parameters for the stored proc dynamically. Basically you have to use fixed params or use a wrapping stored proc to generate "mildly dynamic" params.

    Dan

  4. #4
    Join Date
    Dec 2003
    Posts
    15

    Exclamation

    Love posting my own replies... not that I do it that often but anyway...

    Actually, this article http://p2p.wrox.com/topic.asp?TOPIC_ID=5485 describes a method that actually works. It alows you to create dynamic parameters for a SQL Stored Procedure in Access. I got this working in Access2k.

    I hate Access... :-\

    Dan

Posting Permissions

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