Post reply

Running Stored Procedure from Access

  • May 23, 2010 at 3:17 pm

    #1171332

    Matt,

    Do you know how to imbed the connect string inside that pass-through query? I did one and every time I run it, I have to slog through the ODBC screens. It would be great to set it up once!

    Thanks

  • October 6, 2011 at 12:02 pm

    #1391372

    Hi Mr. Mitchell:

    I have to thank you, I was trying to execute a SQL 2005 stored procedure in a MS Access 2007 DB, I revised several examples (I think around 15 with more than 15 lines of code each one) without the expected results. Your solution is simple and with just 2 lines of code, I got the solution of my problem. Thank you again.

    WILLIAM MITCHELL (6/6/2008)

    Create a new query in Access, don't add any tables. Click Query ~ SQL Specific ~ Pass-Through. In the SQL window, type your statement e.g.

    EXEC usp_MyProcedure '6/6/2008'

    then click View ~ Properties and under the ODBC Connect String click the build button (...) and select a DSN that points to your SQL database.

    Save your query e.g. qryMyQuery

    You can run the query just like any Access action query.

    To change the parameters for the query, use this in VBA code:

    CurrentDB.QueryDefs("qryMyQuery").SQL = "EXEC usp_MyProcedure '6/1/2008' "

    Make sure that you grant EXECUTE permission for the stored procedure to the login used by your DSN.

    HTH

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply