AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

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.

2 Comments:

Blogger M.Ivashkov said...

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.

5:57 PM  
Blogger Alex Dybenko said...

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

9:07 AM  

Post a Comment

<< Home