[AccessD] Array as source for query

>> Can you bind a combo to an ADO recordset?

You can bind forms to them, so I don't see why you couldn't bind a combo
to them, but you'd have to use code to set it.

Charlotte 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Tuesday, January 23, 2007 1:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com