Slow Searches in Access

[Light Speed]

Hi,

We use Access 2000 forms to edit tables linked from an Oracle 8i
database. When searching for records, unless we uncheck the option
"Search Fields As Formatted" the searches are impossibly slow. The
fields being searched are indexed under Oracle.

a) Is there some way we can make this option unchecked by default?

b) Is there another method of speeding up the searching function?

Any tips greatly appreciated.

Thanks

[Albert D. Kallal]

Re: Slow Searches in Access

I would build a search form with a prompt box.

I would then open he form to the one record you need. This means if you have
1 million records, or 5 records, and you search for a invoice number, the
response will be instant.

You can use the after update event of a text box, and fill a sub-form with
data for the user to select, and this also works very well.

So, lets assume,that you want to search by last name, in the after update,
we can go:


dim strSql as string

strSql = "select * from tblCustomer where LastName like '" & me!txtLastSerch
& "*'"

me.MySubForm.Fo rm.RecorSource = strSql

The above will thus stuff the sub-form with a nice hit list. it is then
simple matter to place a button in the continues form to launch the main
form to the ONE RECORD.

I have a good explain, and some screen shots of the above idea in action
here:

http://www.attcanada.net/~kallal.msn/Search/index.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.attcanada.net/~kallal.msn