VBA call store procedure

[YZXIA]

Can someone provide me with a snippet of code that allows me to call a
store procedure that resides in a SQL Server?

 

[Norman Yuan]

You would use ADO to do the call. Some quick code look like:

Dim cn As ADO.DB.Connection
Dim cmd As ADODB.Command
Dim pmt As ADO.Parameter

''Create connecction to SQL Server database
SET cn=New ADODB.Connection
cn.Open myConnectionString

''Create Command Object
Set cmd=New ADODB.Command
cmd.CommandType=adCmdStoredProc
cmd.CommandText="myStoredProcedureName"
Set cmd.ActiveConnection=cn

''Set up parameters if needed
Set pmt=cmd.CreateParameter(......)
cmd.Parameters.Append pmt
....

'Finally execute the command
''Then you can retrieve output parameter value, or store return RecordSet on
an AADODB.recordSet
cmd.Execute

or

Dim rs As ADODB.recordSet
Set rs=cmd.Execute

cn.Close

.....