I've been 'lucky' enough to inherit an Access database that needs cleaned up tremendously. We're actually going to move off of Access and convert it into .NET/SQL Server. For now however we're retaining the Access database's UI and moving all the queries into SQL Server.
Unfortunately I've never really worked with Access directly so I'm stumbling as I go.
I'm looking to figure out a way to write a function that takes a stored proc and some parameter values and executes the stored proc on SQL Server and returns the results as a Resultset.
So a signature might look li开发者_StackOverflow社区ke...
Public Function ExecuteStoredProcedure(storedProcName As String, parameterValues As String) As RecordSet
The parameter values passed in would be comma delimited.
So a sample call to this function might look like...
Set returnValues = ExecuteStoredProcedure("SP_GetTableXYZContents","'01/01/2011','ABCD',2345")
This seems so trivial but I can't seem to get it functioning properly. Can anyone point me in the right direction?
I have them executing simply by the following (but unable to get return values)
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "...connectionstring..."
conn.Execute storedProcCall, dbSQLPassThrough
Note that in the code above the function only takes one string variable in (a SP name and parameter values where the SP name and values are space separated and the values are comma separated.
Thanks in advance for any help!
There are a few ways to accomplish this. Here are two possibilities.
The first is the simplest:
' String specifying SQL.
SQL = "My_StorProc parm1, parm2, parm3"
...
' For a stored procedure that doesn't return records.
MyDb.Execute SQL, dbSQLPassThrough
i = MyDb.RowsAffected
...
'For a stored procedure that returns records.
set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)
Ref: How To Call Stored Procedures Using Data Access Objects
The second uses ADODB objects:
Dim Conn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim Connect As String
sConnect= "driver={sql server};" & _
"server=server_name;" & _
"Database=pubs;UID=uder_id;PWD=password;"
' Establish connection.
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
' Open recordset.
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = Conn1
Cmd.CommandText = "sp_AdoTest"
Cmd.CommandType = adCmdStoredProc
Cmd.Parameters.Refresh
Cmd.Parameters(1).Value = 10
Set Rs = Cmd1.Execute()
' Process results from recordset, then close it.
Rs.Close
Conn.Close
Set Rs = Nothing
Set Cmd = Nothing
Set Conn = Nothing
Ref: How To Invoke a Stored Procedure with ADO Query Using VBA/C++/Java
You want to have a look at the ADODB.Command
object. It works in a similar way to the SqlCommand object in .Net (i.e you set the CommandText
, set the CommandType
, and add parameters using the CreateParameter
method I believe). It has been a while since I used it as well.
EDIT:
Forgot to mention that the Execute
method on the Command object returns you a Recordset
as well.
精彩评论