By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,265 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,265 IT Pros & Developers. It's quick & easy.

maximum number of open recordsets - Access 2003/2007

P: 1
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"?

Jan 12 '11 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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.

Jan 12 '11 #2

Expert 5K+
P: 8,698
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:
    Expand|Select|Wrap|Line Numbers
    1. rst.Close
    2. Set rst = Nothing
  5. If you are not sure of the State of the Recordset, then:
    Expand|Select|Wrap|Line Numbers
    1. If Not (rst Is Nothing) Then
    2.   rst.Close
    3.   Set rst = Nothing
    4. End If
Jan 12 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.