Good afternoon.
I need assistance with calling a function on the back end, that takes paremeters for a stored procedure. Then, that function returns the recordset back to the main DB. Below is the code for the procedures on the back and, and what I have so far on the front end.
**This function calls the stored query, with given parameters, and returns the recodset**
Public Function RSFromParameter Query(strMonthY ear As Date, strCase As Integer) As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "qryMData"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParam eter("prmmonthy ear", adDate, adParamInput, Len(strMonthYea r))
Set prm2 = cmd.CreateParam eter("prmcase", adInteger, adParamInput, Len(strCase))
prm.value = strMonthYear
prm2.value = strCase
cmd.Parameters. Append prm
cmd.Parameters. Append prm2
Set rst = New ADODB.Recordset
rst.Open cmd
Set RSFromParameter Query = rst
End Function
**This is the function that i have written for the front-end. It opens the datasource, but what next?**
Public Function testprocedure2( )
Const adOpenOtatic = 3
Const adUseClient = 3
Const adLockBatchOpti mistic = 4
Dim cmd As New ADODB.Command
'Open a connection
Set oConn = CreateObject("A DODB.Connection ")
oConn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Persist Security Info=False;" & _
"Data Source=\\---------"
'Create the Recordset object
Set rst = CreateObject("A DODB.Recordset" )
rst.CursorLocat ion = adUseClient
Set rst.ActiveConne ction = oConn
'returns function as recordset
Set rst = RSFromParameter Query("3/1/2007", "2") **This works when the data is in the same database, but not when the database is split
Do Until rst.EOF
Debug.Print rst(0), rst(1), rst(2)
rst.MoveNext
Loop
End Function
I need assistance with calling a function on the back end, that takes paremeters for a stored procedure. Then, that function returns the recordset back to the main DB. Below is the code for the procedures on the back and, and what I have so far on the front end.
**This function calls the stored query, with given parameters, and returns the recodset**
Public Function RSFromParameter Query(strMonthY ear As Date, strCase As Integer) As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
Set cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "qryMData"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParam eter("prmmonthy ear", adDate, adParamInput, Len(strMonthYea r))
Set prm2 = cmd.CreateParam eter("prmcase", adInteger, adParamInput, Len(strCase))
prm.value = strMonthYear
prm2.value = strCase
cmd.Parameters. Append prm
cmd.Parameters. Append prm2
Set rst = New ADODB.Recordset
rst.Open cmd
Set RSFromParameter Query = rst
End Function
**This is the function that i have written for the front-end. It opens the datasource, but what next?**
Public Function testprocedure2( )
Const adOpenOtatic = 3
Const adUseClient = 3
Const adLockBatchOpti mistic = 4
Dim cmd As New ADODB.Command
'Open a connection
Set oConn = CreateObject("A DODB.Connection ")
oConn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Persist Security Info=False;" & _
"Data Source=\\---------"
'Create the Recordset object
Set rst = CreateObject("A DODB.Recordset" )
rst.CursorLocat ion = adUseClient
Set rst.ActiveConne ction = oConn
'returns function as recordset
Set rst = RSFromParameter Query("3/1/2007", "2") **This works when the data is in the same database, but not when the database is split
Do Until rst.EOF
Debug.Print rst(0), rst(1), rst(2)
rst.MoveNext
Loop
End Function