[AccessD] Slo-o-o-w ODBC connection to Oracle - Solved

Spot on Emilia. I had already discovered your solution.

There were in fact two sources of delay. In the sub-form the record source had SELECT DISTINCT... which was causing a full table scan, and the DISTINCT was unnecessary anyway.

The problem with the main form was indeed that I was attempting to apply the where clause 'externally' with 

Me.RecordSource = "select * from MyQuery Where ID=" & ComboBox.

My solution was to make a copy of the pass-through query "MyQuery" to use as a template, and then at runtime I retrieve its SQL, add the WHERE clause to the SQL string, and then use that to update the SQL of MyQuery. Problem solved.

Thanks to all.

Lambert

-----Original Message-----
From: Microsoft Access Database Discussion List [mailto:ACCESS-L at PEACH.EASE.LSOFT.COM] On Behalf Of Emilia Maxim
Sent: Wednesday, October 30, 2013 3:25 PM
To: ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: Slo-o-o-w ODBC connection to Oracle

Lambert,

> The main form is bound to the people table via a simple select query. 
> The record source of the form is set when a person is selected from an 
> unbound combobox...
>           Me.RecordSource = "select * from MyQuery Where ID=" & ComboBox.

> This same combobox selection also sets the record source for the continuous
>   sub form
>              Me.View_Data_sub_frm.Form.RecordSource = "Select * from 
> Anoth= erQuery Where FKID=" & ComboBox

What about MyQuery and AnotherQuery? I guess if you set the Where condition as above, Access will pull all data from both queries, and would filter them only afterwards, i.e. locally.

Pass thru queries cannot be used here, because they are not updatable, and I think the forms are not only to display data, but also to modify them. Maybe you could modify the SQL of MyQuery and AnotherQuery themselves by building the Where condition.


--
Regards
Emilia, TWIG

Emilia Maxim
PC-SoftwareService
Stuttgart, Germany

--------------------------------------------------------------------------
The ACCESS-L list is hosted on L-Soft international's LISTSERV(R) software running on Microsoft Windows Server 2008 R2. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L