X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Filtering Invokes Another Query From The Back End, Access 2003    
post Dec 20 2018, 03:25 PM

Posts: 315
Joined: 20-July 11
From: Winnipeg, MB, Canada

I have an Access application with an Oracle back end. Using TOAD's SQL Trace, I can show that when FilterOn is set to True, the RecordSource fires against the backend (Oracle sees the RecordSource statement firing again.)

I'm thinking that setting FilterOn = True should merely filter the records currently in the form, but that does not seem to be the case.

For performance, I'd like to eliminate this extra hit on Oracle. About the only way I can see is to always add any filter conditions before I query/requery. But that would also require me to re-query whenever only a filter needs to be applied or removed.

Is there anything I can do to eliminate this extra hit against Oracle when FilterOn is set to true, or what's the best approach here, if any?


Go to the top of the page
post Jan 11 2019, 09:59 AM

UtterAccess VIP
Posts: 1,565
Joined: 19-December 02
From: Lansing, MI

The only approach I can think of here is to set your form's recordset to a module-level disconnected ADODB recordset, rather than setting the form's recordsource. The actions that you currently use to set the form filter property would instead set the recordset's filter property, then set it back to the form's recordset. I do this when I want forms to be immediately responsive to filters, such as those that apply as you type. The disconnected recordset keeps the records only in memory, with no constant connection to the source table.

There are couple of warnings about this though:

The records will be read-only. If you want users to be able to edit them, you'd have to write the edits back to the source table using VBA, or use your filtered list to open a second bound form for editing.

Disconnecting a recordset requires a CursorLocation setting of adUseClient. This has a number of problems in Access 2007+. Under various conditions, client-side cursors produce errors when opening recordsests, filtering them or checking for EOF. Sometimes they simply don't return records. I reported this to MS with my Access 2019 testing but it's an issue going back to 2007. You wouldn't have this problem with Access 2002/2003 but you'd have to be aware of it if you upgrade.

ADO recordset filters are not as robust as form filters and WHERE clauses. For example, you can't define a filter on a calculation. "FieldName = 1" is fine but "FieldName+1=1" is not. You also can't use factored and/or combinations. "(a or b) and (c or d)" must be written as "(a and c) or (a and d) or (b and c) or (b and d)"
Go to the top of the page
post Jan 11 2019, 12:25 PM

UtterAccess VIP
Posts: 10,733
Joined: 10-February 04
From: South Charleston, WV

Is this for display only? Maybe copying the desired records from Oracle into an Access temporary table from the get-go. From that point on, filtering is done in Access. So no hit on Oracle.

Robert Crouser
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    26th May 2019 - 06:26 AM