rodrunner79
IS-IT--Management
Hello,
I am creating a sub that executes a stored procedure with parameters. On the CreateParameter method, instead of defining the arguments for every parameter I want to append, I want to derive them from a recordset. But when I call the sub, I keep getting a "Mismatch" error and VBA highlights the CreateParameter method line that throws off the error. The reason why I want to do this is because I want to store all my stored procedures, the parameter names and data types in a local/linked table in Access. Is this possible?
Here's my code:
Can't means won't try.
I am creating a sub that executes a stored procedure with parameters. On the CreateParameter method, instead of defining the arguments for every parameter I want to append, I want to derive them from a recordset. But when I call the sub, I keep getting a "Mismatch" error and VBA highlights the CreateParameter method line that throws off the error. The reason why I want to do this is because I want to store all my stored procedures, the parameter names and data types in a local/linked table in Access. Is this possible?
Here's my code:
Code:
Function ExecuteStoredProcedure(ByVal lngUtilityID As Long, _
varFirstValue As Variant, varSecondValue As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
strSQL = "SELECT A.* " & _
"FROM tblSytemUtility AS A " & _
"WHERE A.Category = 'Stored Procedure' " & _
"And A.SPID = " & lngUtilityID & ";"
Set db = CurrentDb()
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
Set cnn = New ADODB.Connection
cnn.Provider = "MSDASQL"
Set cnn = New ADODB.Connection
cnn.ConnectionString = "driver={SQL Server};" & _
"server=192.168.1.255,1433;uid=TestUser;pwd=defaultpass;database=MYSQLDB"
cnn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = rs!UtilityName ' Stored procedure name derived from recordset
Set prm = cmd.CreateParameter(rs!CustomField1, rs!CustomField2, rs!CustomField3, rs!CustomField4, varFirstValue)
cmd.Parameters.Append prm
cmd.Parameters(rs!CustomField1).Value = varFirstValue
Set prm = cmd.CreateParameter(rs!CustomField5, rs!CustomField6, rs!CustomField7, rs!CustomField8, varSecondValue)
cmd.Parameters.Append prm
cmd.Parameters(rs!CustomField5).Value = varSecondValue
cmd.Execute
cnn.Close
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Set cnn = Nothing
Set cmd = Nothing
End Function
Can't means won't try.