I need to search a huge access database (over 2million records) from visual
basic.

There are 8 seperate fields that can be selected to search on these are:

company name
address line 1
district
county
business type
town
no. of staff
post code

I have tried using a dynaset for the search, which works fine when only one
field is selected, but when i do a search on multiple fields (e.g. customer
name & county) the application grinds to a halt while the dynaset is being
read in.

Is there a quick way to do this selection? I have wondered about using a
multi field index from within Access to do this, but am unsure of where to
start!

I need to distribute this application so access is the easiest choice of
database

Thanks in advance

Jim

Jim,
You have only one choice to avoid loading all 2 million rows. I've not been
able to confirm if this will work with Access. It may fetch all 2 mil
anyway.

First add an index for each column that will be used as part of the search.
This will slow inserts on new records but you don't have much of a choice if
you want to prevent fetching all rows.

Second, take the user's search parameters and make a single SQL statement.
You can create a few query examples by hand and try them before coding the
SQL string creation. Running these from a query window should tell you if
you are getting only the rows you want or all 2 million. You should see
nearly instant response if Access is getting only the wanted rows and a long
wait if not.

A true database server such as SQL Server, Oracle, or DB2 is probably your
only choice if this doesn't work. Also maybe Foxpro but I'm not sure how its
data engine works.

Let us know the results. I've never had an Access db big enough to confirm
if this is possible.

Oscar Bowyer