Searching through Access Database

Searching through Access Database

Post by Brandt Linkowsk » Thu, 22 Apr 1999 04:00:00



I am writing the front end for an Access database using VB 5 and have to do
a lot of searching to compare certain values. Some of the tables have been
loaded into a snapshot array, and some of the others must be accessed
through a data control due to their larger size. Below is some code to
illustrate:

If datRim.Recordset.RecordCount > 0 Then
        If datRim.Recordset.Fields("MANUFACTURER_FK") <> "" Then
            snSnapshotArray(MANUFACTURER).MoveFirst
            Do While snSnapshotArray(MANUFACTURER).EOF <> True
                If snSnapshotArray(MANUFACTURER).Fields("PARTY_ID") =
datRim.Recordset.Fields("MANUFACTURER_FK") Then
                    'code here to do stuff
                End If
                snSnapshotArray(MANUFACTURER).MoveNext
            Loop
        Else
            'code here to do stuff
        End If
End If

The problem is when some of these tables grow very large the application
slows dramatically.

 Is there a way to SORT through the snapshotarray or the Access records
using a sorting technique like 'Quicksort', 'Bubblesort' or 'Shellsort'?
Your suggestions are very much appreciated.

 
 
 

Searching through Access Database

Post by Jon Pop » Thu, 22 Apr 1999 04:00:00


When you populate your snapshot array, use an "ORDER BY" SQL expression to
order your resultset.

--
Jon Pope
Coherent Technologies, Inc.
(Remove .NoSpam Before Replying)


> I am writing the front end for an Access database using VB 5 and have to
do
> a lot of searching to compare certain values. Some of the tables have been
> loaded into a snapshot array, and some of the others must be accessed
> through a data control due to their larger size. Below is some code to
> illustrate:

> If datRim.Recordset.RecordCount > 0 Then
>         If datRim.Recordset.Fields("MANUFACTURER_FK") <> "" Then
>             snSnapshotArray(MANUFACTURER).MoveFirst
>             Do While snSnapshotArray(MANUFACTURER).EOF <> True
>                 If snSnapshotArray(MANUFACTURER).Fields("PARTY_ID") =
> datRim.Recordset.Fields("MANUFACTURER_FK") Then
>                     'code here to do stuff
>                 End If
>                 snSnapshotArray(MANUFACTURER).MoveNext
>             Loop
>         Else
>             'code here to do stuff
>         End If
> End If

> The problem is when some of these tables grow very large the application
> slows dramatically.

>  Is there a way to SORT through the snapshotarray or the Access records
> using a sorting technique like 'Quicksort', 'Bubblesort' or 'Shellsort'?
> Your suggestions are very much appreciated.



 
 
 

Searching through Access Database

Post by Coat » Thu, 22 Apr 1999 04:00:00


1) Try opening the recordet like Table and use Seek method, or
2) Use the FindFirst method in the snapshot

Take a look to your VB online documentation ...

--
Alberto Borbolla
Microsoft VB MVP
Tecnologia en Sistemas Mexico


> I am writing the front end for an Access database using VB 5 and have to
do
> a lot of searching to compare certain values. Some of the tables have been
> loaded into a snapshot array, and some of the others must be accessed
> through a data control due to their larger size. Below is some code to
> illustrate:

> If datRim.Recordset.RecordCount > 0 Then
>         If datRim.Recordset.Fields("MANUFACTURER_FK") <> "" Then
>             snSnapshotArray(MANUFACTURER).MoveFirst
>             Do While snSnapshotArray(MANUFACTURER).EOF <> True
>                 If snSnapshotArray(MANUFACTURER).Fields("PARTY_ID") =
> datRim.Recordset.Fields("MANUFACTURER_FK") Then
>                     'code here to do stuff
>                 End If
>                 snSnapshotArray(MANUFACTURER).MoveNext
>             Loop
>         Else
>             'code here to do stuff
>         End If
> End If

> The problem is when some of these tables grow very large the application
> slows dramatically.

>  Is there a way to SORT through the snapshotarray or the Access records
> using a sorting technique like 'Quicksort', 'Bubblesort' or 'Shellsort'?
> Your suggestions are very much appreciated.


 
 
 

Searching through Access Database

Post by SITAR » Mon, 26 Apr 1999 04:00:00


are you using DAO JET, or ADO ..... is it an ODBC connection???

I am just a beginner in VB, but I would like to help.  I posted an example
program here a few days ago, which worked just fine even when the three related
tables had 300,000 records.
I did SQL queries on the two related tables, each time I moved to a new record
in the Master table. The queries were done on fields which were INDEXED.  I
didnt even have to use find, or seek. Perhaps if you looked at that code, it
would give you some ideas about different ways , (hopefully faster ways), of
finding the records you want.

I would definitely suggest taking a very large table, as a test table... and
experiment and bench mark different techniques for finding the records you
need, and when you find THE FASTEST, most efficient techque,... THEN
INCORPORATE that technique in a complicated application.   DONT make a complex
app first, on small test tables,..AND only find out in PRODUCTION, when tables
grow large,.. that those techniques are slow and inefficient.....

if you email me,... i will be happy to send you the code for my example
application.... if it would help in any way...

sitaram at xxxaol.com (remove xxx)