How to call SQL Server Stored Procedure from within MS Access VBA

Question

I have a Stored Procedure that takes parameters, but does not return anything. How do I call it from within MS Access?

EDIT - With Remou's assistance, I've got it working:

Set cnn = New adodb.Connection
cnn.ConnectionString = 
   "driver=SQL Server;server=SERVER\SERVER;database=a_db;Trusted_Connection=Yes"

cnn.Open cnn.ConnectionString

Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"

Set param = cmd.CreateParameter
                ("DataSetID", adInteger, adParamInput, 6, stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
                ("Destination", adChar, adParamInput, 2, stDestination)
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

You can run against a connection or you can use a pass-through support.microsoft.com/kb/303968 — Fionnuala
– Fionnuala, Commented Sep 13, 2012 at 11:16
It tells me 'Cannot execute a select query', which confuses me as it's not a select query — aSystemOverload
– aSystemOverload, Commented Sep 13, 2012 at 12:07

Fionnuala · Accepted Answer · 2012-09-13 12:29:17Z

4

Here is an outline that may help. I may be able to get back with more detail later. It is not specific to your case, but it may keep you going :)

Public dbconn As ADODb.Connection
Dim cmd As New ADODb.command
Dim rs As New ADODb.Recordset
Dim param As New ADODb.Parameter

Set dbconn = New ADODb.Connection
dbconn.ConnectionString = GetServerCon

dbconn.Open dbconn.ConnectionString

Set cmd = New ADODb.command
cmd.ActiveConnection = dbconn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insert_user"

Set param = cmd.CreateParameter(_
   "username", adVarChar, adParamInput, 50, Environ("UserDomain") & "dddddddd\" _
   & Environ("Username"))
cmd.Parameters.Append param

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd

answered Sep 13, 2012 at 12:29

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call SQL Server Stored Procedure from within MS Access VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related