For DAO:

Function Execute_Passthrough_Query(sqltext As String)

' Purpose: Run a temporary pass-through query.
' Accepts: sqltext: SQL string to execute or stored procedure's name.
' Returns: nothing.
Dim dbSQL As Database, qdSQL As QueryDef
Dim strConnect As String
Dim strCommand As String

strConnect = “your connection string”
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 Select_Passthrough_Query(sqltext As String) As Recordset

' Purpose: Run pass-through query.
' Accepts: sqltext: SQL string to execute or stored procedure's name.
' Returns: recordset.

Dim dbSQL As Database
Dim strConnect As String

strConnect = “your connection string”
Set dbSQL = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect)
Set Select_Passthrough_Query = dbSQL.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)

End Function

How to return records using Select_Passthrough_Query function:

Private Sub Command1_Click()
Dim rs as Recordset
Dim strSQL as String

strSQL= “MyStoredProcedureName”
Set rs= Select_Passthrough_Query(strSQL).OpenRecordset

End Sub