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:
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.
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.
posted by Alex Dybenko | 10:09
2 Comments:
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.
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
Post a Comment
<< Home