Huge Access Database 
Author Message
 Huge Access Database

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



Fri, 13 Sep 2002 03:00:00 GMT  
 Huge Access Database
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

Quote:

>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



Fri, 13 Sep 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Access 2000 database get huge in size.

2. Accessing a huge Database(17Meg)

3. Inserting huge text files into Access database using VB6

4. Accessing a huge Database (17meg)

5. Inserting huge text files into Access database using VB6

6. Code Creating huge Database

7. Problem with Huge Database

8. Huge Database Problem!

9. Append queries causing HUGE database file.

10. Huge Tables & Database properties

11. Sorting Huge Databases In Memory

12. handling a huge database

 

 
Powered by phpBB® Forum Software