Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Posts
    10

    Unanswered: use results of stored proc as recordsource to form

    I am trying to to set the results of a stored procedure as the recordsource of a form. This is because I want server side processing and thought that a multi parameter query is easier to write in t-sql than vba (the latter simply refuses to do a nz(parameter, select all records) type of thing).

    So I have been trying this:
    Code:
    Private Sub btnFilter_Click()
    Dim strConnection As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;TRUSTED_CONNECTION=Yes;"
    Me.RecordSource = ("Exec dbo_spFilterCases, " & Me.cmbCaseFilter & "," & Me.cmbFilterAdvisor & "," & Me.cmbFilterClientType & "," & Me.cmbFilterDate & "," & Me.txtDate1 & "," & Me.txtDate2 & " ")
    but am getting an error, as access can't find the object. This makes some sense to me as I am sure I still need to connect. However, I have no idea on how to do this. Also after connecting and getting the stored proc to execute, I would need to ferry the resulting recordset into access and then set it as a recordsource. So, should I be opening a recordset?but then you can't set a recordset to be the recordsource, can you.

    I have been fiddeling with this for two days now - so any help, very much appreciated. Thanx.
    Thank you for the help!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    An Access form has no available Connect or Connection property. You must use a QueryDef object to connect to a SQL data source.
    Try:
    Code:
    Dim qdf As DAO.QueryDef
    Dim strConnection As String
        
    strConnection = "ODBC;DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;TRUSTED_CONNECTION=Yes;"
    If DCount("*", "MSysObjects", "name='Source' AND Type = 5") > 0 Then DoCmd.DeleteObject acQuery, "Source"
    Set qdf = CurrentDb.CreateQueryDef("Source")
    qdf.Connect = strConnection 
    qdf.SQL ="dbo_spFilterCases, " & Me.cmbCaseFilter & "," & Me.cmbFilterAdvisor & "," & Me.cmbFilterClientType & "," & Me.cmbFilterDate & "," & Me.txtDate1 & "," & Me.txtDate2 & " "
    CurrentDb.QueryDefs.Refresh
    Me.RecordSource = qdf.Name
    Me.Requery
    Be aware that the form will be read-only.
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    10
    thank you, tried it and get syntax error near ',' on line Me.RecordSource = qdf.Name

    Code:
        If DCount("*", "MSysObjects", "name='Source' AND Type = 5") > 0 Then
            DoCmd.DeleteObject acQuery, "Source"
            Set qdf = CurrentDb.CreateQueryDef("Source")
            qdf.Connect = strConnection
            qdf.sql = "spFilterCases, " & Me.cmbCaseFilter & "," & Me.cmbFilterAdvisor & "," & Me.cmbFilterClientType & "," & Me.cmbFilterDate & "," & Me.txtDate1 & "," & Me.txtDate2 & " "
            CurrentDb.QueryDefs.Refresh
            Me.RecordSource = qdf.Name
            Me.Requery
        End If
    also, on the server side, the stored proc seems to not have been executed, I tried various options in terms of the stored proc name, and the with or without EXEC, no joy. Also, would you mind explaining to me what this line does? I assume type 5 are stored procs?
    Code:
    If DCount("*", "MSysObjects", "name='Source' AND Type = 5") > 0 Then

  4. #4
    Join Date
    Nov 2012
    Posts
    10
    ...found what the issue was - when sql says it's happy with null vallues, it means it wants "null" in the exec string...

    Code:
    qdf.sql = "EXEC spFilterCases " & Nz(Me.cmbCaseFilter, "Null") & "," & Nz(Me.cmbFilterAdvisor, "Null") & "," & Nz(Me.cmbFilterClientType, "Null") & " "
    Can I also ask why the form is now read-only. And is there a way to change this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •