ADO Based Forms - Filters

[gray]

Hi

I'm just about to begin adding some more complex filters into an ADO RS based form (subform actually) and I have an architecture question please.

In the main-form open event, I build an SQL String, apply it to an ADO RS.Open, open the subform with the RS. I then use filters in the main-form Current event (using the new current main-form ID) in order to replicate the Master/Child links functionality in DAO. This works hunky dorey for simple filters.

I started this weeks ago, got distracted and I'm now coming back to it. At that time I'm positive I read somewhere that ADO filters do not have an Operator Preference for ANDs & ORs?? I've looked around but can't find that info again.

Before I start unpicking reams of my code and wandering into the Access maze again I wonder, could I ask please; Firstly, did I imagine this? Secondly, if it is true, how does one achieve the equivalent of the below.

strFilter= "Surname='Smith' AND (FirstName='John' OR FirstName='Fred')
myRS.Filter = adFilterNone
myRS.Filter = strFilter
Set myForm.Recordset = MyForm.Recordset

Thanks

Edit-

Ran a few tests with mixed ANDs & ORs and they seem to produce the correct results... I never trust things I've even seen with my own eyes nowadays.... so any affirmation or contradiction to my results would be much appreciated

 

[Pat Hartman]

Access forms are natively DAO so attempting to use an ADO recordset is fraught with problems. And that's all I can tell you because this is something I would never do. There is no benefit to fighting with Access. It always wins and life is simply too short for this type of aggravation . I'm sure you think you have a valid reason for deviating from the Access norm of bound forms so I won't even go there.

It doesn't make sense to me that ADO filters would not have an order of precedence. Everything else does. The standard order is NOT, AND, OR.

Logically, if you are filtering the recordset, you would use ADO but if you are filtering the form it would be DAO.

If you've gone through the bother of doing this, why does the recordset need filtering anyway. The recordset should request ONLY the record requested so you should be changing the query criteria and rerunning the query. People rail at Access developers for binding forms to tables which is the "native" Access way. You seem to be doing the same thing but the hard way because you are doing it with code instead of property settings. With Jet/ACE tables, it doesn't make much difference but with large tables and a SQL Server BE, a query that selects 1 record is quite differnet from one that selects an entire table.

 

[gray]

Hi

Thanks for the reply. Firstly, I have to apologise for raising this thread. I looked back and I'd raised a very similar one about ADO filters a few months back... I got so distracted with other problems recently that I completely forgot I'd gone through this already.

If you are reading this for the first time there is further explanation at:-
http://support.microsoft.com/kb/235892

As to ADO. I was persuaded to ADO because I'd read it was a requirement if SQL server or other Db back-ends might be needed in the future. As you say, Pat, this is a not a trivial conversion and is indeed fraught with problems. Here's a couple of observations.

a) Searching ("Find") is only allowed on one criteria so if you want to RS.Find First_name='Fred', Second_name = 'Bloggs' you have to use Rs.Filter instead. Oh the joy when this penny drops... after hours of trying to figure out why searches were not returning the correct results.
b) RS.Filters using AND & ORs can catch you out. They do not map directly with use in SQL ... see the link above
c) If you want to use the same functionality as DAO Master/Child links this can be done either by modifying the RS.Source (SELECT * FROM xxx) or updating an RS.Filter. These would be modified in the main form current event.
Now the former necessitates a reload of the entire subform each time ... click to another record in the main form where there are multiple subforms and you will have time to catch up on your filing.
If you use the latter (RS.Filter), the RS.Source obviously must contain at least the records you are looking for... so you have to load the whole or a subset of the table in the Rs.Source. Add in large tables and network traffic.. well you get the picture.
d) There are a bedazzling array of options for cursors, locks and connections dependent upon the back-end and provider capabilities and if BeginTrans, CommitTrans is used.. deletes, adds or edits are needed.
e) I deduce that these are effectively disconnected RS's. With the options I have set, to see other peoples' new records the form needs to be reloaded, edited data can appear just by setting the RS to itself .. Set myForm.RS=myForm.RS.

Apologies again for raising my query twice.

 

[Pat Hartman]

This bug hasn't been fixed since 2001?

I'll repeat myself - There is no benefit from bringing down a large recordset and filtering it locally. The most efficient solution is to use SQL to select only the rows you want - regardless of whether you are using bound forms or disconnected recordsets. Change the criteria and rerun the query. You can't beat a bug and the change will be more efficient anyway.

It is not too late to swich to a bound form. You'll be deleting code rather than writing it.

 

[gray]

Hi Pat

I am testing SQL TXs in DAO Bound Forms even as I write! thanks and rgds

 

[Pat Hartman]

It will be like a weight being lifted off your shoulders You'll have so much free time you'll be able to work a second job.