[AccessD] Array as source for query

Charlotte,

That is a good question, and my guess is that it does not, at least not the
SAME problem.  IOW, what is running out is DAO recordsets that appear to be
in a pool.  I used the following code to "test the limits"

Function FETestOpenRst() As Integer
On Error GoTo Err_FETestOpenRst
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim col As Collection
    Set db = CurrentDb
    Debug.Print db.Name
    Set col = New Collection
    While True
        Set rst = db.OpenRecordset("usystblFEFWSysVars")
        col.Add rst
    Wend
Exit_FETestOpenRst:
Exit Function
Err_FETestOpenRst:
    Select Case Err
    Case 3014, 3048
        FETestOpenRst = col.Count
        'Debug.Print col.Count & " recordsets available to open."
        'MsgBox Err.Description & col.Count & " recordsets open.", , "Error
in Function basCallback.FETestOpenRst"
        Resume Exit_FETestOpenRst
    Case Else
        MsgBox Err.Description, , "Error in Function
basCallback.FETestOpenRst"
        Resume Exit_FETestOpenRst
    End Select
    Resume 0    '.FOR TROUBLESHOOTING
End Function

This does nothing more than open a table over and over, adding a pointer to
the recordset to a collection (to hold it open) untilo I got a JET error
that prevents opening any more recordsets.

When I just open my FE and run this, the first number I get is 2042.  That
represents the maximum number that I can open before I start to get an error
in CODE.  However in real life, I start getting errors when the number drops
down into the 1300 range, which means that for whatever reason, Access
starts to not populate forms and combos when its pool of available recordset
pointers (if there is such a thing as a pool) drops below a certain level.

So, can you offload this by using ADO recordsets?  Probably.  Can you bind a
combo to an ADO recordset?

In testing this (long ago), I started out by creating a set of combos bound
to a recordset.  I then copied that compo and pasted it back in, in groups
of ten, until I had groups of 100, then groups of 100 etc.  Opening and
closing until I started getting errors.  Later I built this function above
to do my testing.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Tuesday, January 23, 2007 1:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Array as source for query

Jurgen,

Does the restriction still cause problems if you use ADO disconnected
recordsets?  They could be written to and populated from an XML file as
well.

I haven't worked much in Access with them since version 2000 because my shop
insisted on DAO, but ADO.Net is so flexible, that I would suspect the answer
lies in ADO with Access.  I know I have an old demo that uses ADO to bounce
between an array and a recordset in memory and then writes it to an XML
file.

Charlotte Foust