Simple workaround to slow Find or unusable Seek on attached tables!

When I migrated my tables to SQL Server I needed a way to overcome the
slow performance of the Find method on my recordsets. Although this
can be done by accessing the table directly using dao and odbc, or by
using a parameter query that's customized in code for each search, I
was able to find a simple and fast solution based on the same native
Access SELECT query I used previously with native Access tables.

In my example I open an invoice editor form based on my Access select
query.

I added the following criteria to the InvoiceNumber column in the
design view of my query:

IIf(IsNull([Forms]![Enter/Edit
Invoices]![txtInvoiceToFind]),[Invoices].[InvoiceNumber],[Forms]![Enter/Edit
Invoices]![txtInvoiceToFind])

For readability and comments:

IIf(IsNull([Forms]![Enter/Edit Invoices]![txtInvoiceToFind]),
<THEN> [Invoices].[InvoiceNumber], <return all invoices>
<ELSE> [Forms]![Enter/Edit Invoices]![txtInvoiceToFind]) <selected
invoice>

I have a criteria field and two command buttons, BEGIN SEARCH and
CLEAR SEARCH, in one corner of the form.

When the form opens, the search criteria is null by default so the
query loads all invoices (thousands, fairly quickly) into the
recordset, and the first record appears in the form.

When I enter an invoice number in the search criteria textbox on the
form and click BEGIN SEARCH, the cmdBeginSearch onclick event
requeries the form (me.requery) and returns only the invoice sought.
At this point, I can edit the invoice. Note that the datasheet view
will reveal that only this invoice is
available in the recordset.

When I click CLEAR SEARCH its onclick event sets the search criteria
field to null and requeries the form, once again returning all
invoices to the recordset.

Hope this helps someone. It saved me a headache trying to balance my
native query with direct access to the tables.

Bob

re: Simple workaround to slow Find or unusable Seek on attached tables!

"Bob C." <bcanavan@bmghomes.com> wrote in message
news:2e5623fb.0409270917.3f33fda@posting.google.co m...[color=blue]
> When I migrated my tables to SQL Server I needed a way to overcome the
> slow performance of the Find method on my recordsets. Although this
> can be done by accessing the table directly using dao and odbc, or by
> using a parameter query that's customized in code for each search, I
> was able to find a simple and fast solution based on the same native
> Access SELECT query I used previously with native Access tables.
>
> In my example I open an invoice editor form based on my Access select
> query.
>
> I added the following criteria to the InvoiceNumber column in the
> design view of my query:
>
> IIf(IsNull([Forms]![Enter/Edit
>[/color]
Invoices]![txtInvoiceToFind]),[Invoices].[InvoiceNumber],[Forms]![Enter/Edi
t[color=blue]
> Invoices]![txtInvoiceToFind])
>
> For readability and comments:
>
> IIf(IsNull([Forms]![Enter/Edit Invoices]![txtInvoiceToFind]),
> <THEN> [Invoices].[InvoiceNumber], <return all invoices>
> <ELSE> [Forms]![Enter/Edit Invoices]![txtInvoiceToFind]) <selected
> invoice>
>
> I have a criteria field and two command buttons, BEGIN SEARCH and
> CLEAR SEARCH, in one corner of the form.
>
> When the form opens, the search criteria is null by default so the
> query loads all invoices (thousands, fairly quickly) into the
> recordset, and the first record appears in the form.
>
> When I enter an invoice number in the search criteria textbox on the
> form and click BEGIN SEARCH, the cmdBeginSearch onclick event
> requeries the form (me.requery) and returns only the invoice sought.
> At this point, I can edit the invoice. Note that the datasheet view
> will reveal that only this invoice is
> available in the recordset.
>
> When I click CLEAR SEARCH its onclick event sets the search criteria
> field to null and requeries the form, once again returning all
> invoices to the recordset.
>
> Hope this helps someone. It saved me a headache trying to balance my
> native query with direct access to the tables.
>
> Bob[/color]

My only comment would be "Why load all of them in the first place (or
ever)?". You're pulling all records over the network knowing full well
that the user will only ever ask for one of them at a time. I wouldn't
pull ANY records over until the user asked for the one they want.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Simple workaround to slow Find or unusable Seek on attached tables!

Your comments are fair and good.

Thank you for sharing that with us.

The basic rule here is (as you show) is to ALWAYS ask the user what they
want, and THEN load the form.

There is NO reason to load up a form without first having ask the user what
they want.

strInvoice = inputbox("what invoice to edit")

docmd.OpenForm "frmCustomerInvoice",,,"invoiceid = " & strInvoice

The above is all you need, and restricts the form to the ONE record.

A good design will thus NEVER load a up a form that is simply attached to a
table. So, even without sql server, I find tables with 50,000 records, and
about 5 to 10 users on a office network respond absolute instant. (remember,
reducing the number of records to a form also works when you DO NOT use sql
server). So, your tip does not only apply to sql server here!

I also outline a nice search concept here:

http://www.attcanada.net/~kallal.msn/Search/index.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

re: Simple workaround to slow Find or unusable Seek on attached tables!

Thanks for your comments, guys.

My error may have been in not posting the following footnote with my
solution:

My example was a simplification of the actual app. In practice, my
app accepts both vendor and invoice criteria for the search. The
recordset initially loads records for a single vendor, giving the user
the ability to switch to the datasheet view of the form and see all
the invoices for a single vendor, a relatively very small set. The
datasheet view is used so often that this approach is good enough for
now.

As the title of my first post may suggest, I needed to quickly provide
a way to replace the FIND method in the search code, and save the user
time in retrieving a single invoice. (I haven't even mentioned the
subform containing the itemized detail for each invoice.) My solution
worked (with no noticeable impact on network traffic), and the users
are happy. Now I can go back and make it efficient, as you correctly
describe in your replies.

Thanks again. All the best,

Bob C.