[AccessD] Execute stored procedures from Access

You can use a SQL Pass-Through query to run a stored procedure from an mdb.
This won't work if your using an adp. If the parameters change, just change
the SQL of the query via code then run it.  Example follows:

Function RunStoredProc()

    Dim db as Database
    Dim qdf and QueryDef

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryYourPassThroughQueryNameHere")
    qdf.SQL = "YourStoredProcedureNameHere " & YourParameter1 & ", " &
YourParameter2
    qdf.Close
    Set qdf = Nothing
    db.Close
    Set db = Nothing

    DoCmd.OpenQuery "qryYourPassThroughQueryNameHere"

End Function

One drawback to this is I'm not sure if you can capture any returned values
from the stored procedure.

HTH

Rusty


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 21, 2009 10:47 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Execute stored procedures from Access

Does anyone have commented code to execute SQL Server stored procedures with
parameters from Access code?

--
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************