[AccessD] Find First in an Array?

Hi Rocky

When you open a recordset it is read once into memory and Access doesn't reread the table if not needed.

Seek is so fast that you can use it for recursive calls at amazing speed.
Here's my old posting from 2002-02-06 on this. Note the use of a static recordset to keep it :

<archive>
Hi Bruce

You have several options. One, however, won't do - as noted by Arthur -
and that is a recursive query using SQL only; Access can't be fooled and
will claim about a circular reference. Your only chance is to create a
query resolving a limited number of levels only, say, 8 or 10.

But you can cover the recursive call in a domain aggregate function like
DLookup(). This is, however, very slow as DLookup() calling the query
will run for each and every record. For more than some dozens of records
this will most likely be unacceptable.

The fastest way, for an unlimited number of levels, I've found is to
create a lookup function which walks the tree for each record. This can
output either the level of the record or a compound key build by the key
of the record and all keys above.

As the lookup function will use the same recordset for every call, you
can make it static, and (for Jet) you can improve further by using Seek
to locate the records.
Here's an example which will give you an idea:

<code>

Function RecursiveLookup(ByVal lngID As Long) As String

  Static dbs      As Database
  Static tbl      As TableDef
  Static rst      As Recordset

  Dim lngLevel    As Long
  Dim strAccount  As String

  If dbs Is Nothing Then
    ' For testing only.
    ' Replace with OpenDatabase of backend database file.
    Set dbs = CurrentDb()
    Set tbl = dbs.TableDefs("tblAccount")
    Set rst = dbs.OpenRecordset(tbl.Name, dbOpenTable)
  End If

  With rst
    .Index = "PrimaryKey"
    While lngID > 0
      .Seek "=", lngID
      If Not .NoMatch Then
        lngLevel = lngLevel + 1
        lngID = !MasterAccountFK.Value
        If lngID > 0 Then
          strAccount = str(!AccountID) & strAccount
        End If
      Else
        lngID = 0
      End If
    Wend
    ' Leave recordset open.
    ' .Close
  End With

'  Don't terminate static objects.
'  Set rst = Nothing
'  Set tbl = Nothing
'  Set dbs = Nothing

'  Alternative expression for returning the level.
'  (Adjust vartype of return value of function.)
'  RecursiveLookup = lngLevel ' As Long
  RecursiveLookup = strAccount

End Function

</code>

This assumes a table with a primary key ID and a foreign (master) key
pointing to the parent record - and a top level record (not used) with a
visible key (AccountID) of 0.

Now your tree will be nicely shown almost instantaneously using a query
like this where Account will be the visible compound key:

  SELECT
    *, RecursiveLookup([ID]) AS Account
  FROM
    tblAccount
  WHERE
    (AccountID > 0)
  ORDER BY
    RecursiveLookup([ID]);

/gustav

> I am completely stymied on this one.  Can any help, offer any code samples,
> comments,  etc, please?
>
> I have an n-level tree data structure implemented as a single table with a
> set of recursive foreign keys, i.e. the table is related to itself in
> several ways each way producing a different tree structure.  Lets call these
> "views".  Any branch can be any depth.  The order of the branches is,
> relatively, unimportant as it is based purely on an alphabetic sort of the
> branch title.
>
> I have a great form for maintaining the tree, using the treeview control.
> This provides the desired navigation, display, edit etc.
>
> BUT, now I need to print each tree view as a report.  I have tried creating
> cascading queries as the recordsource - no dice, I don't know how many
> levels a particular view will have.  I have tried to create a report with a
> recordset returning function as a recordsource, and cannot get this to work.

</archive>

/gustav


>>> rockysmolin at bchacc.com 19-02-2009 16:23 >>>
Fortunately this is a front end table so no going over the network.  And I'm
going to try Shamil's Seek approach - be a simple change.  

But you're right about it being faster in memory anyway.  What I'm trying to
do is stop the flashing and repainting.  What I did yesterday is change the
calls in the _Open event to:

Echo False
Scale the Form
Translate the form
Maximize the Form
Echo On


This seem to have had a real good effect on the forms.

Also noticed that the first time I open a form I get one quick repaint (I'm
using Chinese to test this because it using Unicode and seems to have a
little more complexity) - the second time I open the form it's faster -
almost like it was already translated even though I close the form on exit.
 


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com 
www.bchacc.com 
 
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, February 19, 2009 4:49 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Find First in an Array?

Rocky,

A table may or may not be "noticeably slower" however you have already said
that you had issues. 
Once you cache the data, getting data out of a cache structure will be 100
to 1000 times faster. 
Remember that a table is NOT cached (though a disconnected ADO recordset is
I guess).  DAO requires a trip to the disk OVER THE NETWORK (if any).

As for going back to the beginning, please do but do not let it stop you
from implementing what I will build for you.  Just remember that a class
object is just like any other object in Access (cbo, recordset, query) it is
just a tool that anyone can use once built.

I will do the building for this instance.

John W. Colby
www.ColbyConsulting.com 


Rocky Smolin at Beach Access Software wrote:
> I'm wondering if an array or collection will be noticeably faster than 
> a recordset since there are about 2500 records in the controls table 
> and so it's probably all in memory already.  The fields are not 
> indexed - so I'll bet I could get a little boost in performance by 
> indexing the fields I search on.
> 
> OTOH, it's a good opportunity to learn something about classes.  I'll 
> post the table layout tomorrow.  But I think to be fair to John and 
> anyone else who wants to help, I should go back to all those saved 
> emails on the classes class and start from the beginning.