I am trying to filter only wanted data from a query or stored procedure using adodb using multiple criteria. Is there any easy method to do this? Currently i am using the command object and add the WHERE and ORDER BY parts to the command.commandtext property (see below), but this doesent seem to work (it just ignors the added filter and order it seems).
Any help is apreciated. Thanks
Dim cnKeytox As ADODB.Connection
Dim cat As ADOX.Catalog
Dim qdfAdox As ADOX.Procedure
Dim CMD As ADODB.Command
Dim rsADO As New ADODB.Recordset
'Create new catalog and set connection to Keytox database
Set cnKeytox = CurrentProject.Connection
Set cat = New ADOX.Catalog
cat.ActiveConnection = cnKeytox
'Get dataquery and add parameters
If sReport = "Screening report" Then
Set qdfAdox = cat.Procedures("SCR res 3 non blanks with blank results")
Set CMD = qdfAdox.Command
'add filter and order
CMD.CommandText = Replace(CMD.CommandText, ";", " " & sFilter & sOrder)
CMD.Parameters(0) = CSng(Form_Form_Reports.EditBlankWindow.Value)
Set rsADO = CMD.Execute
'do something with data.....
Ok i found a solution: I just copied the command.commantext property (and other necessary properties) to a new command object and executed that 1. This works, though i am still wondering why it won't work on the original command object. Anyone an idea?