Execute Stored Procedure from VBA

Romashka · February 21st, 2007, 04:09 PM

Hi!

I'm trying to run a SS stored procedure
from the Access front end.
The code in the Switchboard's Form_Load event goes like this:

Code:

Dim qrySQL As DAO.QueryDef
Set qrySQL = CurrentDb().CreateQueryDef("sp_setapprole")
qrySQL.SQL = "EXEC sp_setapprole " & "MY_Role" & ", " & "MY_Role_PW" & ";"
qrySQL.Execute
qrySQL.SQL = "exec sp_setapprole role_name_here, role_pwd_here"
Set qrySQL = Nothing

But it always gives me Run time error 3129:
Invalid SQL statement; expected âDELETEâ, âINSERTâ, âPROCEDUREâ,
âSELECTâ, or âUPDATEâ.
at qrySQL.SQL = "EXEC sp_setapprole " & "MY_Role" & ", " & "MY_Role_PW" & ";"

What can be a problem here? Please help!!!! Thanks!

mmcdonal · February 22nd, 2007, 12:52 PM

I am not sure if you can run a SQL Server stored procedure from an Access front end, but if you can, you sure cannot do it with DAO. You need to switch to ADO to talk to a SQL Server. DAO is only for Jet databases.

HTH

mmcdonal