UtterAccess.com
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    
 
   
whdyck
post Dec 20 2018, 03:25 PM
Post#1



Posts: 284
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?

Thanks.

Wayne
Go to the top of the page
 
JeffK
post Jan 11 2019, 09:59 AM
Post#2


UtterAccess VIP
Posts: 1,564
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:

1
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.

2
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.

3
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
 
projecttoday
post Jan 11 2019, 12:25 PM
Post#3


UtterAccess VIP
Posts: 10,480
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    16th January 2019 - 03:29 PM