maximum number of open recordsets - Access 2003/2007

[Satheesh V]

hi,
I use DAO recordset to query database tables of MSAccess. I open the recordset and miss/forget to close it. Can anyone tell me the maximum number of recordsets that can be open at a time (on querying Access 2003 and Access 2007 table) after which it gives an error saying "no more tables can be open"?

Thanks,
Satheesh

[Stewart Ross]

Hi. We have an Insights article listing Access Specifications which lists the potential number of open tables as 2048, subject to reduction for internal system table usage.

Although 2048 appears to be quite generous, be aware that when you take into account the number of tables opened when running a typical multi-table SELECT query the limit on recordsets (rather than tables) is likely to be much lower than 2048.

Regardless of the limit, I would strongly advise you to ensure that all open recordsets are always closed at end of use.

-Stewart

[ADezii]

Keep in mind a lot has to do with the Scope of the Variable Declaration for the Recordset.

1. If Dim rst As DAO.Recordset is Declared locally such as in the Click() Event of a Command Button, then the Recordset will be Closed automatically as soon as you Exit the Click() Event.
2. If Private rst As DAO.Recordset is Declared in a Form's Code Module, then the Recordset will be Closed automatically as soon as you Close that Form.
3. If Public rst As DAO.Recordset is Declared in a Standard Code Module, then the Recordset will be Closed automatically as soon as your Database is Closed.
4. The above being said, you should 'always' Close a Recordset when you have finished it, and deallocate Memory assigned to the Object Variable as well, as in:
   

   rst.Close
   Set rst = Nothing

5. If you are not sure of the State of the Recordset, then:
   

   If Not (rst Is Nothing) Then
     rst.Close
     Set rst = Nothing
   End If