Calling stored proceedure from vba

  • Thread starter Thread starter Shaw
  • Start date Start date
S

Shaw

I have access2003 and mssql db
I have created in MS-SQL DB a stored proceedure with 3 parameters.

How do I call it from within VBA code and pass parms?
Is there a code snipet example?

Thanks,
Sherwood
 
You need to use a pass-through query to run stored procedures. To pass
parameters, you have to dynamically rewrite the SQL each time.

Something like the following untested air-code:

Dim qdfCurr As DAO.QueryDef
DIm strSQL As String

Set qdfCurr = CurrentDb().QueryDefs("MyPassThroughQuery")
strSQL = "CALL MyStoredProcedure @Parm1=" & Me.txtValue & ", @Parm2='" &
Me.txtOtherValue & "'"
qdfCurr.SQL = strSQL
qdfCurr.Execute ' If the stored procedure doesn't return a record set

or

rsCurr = qdfCurr.OpenRecordset ' If the stored procedure returns a
record set
 
Here are some additional references, depending on your needs:

http://support.microsoft.com/default.aspx?scid=kb;en-us;184749
http://support.microsoft.com/default.aspx?scid=kb;en-us;195047
http://support.microsoft.com/default.aspx?scid=kb;en-us;168210

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have access2003 and mssql db
I have created in MS-SQL DB a stored proceedure with 3 parameters.

How do I call it from within VBA code and pass parms?
Is there a code snipet example?

Thanks,
Sherwood
 
screw MDB use Access Data Projects.

it is a lot more fun.

Docmd.RunSQL "EXEC myproc 1, 2, 'myarg3'"
 
Back
Top