Getting a Resultset from a SQL Server Stored Proc in Access

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 like...

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!

Solution

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 sConnect As String

   sConnect= "driver={sql server};" &amp; _
             "server=server_name;" &amp; _
             "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 = Conn
   Cmd.CommandText = "sp_AdoTest"
   Cmd.CommandType = adCmdStoredProc
   Cmd.Parameters.Refresh
   Cmd.Parameters(1).Value = 10
   Set Rs = Cmd.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