By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Calling Function on Back-end

P: 32
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**
Expand|Select|Wrap|Line Numbers
  1. Public Function RSFromParameterQuery(strMonthYear As Date, strCase As Integer) As ADODB.Recordset
  2.  
  3.    Dim prm As ADODB.Parameter
  4.    Dim prm2 As ADODB.Parameter
  5.    Dim cmd As ADODB.Command
  6.    Dim rst As ADODB.Recordset
  7.  
  8.    Set cmd = New ADODB.Command
  9.    Set cmd.ActiveConnection = CurrentProject.Connection
  10.  
  11.    cmd.CommandText = "qryMData"
  12.    cmd.CommandType = adCmdStoredProc
  13.  
  14.  
  15. Set prm = cmd.CreateParameter("prmmonthyear", adDate, adParamInput, Len(strMonthYear))
  16. Set prm2 = cmd.CreateParameter("prmcase", adInteger, adParamInput, Len(strCase))
  17.  
  18.  
  19.    prm.value = strMonthYear
  20.    prm2.value = strCase
  21.  
  22.    cmd.Parameters.Append prm
  23.    cmd.Parameters.Append prm2
  24.  
  25.    Set rst = New ADODB.Recordset
  26.  
  27.    rst.Open cmd
  28.  
  29.     Set RSFromParameterQuery = rst
  30.  
  31. End Function
  32.  
  33. **This is the function that i have written for the front-end. It opens the datasource, but what next?**
  34.  
  35. Public Function testprocedure2()
  36.  
  37. Const adOpenOtatic = 3
  38. Const adUseClient = 3
  39. Const adLockBatchOptimistic = 4
  40.  
  41. Dim cmd As New ADODB.Command
  42.  
  43.  'Open a connection
  44.     Set oConn = CreateObject("ADODB.Connection")
  45.   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  46.                         "Persist Security Info=False;" & _
  47.                         "Data Source=\\---------"
  48.  
  49.   'Create the Recordset object
  50.   Set rst = CreateObject("ADODB.Recordset")
  51.   rst.CursorLocation = adUseClient
  52.  
  53. Set rst.ActiveConnection = oConn
  54.  
  55. 'returns function as recordset
  56. Set rst = RSFromParameterQuery("3/1/2007", "2") **This works when the data is in the same database, but not when the database is split
  57.  
  58.    Do Until rst.EOF
  59.       Debug.Print rst(0), rst(1), rst(2)
  60.       rst.MoveNext
  61.    Loop
  62.  
  63. End Function
Jun 4 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.