Tuesday, December 06, 2005

Be careful using CurrentDB

As we all know CurrentDB returns a reference to currently opened database, so we can open recordsets just using CurrentDB.OpenRecordset(). In most cases this works fine, but in complex and recursive procedures you can easy reach a limit of open references (or instances) CurrentDB can hold. Code below shows this:
Sub TestCurrentDB()
Dim i As Long, rst(1000) As DAO.Recordset
  For i = 1 To 1000
    Set rst(i) = CurrentDb.OpenRecordset( _
    "Select * from Table1", dbOpenDynaset)
    rst(i).MoveFirst
  Next i
End Sub



Code will stop at i=250 with error message 3048:
“Can’t open any more databases”
The workaround – is to declare a public variable dbs as dao.database, set it to CurrentDB at program startup and then use it instead of CurrentDB.
at

2 comments:

  1. M.Ivashkov5:57 PM

    I am not sure , may be you can try rst(i).close. It looks like a missleading message to me. CurrentDB does not hold anything, but create an object and assigns its reference to an rst array element. It seems access cannot hold >250 open connections? if it is so then why it works via a variable? should give the same error if >250 connections.

    ReplyDelete
  2. Alex Dybenko9:07 AM

    Hi,
    the problem with CurrentDB - when you call CurrentDB it creates a reference, and keep it, so limit can be easy reached. Calling DBEngine(0)(0) will PASS reference. Michael Kaplan (michka) have good explanation on this issue, for example here: https://groups.google.com/forum/?hl=en&fromgroups=#!topic/comp.databases.ms-access/XROIwkzZsPk

    ReplyDelete