01-31-06, 15:34 #1Registered User
- Join Date
- Jan 2005
Unanswered: Using a Variable to Call a Stored Procedure Name
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;
MyCalendar = CurrTSCalendar
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 & _
' Connect to the data source.
' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn
objConn.MySProc MyCalendar, objRs
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing
01-31-06, 16:06 #2Registered User
- Join Date
- Dec 2002
First, I'm not necessarily certain that what you are doing is a good idea. I certainly wouldn't view it as a "best practice". If you have two different stored procs that serve different purposes, then you probably ought to call them from different functions. Alternatively, you could potentially create a single stored proc with a flag value that would execute differently based on the value in the flag.
In addition, I was puzzled by this line in your original post:
My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
Here is the code:
Dim sName sName = "spMySproc" Call CallSproc (sName) Function CallSproc (sSprocName) Dim oComm Dim oConn Dim oRS Dim sConnectionString Dim i Set oConn = CreateObject("ADODB.Connection") Set oComm = CreateObject("ADODB.Command") Set oRS = CreateObject("ADODB.Recordset") sConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CATALOG;Data Source=MyServer" oConn.ConnectionString = sConnectionString oConn.Open oComm.CommandText = sSprocName oComm.CommandType = 4 ' adStoredProc oComm.ActiveConnection = oConn Set oRS = oComm.Execute i = 0 While Not oRS.EOF oRS.MoveNext i = i + 1 Wend MsgBox i End FunctionHave you hugged your backup today?