Function Execute_Passthrough_Query(sqltext As String)

' Purpose: Run a temporary pass-through query.
' Accepts: sqltext: SQL string to run or stored procedure's name.
' Returns: nothing.

Dim dbSQL As Database, qdSQL As QueryDef
Dim strConnect As String
Dim strCommand As String

strConnect = "ODBC;DRIVER=SQL Server;SERVER=LIBNT4NAS02;DATABASE=test_soldb;Trusted_Connection=Yes"

Set dbSQL = DBEngine.Workspaces(0).Databases(0)

' Create a temporary QueryDef object that is not saved.
Set qdSQL = dbSQL.CreateQueryDef("")

' Set the ReturnsRecords property to False in order to use the
' Execute method.
qdSQL.ReturnsRecords = False

qdSQL.Connect = strConnect


qdSQL.SQL = sqltext

qdSQL.Execute

qdSQL.Close
Set qdSQL = Nothing
dbSQL.Close
Set dbSQL = Nothing

End Function

Function Passthrough_Query(strSQL As String) As Recordset

' Purpose: Run pass-through query.
' Accepts: strSQL: stored procedure's name.
' Returns: resultset.

Dim dbSQL As Database
Dim strConnect As String

strConnect ="ODBC;DRIVER=SQL Server;SERVER=LIBNT4NAS02;DATABASE=pubs;Trusted_Connection=Yes"

Set dbSQL = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect)
Set Passthrough_Query = dbSQL.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

End Function


Private Sub Command1_Click()

Dim rsResult As Recordset
Dim strSQL as String

strSQL="exec my_stored_procedure_name"

Set rsResult = Passthrough_Query(strSQL).OpenRecordset

End Sub