Execute SQL Stored Proc from Access

[Jinjer]

I am working with an Access 2003 mdb which is connected via an ODBC DSN to a
SQL 2005 database. I need to run a SQL stored proc from Access. This stored
proc is not any kind of query; it's a stored proc that runs several other SPs
in the SQL database. I have tried various methods, but keep getting
different errors.

One of the methods I tried is:
sSQL = "EXEC sp_SprocName @SPvar = " & fvVar()
DoCmd.RunSQL sSQL

This method works fine in an Access Project, but this is not a project, but
an mdb. I get the error 3129 Invalid SQL statement; expected 'DELETE',
'INSERT', ...etc.

Any code suggestions that will run this SP?

Thanks,

 

[Mark Andrews]

You can create a pass-thru query and store the username/password in the
query itself. Then you can use SQl Server syntax
to do anything. I don't have it in front of me but I think you check the
box to save connection information.

Example: "Exec sp_Sprocname @Salary > 50000"

At runtime you change the querydef. This little example assumes the query
is created and is called "qryTest". First make it manually and
you should be able to run it and see records.

dim qd as DAO.querydef
dim sql as String
dim dblSalary as Double

dblSalary = 50000

sql = "Exec sp_Sprocname @Salary > " & Cstr(dblSalary)

Set qd = Currentdb.QueryDefs("qryTest")
qd.sql = sql
qd.Close

'then use qryTest for whatever it's needed for (report, subform etc...)

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com