Calling Function on Back-end

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • klove1209
    New Member
    • Feb 2007
    • 32

    #1

    Calling Function on Back-end

    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**
    Code:
    Public Function RSFromParameterQuery(strMonthYear 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.ActiveConnection = CurrentProject.Connection
    
       cmd.CommandText = "qryMData"
       cmd.CommandType = adCmdStoredProc
    
     
    Set prm = cmd.CreateParameter("prmmonthyear", adDate, adParamInput, Len(strMonthYear))
    Set prm2 = cmd.CreateParameter("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 RSFromParameterQuery = 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 adLockBatchOptimistic = 4
    
    Dim cmd As New ADODB.Command
    
     'Open a connection
        Set oConn = CreateObject("ADODB.Connection")
      oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Persist Security Info=False;" & _
                            "Data Source=\\---------"
    
      'Create the Recordset object
      Set rst = CreateObject("ADODB.Recordset")
      rst.CursorLocation = adUseClient
    
    Set rst.ActiveConnection = oConn
    
    'returns function as recordset
    Set rst = RSFromParameterQuery("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
    Last edited by NeoPa; Jun 6 '07, 01:35 PM. Reason: Please use [CODE] tags
Working...