sql parametered stored procedure as form recordsource

BabaYaga · January 16th, 2013, 08:08 AM

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 need to connect. However, I have no clue on how to do this. and then surely I would need to ferry the resulting recordset into access and then set it as a recordsource, or am I on the wrong track here?
Thank you for the help!