[AccessD] fastest way to execute sql

Mark,

Actually, it is very simple.

You will need to know the general syntack for the backend database, but it
should be something like this:

Set the query type as passthrough.

SELECT *
FROM MyTable
WHERE MyMemoField LIKE '%TheWord%' OR MyMemoField LIKE '%AnotherWord%'

Open the query properties.
Set the ODBC string.
Set the timeout to 0.  This will let it run until it completes.

This will let the server to run the query and it will return only the results
back to Access, not all 1.2 mil rows.

If you want the results in an Access table, then you can use this query as the
source for a make table or append query.

NOTE:  The synatx I used above is generic.  You might have to use " instead of
'.  The % is the wild card for SQL Server like the * is for Access.  It might
be different in Informix.

Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge


Quoting accessd-request at databaseadvisors.com:

> Date: Thu, 13 Jul 2006 20:32:59 +0000
> From: "Mark A Matte" <markamatte at hotmail.com>
> Subject: Re: [AccessD] fastest way to execute sql
> To: accessd at databaseadvisors.com
> Message-ID: <BAY121-F219DE40FE5014F9980C62ED26E0 at phx.gbl>
> Content-Type: text/plain; format=flowed
>
> Robert,
>
> Thanks for the feedback...I have never used pass-throughs...here is my
> scenario.  Access db connected to Informix via ODBC.  I need to do some
> fuzzy/wildcard searches against a number of  memo/blob fields.  I have tried
> to do these types of searches in the past on the linked tables with no
> success(time outs or just never finishes).  I typically pull ALL(1.2 mil)
> records from the needed table and then do the fuzzy/wildcard searches on
> local tables.  This has been the only way I got the info needed.  In this
> scenario, would your suggestion still apply...and if so, do you mean create
> my fuzzy/wildcard searches as a pass-through...and then use the pass-through
> as the source in another query to append to a local table?
>
> Thanks,
>
> Mark A. Matte