Using A Variable To Call A Stored Procedure Name

Hello;

I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.

I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.

So how do I refer to a procedures name using a variable?

Here's my code;


Function LieuBen()

MyCalendar = CurrTSCalendar

Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")

End Function

Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String

Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"

Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command

ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"

' Connect to the data source.
objConn.Open ConnectionString

' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn

objConn.MySProc MyCalendar, objRs

objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing


End Function