DLookup doesn’t release the database object?

[AHeyne]

On Accessjumpstart I read this today:

This is something you’d likely never know unless you’ve experienced the dreaded “Out of Resources” error, but DLookup doesn’t release the database object it creates after it’s done.
Garbage collection in Access seems to clean up after it, but calling it recursively in queries or in long code loops can eventually garner you a nice error message which is not at all clear.

This is new to me, I've never heard of it before.

Has anyone been able to reproduce this in practice and/or have more information about it?

 

[gemma-the-husky]

I'm not sure about that.

How would you know it's the dlookup that causes the issue, and not something else in the recursive loop.

What error did they report. Did they show some code? How many levels of recursion did they need to cause the issue?

I mean, as an example, if you write a recursive Fibonacci process it runs slower and slower for relatively low iteration counts, because the number of calls pushed on to the stack gets exponentially higher (and I do think it really is exponentially higher), but recursion to walk a tree say, is efficient to run and far easier to write than without recursion.

 

[ebs17]

to reproduce this in practice

Look for "ms access runtime error 3048 cannot open any more databases"

The bigger problem is not database objects, but rather each DLookup is a complete query that only retrieves one piece of content. In a mass application, you are therefore making a design error => a constant barrage of queries.
If you walk along the edge of the abyss, you shouldn't be surprised if you fall off sometimes.

 

[AHeyne]

Thanks @gemma-the-husky and @ebs17 .

I am primarily interested in reading up on what underpins the above statement from the article, as I was completely unaware of this until now.

Whether DLookups are generally useful is another matter, of course.

 

[ebs17]

I am primarily interested in reading up on what underpins the above statement from the article, as I was completely unaware of this until now.

Jonathan Halder, as the author of the article, can certainly provide further information on this. He certainly has some research and evidence to support his statement. Others do not have to laboriously gather what he has.

Regarding the DLookup replacement function shown: There are already many of these. I personally have two main points of criticism:
1) CurrentDb = new instantiation. For a sensible solution, I would definitely use a persistent DB reference (CurrentDbC, ThisDb). This is a point regarding resource consumption and performance.
2) I would avoid the laborious and limited assembly of an SQL statement in the function. As a DB developer, I can write an SQL statement myself and pass it in as an argument. Then I have real freedom, even if there are no suitable stored domains. The DLookup replacement also becomes DMax, DCount, etc. via the SQL statement.

 

[Josef P.]

Perhaps the problem occurs when more than 507 DLookup statements are used at the same time.
507 ... because I can reproduce the error 3048 (Cannot open any more databases) with CurrentDb by creating the 508th instance.
Maybe DLookup uses something similar to CurrentDb.

Unfortunately, I have no experience with this myself, as I also use a replacement function, but not because of the DB number restriction, but for performance (I always use the same DB instance) and usability reasons (Lookup function for DAO, ODBC and ADODB/OLEDB).

Note:
In the linked code, the DB instance that is retrieved by CurrentDb is closed with Close. This can also be omitted. It doesn't matter if it's there, but I would leave it out because OpenDatabase wasn't used anywhere - I don't close anything that I don't open myself in the code.

 

[MajP]

There are lots of reasons to roll your own domain functions, so this is nothing new
Provided twenty years ago

Microsoft Access tips: Extended DLookup()

Replacement for DLookup() in Microsoft Access. The replacement is faster, handles nulls correctly, and lets you specify which record you want returned (by specifying a sort order.)

allenbrowne.com

So this would be very slow to do 1000 times in a loop compared to simple VBA operations.

But this argument seems nonsensical. Instead of using a standard dlookup in a loop use this nice dlookup that also get a new reference to the current db and creates a recordset. Seems like a 5% solution to the real problem.

How about open a single recordset and move through that. I cannot think of a case where I would use a dlookup or this function in a loop. At a minimum like everyone has already said keep a persistent reference to the db.

 

[AHeyne]

Thanks for the answers so far and @Josef P. also for the test.

It might actually be easiest to ask Jonathan Halder. He's not on the forum, is he?


Just to make it clear once again:
I am not looking for a replacement for DLookup, I rarely use it and use more suitable approaches of various kinds.
I just wanted to mention this again, so that your efforts don't miss the point ;-)