The DBengine(0)(0) problem

684 views
Skip to first unread message

Mark Johnson

unread,
Mar 10, 2003, 8:23:41 AM3/10/03
to
I suggested this, already. 'Byzantine' was the reply, perhaps fairly.
I wonder if not declaring the return is a potential problem. I wonder,
more to the point, if using Set creates an instance that must be
closed. If repeatedly called, does each Set just add to memory until
memory is quickly exhausted? I don't know.


' Either return pointer to current db, or - if some unlikely problem - return NULL.

Public Function GetThisDB()
On Error GoTo Err_GetThisDB

' Will likely never do anything but SET here, and exit.
' If it's not a dot-Name match with workspace 0, db 0, then it will raise an error.
' (dot-Name : should return the full path for database)
' (strThisDBFilename : full filename, part of the path, something to uniquely identify)
If InStr(1, DBEngine(0)(0).Name, strThisDBFilename) > 0 Then
Set GetThisDB = DBEngine(0)(0)
Exit Function
End If

Exit_GetThisDB:
' If the exceptional case that workspace 0, db 0 is not the database you expect,
' look through all databases in workspace 0 to find what you want - or return null.

Dim numDbCount
' returns 1-based scalar - slide down to 0-based
numDbCount = DBEngine(0).Databases.Count - 1

Do While numDbCount > 0
If InStr(1, DBEngine(0)(numDbCount).Name, strThisDBFilename) > 0 Then
Set GetThisDB = DBEngine(0)(numDbCount)
Exit Function
End If
numDbCount = numDbCount - 1
Loop

Exit_RtnNull:
GetThisDB = vbNullChar
Exit Function

Err_GetThisDB:
Select Case Err.Number
Case 3265
Resume Exit_GetThisDB
End Select

Resume Exit_RtnNull

End Function

Michael (michka) Kaplan

unread,
Mar 10, 2003, 11:11:13 AM3/10/03
to
No, it does not. And since it would never be necessary in a production
app, so it is probably overkill to do all this.


--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

"Mark Johnson" <1023...@compuserve.com> wrote in message
news:184p6vknocuc49cui...@4ax.com...

Mark Johnson

unread,
Mar 10, 2003, 4:19:30 PM3/10/03
to
"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
wrote:

>No, it does not. And since it would never be necessary in a production
>app, so it is probably overkill to do all this.

It doesn't need to be freed, in other words. And I thought the
consensus was that this was a problem to be feared, and something that
you would have to allow for in a "production app". The old threads
show that you were maybe the first to point out the compact/wizard
problem. Does this mean you've changed your mind?

I think that may even have been the reason I initially started using
CurrentDB, before I knew about it creating an entire collection on the
fly whenever invoked. And that was, I suppose, the sense that a
passive reference to the db, without explicitly checking, might,
somehow, in some way, produce a mistaken reference. And then I saw
these threads that, indeed, in rare circumstances, it did.

David W. Fenton

unread,
Mar 10, 2003, 4:43:26 PM3/10/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<184p6vknocuc49cui...@4ax.com>:

> ' Will likely never do anything but SET here, and exit.
> ' If it's not a dot-Name match with workspace 0, db 0, then it
> will raise an error.
> ' (dot-Name : should return the full path for database)
> ' (strThisDBFilename : full filename, part of the path,
> something to uniquely identify)
> If InStr(1, DBEngine(0)(0).Name, strThisDBFilename) > 0 Then
> Set GetThisDB = DBEngine(0)(0)
> Exit Function
> End If

I agree with MichKa that you're doing something that is of no
value.

But I would point out that there is no place in your code for you
to set the value of strThisDBFilename. You could pass it as an
optional argument, or as a ByRef variable value. Of course, that
won't really work that well in a query, so you could have a global
variable for the name.

But that makes little sense to me.

CurrentDB returns the correct name, so if strThisDBFilename is
unassigned, set it by using CurrentDB.Name. Of course, it needs to
persist, the scope of the variable will have to be outside this
function.

Again, I just don't see the point of all this.

There are simply no circumstances where you need to call
CurrentDb() or DBEngine(0)(0) in a loop that cannot be more
properly avoided either by better code structure or by using SQL to
get the lookup values.

It's quite clear that this irrational fear of CurrentDB leads to
all kinds of jumping through hoops, all of which would be
unnecessary if you simply structured your code correctly in the
first place.

I've decided that I cannot think of a circumstance where
DBEngine(0)(0) would be required, where CurrentDB() would not be
preferable.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Mark Johnson

unread,
Mar 10, 2003, 6:26:24 PM3/10/03
to
Mark Johnson <1023...@compuserve.com> wrote:

>' Either return pointer to current db, or - if some unlikely problem - return NULL.

>Public Function GetThisDB()
> On Error GoTo Err_GetThisDB

> ' Will likely never do anything but SET here, and exit.
> ' If it's not a dot-Name match with workspace 0, db 0, then it will raise an error.
> ' (dot-Name : should return the full path for database)
> ' (strThisDBFilename : full filename, part of the path, something to uniquely identify)
> If InStr(1, DBEngine(0)(0).Name, strThisDBFilename) > 0 Then
> Set GetThisDB = DBEngine(0)(0)
> Exit Function
> End If

>Exit_GetThisDB:

Should probably point out that the error 3265 was for testing, and
shouldn't have been included, here. If the test fails, it just simply
falls through to Exit_GetThisDB. There should always be a db0,0, so no
possibility of error in that.

Mark Johnson

unread,
Mar 10, 2003, 6:31:45 PM3/10/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>> ' Will likely never do anything but SET here, and exit.
>> ' If it's not a dot-Name match with workspace 0, db 0, then it
>> will raise an error.
>> ' (dot-Name : should return the full path for database)
>> ' (strThisDBFilename : full filename, part of the path,
>> something to uniquely identify)
>> If InStr(1, DBEngine(0)(0).Name, strThisDBFilename) > 0 Then
>> Set GetThisDB = DBEngine(0)(0)
>> Exit Function
>> End If

>I agree with MichKa that you're doing something that is of no
>value.

This insures that the db pointer is to the database you want, not to
one it's not supposed to be. That's what all the other threads were
about - the 'global db' and so on?

>But I would point out that there is no place in your code for you
>to set the value of strThisDBFilename. You could pass it as an
>optional argument

Just set as a private constant. You know what the name of your own
database is supposed to be.


>won't really work that well in a query, so you could have a global
>variable for the name.

Just a CONST.


>There are simply no circumstances where you need to call
>CurrentDb() or DBEngine(0)(0) in a loop that cannot be more
>properly avoided either by better code structure or by using SQL to
>get the lookup values.

But you need to have a db reference to create the recordset to return
the SQL query.

So, fGetThisDB().OpenRecordset ( <SQL> )


>I've decided that I cannot think of a circumstance where
>DBEngine(0)(0) would be required, where CurrentDB() would not be
>preferable.

CurrentDB is just slower. Again, there's a bunch of threads, from
years ago, commenting on this. And you can see it, in practice. The
reason for having to open CurrentDB locally, is that if you add to the
db, it'll be current. Otherwise, you run the risk it won't be. But the
dbengine is just the pointer to whatever's there. So it's always
current, as well.

Michael (michka) Kaplan

unread,
Mar 10, 2003, 7:21:08 PM3/10/03
to
"Mark Johnson" <1023...@compuserve.com> wrote...

> It doesn't need to be freed, in other words.

Correct.

> And I thought the consensus was that this was a problem
> to be feared, and something that you would have to allow
> for in a "production app".

I do not know about the consensus (until recently, no one was willing
to believe there was an issue at all!). But the vast majority of apps
do not either run wizards inside of them or compact the current db
from within the db in a way that could cause this problem.

So again, this should not be needed in a production app.

> The old threads show that you were maybe the first to point
> out the compact/wizard problem.

I believe I was, yes.

> Does this mean you've changed your mind?

No, not at all. But I caution people to *not* use DBEngine(0)(0) here.
My advice is still sound, and requires no code hacks.

> I think that may even have been the reason I initially started
> using CurrentDB, before I knew about it creating an entire
> collection on the fly whenever invoked.

Well, you were on the right path for a while then. :-)

> And that was, I suppose, the sense that a passive reference to
> the db, without explicitly checking, might, somehow, in some way,
> produce a mistaken reference.

Well, not exactly.

> And then I saw these threads that, indeed, in rare
> circumstances, it did.

However, the rare circumstances are understood -- at least well enough
to have a plan of action.

Your code is not needed in this case.

Mark Johnson

unread,
Mar 11, 2003, 1:19:52 AM3/11/03
to
"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
wrote:

>> And then I saw these threads that, indeed, in rare


>> circumstances, it did.
>
>However, the rare circumstances are understood -- at least well enough
>to have a plan of action.
>
>Your code is not needed in this case.

So, in other words, for all those threads, and all that about using
'currentdb' . . it boils down to, use dbengine if you want - and don't
give it a second thought? which is just fine with me.

Trevor Best

unread,
Mar 11, 2003, 3:24:18 AM3/11/03
to
On Mon, 10 Mar 2003 15:31:45 -0800, in message
<iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
<1023...@compuserve.com> wrote:

>Just set as a private constant. You know what the name of your own
>database is supposed to be.

Until someone renames the database.

>>won't really work that well in a query, so you could have a global
>>variable for the name.
>
>Just a CONST.

Again, one small step for man, one giant cockup for your database.


>So, fGetThisDB().OpenRecordset ( <SQL> )
>
>
>>I've decided that I cannot think of a circumstance where
>>DBEngine(0)(0) would be required, where CurrentDB() would not be
>>preferable.
>
>CurrentDB is just slower. Again, there's a bunch of threads, from
>years ago, commenting on this. And you can see it, in practice. The
>reason for having to open CurrentDB locally, is that if you add to the
>db, it'll be current. Otherwise, you run the risk it won't be. But the
>dbengine is just the pointer to whatever's there. So it's always
>current, as well.

I think you missed David's point, the speed difference of CurrentDB vs
DbEngine does not matter, you call it once in your app and set a
global variable, that's what a lot of people here do, me included.

--
Often statistics are used as a drunken man uses lampposts -
for support rather than illumination.

(replace sithlord with trevor for email)

Michael (michka) Kaplan

unread,
Mar 11, 2003, 8:43:57 AM3/11/03
to
"Mark Johnson" <1023...@compuserve.com> wrote...

> So, in other words, for all those threads, and all that about using
> 'currentdb' . . it boils down to, use dbengine if you want - and
don't
> give it a second thought? which is just fine with me.

Um, huh?

I give up. The words are simple, but clearly you want to use it and
are going to come to such a conclusion no matter what I say.

My recommendation is to use CurrentDb. You could call it once and
cache the return and then you will have no problems. And no extraneous
code.

Mark Johnson

unread,
Mar 11, 2003, 5:55:28 PM3/11/03
to
Trevor Best <sith...@besty.org.uk> wrote:

>On Mon, 10 Mar 2003 15:31:45 -0800, in message
><iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
><1023...@compuserve.com> wrote:

>>Just set as a private constant. You know what the name of your own
>>database is supposed to be.

>Until someone renames the database.

Then they'd just rename the constant. You mention, below, that you
store a global reference to the dbengine pointer. But why do that,
since it's only a pointer? Wouldn't the only one which you'd save,
globally, be the currentdb collection? And if you save it, globally,
what would happen if you used temporary tables, or added queries, or
tables, etc? Would they be part of that collection?


>>>I've decided that I cannot think of a circumstance where
>>>DBEngine(0)(0) would be required, where CurrentDB() would not be
>>>preferable.

>>CurrentDB is just slower. Again, there's a bunch of threads, from
>>years ago, commenting on this. And you can see it, in practice. The
>>reason for having to open CurrentDB locally, is that if you add to the
>>db, it'll be current. Otherwise, you run the risk it won't be. But the
>>dbengine is just the pointer to whatever's there. So it's always
>>current, as well.

>I think you missed David's point, the speed difference of CurrentDB vs
>DbEngine does not matter, you call it once in your app and set a
>global variable, that's what a lot of people here do, me included.

You use currentdb, saved globally on startup? I thought you said you
used dbengine, wherever required locally?

Mark Johnson

unread,
Mar 11, 2003, 6:09:13 PM3/11/03
to
"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
wrote:

>"Mark Johnson" <1023...@compuserve.com> wrote...

>> So, in other words, for all those threads, and all that about using
>> 'currentdb' . . it boils down to, use dbengine if you want - and

>> give it a second thought? which is just fine with me.

>I give up. The words are simple, but clearly you want to use it and


>are going to come to such a conclusion no matter what I say.

I certainly didn't mean to touch a nerve. Because what you write is
not only unfair, but just simply wrong. I "clearly" have no opinion on
this, either way. There is a right way to do things, and a wrong way.
It's absolute, oftentimes. I suggested a solution because you and
others have repeatedly suggested a problem. And there have been
threads, recently, on just this. Now you suggest that using a global
ref to the currentdb collection is the absolutely the right way. And
you're very insistent about it. And that's fine with me. But I asked
in good faith because I wanted to know. You seemed like one who knew,
and who perhaps had changed his mind. So that was something that
wasn't explained. As I said, there have been a lot of threads on this
problem - or, I guess, supposed problem - of a phantom reference, and
the speed differential. They are all recorded on groups.google.


>My recommendation is to use CurrentDb. You could call it once and
>cache the return and then you will have no problems. And no extraneous
>code.

In calling it once, you also have to free the collection, correct? So
when you exit, you need to call a cover function to set db = nothing?
There's your "code", as well as the cover to set it, and create the
global variable. If your objection is that, then I don't understand.
It was suggested that failing to set to nothing could even cause
problems. And if it's a collection that is not automatically updated,
then wouldn't "extraneous code" be required to keep the collection
current under those circumstances when object are added?

David W. Fenton

unread,
Mar 11, 2003, 7:05:51 PM3/11/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<bbqs6vs78s8n6h4v9...@4ax.com>:

>In calling it once, you also have to free the collection, correct?
>So when you exit, you need to call a cover function to set db =
>nothing? There's your "code", as well as the cover to set it, and
>create the global variable. If your objection is that, then I
>don't understand. It was suggested that failing to set to nothing
>could even cause problems. And if it's a collection that is not
>automatically updated, then wouldn't "extraneous code" be required
>to keep the collection current under those circumstances when
>object are added?

In regard to cleanup when closing the application, there is
absolutely no difference whatsoever between CurrentDB() and
DBEngine(0)(0) -- both are object variables that need to be cleaned
up if you want to ensure that your app will successfully close when
terminated properly.

So, the only issues are:

1. will you always end up with a reference to the database you are
expecting, AND

2. will it cause a noticeable performance hit.

As has been explained ad nauseam:

A1. DBEngine(0)(0) in some very rare circumstances will return a
reference that points to a wizard instead of the MDB open in the
Access user interface.

A2. CurrentDB() is several times slower than DBEngine(0)(0), but as
there is no circumstance in which you'd need to create a reference
to the currently open MDB in any kind of loop, the performance
advantage of DBEngine(0)(0) makes no difference.

David W. Fenton

unread,
Mar 11, 2003, 7:08:38 PM3/11/03
to
sith...@besty.org.uk (Trevor Best) wrote in
<s17r6v07oe4di6b6a...@4ax.com>:

>On Mon, 10 Mar 2003 15:31:45 -0800, in message
><iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
><1023...@compuserve.com> wrote:
>
>>Just set as a private constant. You know what the name of your
>>own database is supposed to be.
>
>Until someone renames the database.

Or moves it.

>>>won't really work that well in a query, so you could have a
>>>global variable for the name.
>>
>>Just a CONST.
>
>Again, one small step for man, one giant cockup for your database.

Heavens, I always know *exactly* where my applications are going to
be run from. And if the clients don't like the choices I've made,
they can go hire someone who can write code that adapts to *their*
needs, instead of those of the programmer!!! Harrumph!

>>So, fGetThisDB().OpenRecordset ( <SQL> )
>>
>>>I've decided that I cannot think of a circumstance where
>>>DBEngine(0)(0) would be required, where CurrentDB() would not be
>>>preferable.
>>
>>CurrentDB is just slower. Again, there's a bunch of threads, from
>>years ago, commenting on this. And you can see it, in practice.
>>The reason for having to open CurrentDB locally, is that if you
>>add to the db, it'll be current. Otherwise, you run the risk it
>>won't be. But the dbengine is just the pointer to whatever's
>>there. So it's always current, as well.
>
>I think you missed David's point, the speed difference of
>CurrentDB vs DbEngine does not matter, you call it once in your
>app and set a global variable, that's what a lot of people here
>do, me included.

I don't think Mark has too much experience with Access, at least
not from the posts I've seen of his.

Many people with lots more experience than Mark and with more
experience than me have disagreed on this subject. I don't see that
there's much dispute any longer -- for me, CurrentDB() is the
obvious choice.

But others see it differently.

David W. Fenton

unread,
Mar 11, 2003, 7:19:38 PM3/11/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<t3qs6v0ogb1bifhc7...@4ax.com>:

>Trevor Best <sith...@besty.org.uk> wrote:
>
>>On Mon, 10 Mar 2003 15:31:45 -0800, in message
>><iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
>><1023...@compuserve.com> wrote:
>
>>>Just set as a private constant. You know what the name of your
>>>own database is supposed to be.
>
>>Until someone renames the database.
>

>Then they'd just rename the constant. . . .

Rename?

Who is this "they" you are speaking of?

The client for whom you've built the application?

You'd want your client to be required to

You mention, below, that you

>store a global reference to the dbengine pointer. . .

He said to the pointer created with CurrentDB().

> . . . But why do that,


>since it's only a pointer? Wouldn't the only one which you'd save,
>globally, be the currentdb collection? And if you save it,
>globally, what would happen if you used temporary tables, or added
>queries, or tables, etc? Would they be part of that collection?

Eh?

What are you talking about?

A single reference created upon opening will have all collections
in it, regardless of which method you use. If you add to the
collections and have a persistent reference open, you'll need to
refresh the collections to have access to the new items via the
collections.

There is no difference in this regard between CurrentDB() and
DBEngine(0)(0).

The only difference is that *new* call to CurrentDB() after the
database has been opened and additions to/deletions from
collections made will have the most up-to-date collections. The
collections of a reference created with DBEngine(0)(0) will include
the items in the collections at the time the database is opened
forever, *regardless of when you create your reference*, unless you
manually refresh the collections.

So, if you open a database, add some querydefs or whatever, and
then in DAO set a database variable with a reference derived from
DBEngine(0)(0), the collections will *not* know anything about your
newly created querydefs, because they were not there when the
database was opened. CurrentDB(), on the other hand, refreshes the
collections, and will know about any objects at the time the
reference was returned from CurrentDB().

If the reference created with CurrentDB() is persistent and you add
to the collections after the reference is created, you will need to
refresh the collections.

I've never had to really worry about this, in any event, as I
almost never add any new objects to a fornt end, in order that I
can avoid bloat (so the front end never has to be compacted).

So, the references issue is irrelevant to me.

That is, if CurrentDB() did not refresh the collections, hardly any
of my existing code would break (if any at all).

And even though that performance hit is unnecessary in my
applications, it still isn't significant enough to convince me to
use DBEngine(0)(0).

>>>>I've decided that I cannot think of a circumstance where
>>>>DBEngine(0)(0) would be required, where CurrentDB() would not
>>>>be preferable.
>
>>>CurrentDB is just slower. Again, there's a bunch of threads,
>>>from years ago, commenting on this. And you can see it, in
>>>practice. The reason for having to open CurrentDB locally, is
>>>that if you add to the db, it'll be current. Otherwise, you run
>>>the risk it won't be. But the dbengine is just the pointer to
>>>whatever's there. So it's always current, as well.
>
>>I think you missed David's point, the speed difference of
>>CurrentDB vs DbEngine does not matter, you call it once in your
>>app and set a global variable, that's what a lot of people here
>>do, me included.
>
>You use currentdb, saved globally on startup? I thought you said
>you used dbengine, wherever required locally?

Trevor's t-functions (replacements for the built-in domain
aggregate functions) all were written with DBEngine(0)(0), most
likely because they were created in Access 2, when that was the
recommended methodology (did CurrentDB() even exist then? I can't
remember!).

After I pointed out how to add an optional parameter for a global
db variable, Trevor has agreed that CurrentDB() could be just as
fast as DBEngine(0)(0), and that it would still leave the code
completely independent of the app in which it is called.

At least, that's my understanding of what's gone on here.

Over to you, Trevor?

Michael (michka) Kaplan

unread,
Mar 11, 2003, 9:28:42 PM3/11/03
to
Mark,

My talk about the global var was an answer to your perf concerns. It
has nothing to do with what I would recommend. Because proper
performance work involves behcmarking, and I honestly doubt there is
ever a case that would see CurrentDb behind a perf hit.

As to your insistence on the need for a "Set db = Nothing", this is
also not needed. Neither is db.Close -- in fact, the latter caused
bugs in prior versions of Access.

The only never you hit was in the mischaracterization of prior posts.

SUMMARY: There is nothing to worry about here, and there is no need
for you to use the code you posted. Just use CurrentDb.


--
MichKa [MS]

This posting is provided "AS IS" with
no warranties, and confers no rights.

"Mark Johnson" <1023...@compuserve.com> wrote in message
news:bbqs6vs78s8n6h4v9...@4ax.com...

Mark Johnson

unread,
Mar 11, 2003, 9:55:59 PM3/11/03
to
"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
wrote:

>"Mark Johnson" <1023...@compuserve.com> wrote...

>> So, in other words, for all those threads, and all that about using
>> 'currentdb' . . it boils down to, use dbengine if you want - and

>> give it a second thought? which is just fine with me.

>I give up. The words are simple, but clearly you want to use it and


>are going to come to such a conclusion no matter what I say.

I certainly didn't mean to touch a nerve. Because what you write is


not only unfair, but just simply wrong. I "clearly" have no opinion on
this, either way. There is a right way to do things, and a wrong way.
It's absolute, oftentimes. I suggested a solution because you and
others have repeatedly suggested a problem. And there have been
threads, recently, on just this. Now you suggest that using a global
ref to the currentdb collection is the absolutely the right way. And
you're very insistent about it. And that's fine with me. But I asked
in good faith because I wanted to know. You seemed like one who knew,
and who perhaps had changed his mind. So that was something that
wasn't explained. As I said, there have been a lot of threads on this
problem - or, I guess, supposed problem - of a phantom reference, and
the speed differential. They are all recorded on groups.google.

>My recommendation is to use CurrentDb. You could call it once and
>cache the return and then you will have no problems. And no extraneous
>code.

In calling it once, you also have to free the collection, correct? So

Mark Johnson

unread,
Mar 11, 2003, 9:53:22 PM3/11/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>sith...@besty.org.uk (Trevor Best) wrote in
><s17r6v07oe4di6b6a...@4ax.com>:
>
>>On Mon, 10 Mar 2003 15:31:45 -0800, in message
>><iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
>><1023...@compuserve.com> wrote:

>>>Just set as a private constant. You know what the name of your
>>>own database is supposed to be.

>>Until someone renames the database.

>Or moves it.

No, I can understand preferring a dynamic reference. That would
require reading the db.name on startup - correct?


>Heavens, I always know *exactly* where my applications are going to
>be run from. And if the clients don't like the choices I've made,
>they can go hire someone who can write code that adapts to *their*
>needs, instead of those of the programmer!!! Harrumph!

But if you read the dot-name on startup, won't that tell you where the
db is, and what it's called? I mean, after all, the comparisons made
and the problem mentioned for dbengine were based on a specific name
and path. That's what was given in the examples to demonstrate the
very problem.


>I don't think Mark has too much experience with Access

Yes. And I've said that, explictly, many times. I'm just trying to
learn. If you remember, that's why I posted that full working example
of a treeview, first using Dir, and later with the more robust and
reliable fso, because I hadn't seen anything that simple and
comprehensive anywhere on the web or the ngs for how to create and
operate such a control. Now it's on groups.google. And it didn't take
that long for me to learn how to use it. So learning is possible. But
when people disagree on just how something actually works, then it's a
whole 'nother step to working on any solution - if one can't agree on
the problem.


>Many people with lots more experience than Mark and with more
>experience than me have disagreed on this subject.

Well, I don't see why. I mean - what's to disagree about? unless
people are coming at this with different assumptions about how
dbengine works, or how problems with it arise? The basis for any
arguments would come from disagreement over how the procedures
operate.

Mark Johnson

unread,
Mar 11, 2003, 9:58:42 PM3/11/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>He said to the pointer created with CurrentDB().

>> . . . But why do that,
>>since it's only a pointer? Wouldn't the only one which you'd save,
>>globally, be the currentdb collection? And if you save it,
>>globally, what would happen if you used temporary tables, or added
>>queries, or tables, etc? Would they be part of that collection?

>What are you talking about?

That currentdb creates this collection, which takes time, and dbengine
simply points to what is there, at the moment. That's how it was
explained. You're saying that's simply not true.


>The only difference is that *new* call to CurrentDB() after the
>database has been opened and additions to/deletions from
>collections made will have the most up-to-date collections.

I understand. The effort was to remove that overhead, by simply
calling dbengine every time. Whatever it points to would be current.
The objection was given that a call to some cover function to return
dbengine was "extraneous code". And yet to dynamically add objects,
you'd require code to do so, not to mention the global var, the open
and set covers, that wouldn't be necessary for just referring to the
pointer. That's more lines, right there, than the simple one-time
cover I suggested. But if dbengine simply doesn't work the way it was
explained, then that changes things.


>collections of a reference created with DBEngine(0)(0) will include
>the items in the collections at the time the database is opened
>forever, *regardless of when you create your reference*, unless you
>manually refresh the collections.

I was told that dbengine simply returns a pointer to the current db as
it is, not some snapshot as it was opened. If that's not the case,
then there's no difference in terms of having to update either.


>So, if you open a database, add some querydefs or whatever, and
>then in DAO set a database variable with a reference derived from
>DBEngine(0)(0), the collections will *not* know anything about your
>newly created querydefs, because they were not there when the
>database was opened.

I didn't know that. If that's simply the case, then I don't see an
advantage of dbengine over simply the "extraneous code" needed for a
global db set with currentdb. But that's not how I understood it,
previously. I don't believe anyone explained it, that way.


>CurrentDB(), on the other hand, refreshes the
>collections, and will know about any objects at the time the
>reference was returned from CurrentDB().

Because it creates an entirely new collection, every time, which is
probably why people say it's important to close any database objects
set using this method.


>I've never had to really worry about this, in any event, as I
>almost never add any new objects to a fornt end, in order that I
>can avoid bloat (so the front end never has to be compacted).

There was that wizard/compact problem mentioned before I really had
much interest in this, that you see on groups.google. So that was a
case where dbengine literally would fail, but not currentdb.


>Trevor's t-functions (replacements for the built-in domain
>aggregate functions) all were written with DBEngine(0)(0), most
>likely because they were created in Access 2, when that was the
>recommended methodology (did CurrentDB() even exist then? I can't
>remember!).

>After I pointed out how to add an optional parameter for a global
>db variable, Trevor has agreed that CurrentDB() could be just as
>fast as DBEngine(0)(0), and that it would still leave the code
>completely independent of the app in which it is called.

I didn't know that. I thought he was saying that he would continue to
use dbengine, and that it never posed a problem for him.

But if dbengine _doesn't_ return a current pointer, but rather will
not point to a database with dynamically created objects, that it is
also creating some sort of collection - but somehow doing so much
faster than currentdb - then locally, even, I can see what advantage
there'd be. And the reason for preferring it was simply that there was
no memory to free up, used globally or otherwise, and no collection to
deal with if set locally. But, again, that's now how I understood
dbengine.


Mark Johnson

unread,
Mar 11, 2003, 9:58:46 PM3/11/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>>In calling it once, you also have to free the collection, correct?
>>So when you exit, you need to call a cover function to set db =
>>nothing? There's your "code", as well as the cover to set it, and
>>create the global variable. If your objection is that, then I
>>don't understand. It was suggested that failing to set to nothing
>>could even cause problems. And if it's a collection that is not
>>automatically updated, then wouldn't "extraneous code" be required
>>to keep the collection current under those circumstances when
>>object are added?

>In regard to cleanup when closing the application, there is
>absolutely no difference whatsoever between CurrentDB() and
>DBEngine(0)(0) -- both are object variables that need to be cleaned
>up if you want to ensure that your app will successfully close when
>terminated properly.

But if you use either inline, why would that be? In fact, it was
explained to someone in one of those google threads, that a reference
they assumed had been made was only for the one line in which they
used currentdb inline, and directly. So, it would imply that the
collection was automatically freed if not explictly Set to an object.

So I asked about Setting the dbengine for a return. I didn't think
that would make any difference, because it was immediately returned.
But maybe there was something about using, Set. But I was told there
was not. It didn't need to be freed. The function itself didn't
require an "= nothing", before reuse.


>A2. CurrentDB() is several times slower than DBEngine(0)(0), but as
>there is no circumstance in which you'd need to create a reference
>to the currently open MDB in any kind of loop, the performance
>advantage of DBEngine(0)(0) makes no difference.

Well, if you use repeated lookups, for example, then Trevor's example,
and most others I would imagine, would have to open and close a
currentdb each time, short of using it as a global variable, with
covers to set and free on close, and update for object
additions/deletions, etc. It would be a little suite of global-db
routines. But even if used inline, there's still the creation of that
collection, which apparently is time-consuming. But you just said, in
another message, that dbengine doesn't return a pointer to the
database, but to some sort of snapshot or collection when the db is
first opened. So that changes everything. If you dynamically create an
object, I assumed that a dbengine pointer would include that at any
point after its creation. But you're saying even dbengine has to be
reset after than, just like currentdb. That changes a lot.

J Ohlson

unread,
Mar 12, 2003, 5:02:20 AM3/12/03
to
Mark Johnson <1023...@compuserve.com> wrote in
news:vi8t6vcqp21h756hd...@4ax.com:


>>CurrentDB(), on the other hand, refreshes the
>>collections, and will know about any objects at the time the
>>reference was returned from CurrentDB().
>
> Because it creates an entirely new collection, every time, which is
> probably why people say it's important to close any database objects
> set using this method.

You mean it's important to set the database objects to Nothing? You don't
.Open a database object here, so there's nothing to .Close. Compare with
recordsets.

Regards
John

Mark Johnson

unread,
Mar 12, 2003, 5:42:03 AM3/12/03
to
"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
wrote:

>My talk about the global var was an answer to your perf concerns.

But messy by comparison to the simple one-time cover that I suggested,
is all.

>has nothing to do with what I would recommend. Because proper
>performance work involves behcmarking, and I honestly doubt there is
>ever a case that would see CurrentDb behind a perf hit.

I've seen it myself, in repeated use of custom lookups. It may not
seem noticeable. But at some level it gives the sense of the FE being
sluggish, or just somehow a 'step behind'. It could become annoying.
But, no, that was not with the use of a global variable, set at some
point on startup, and then managed for any updates, and closed on
exit. That _would_ simply solve that particular problem, and I agree.
No, it was just the use of currentdb set to an object, as you see
described in all the M$ documentation for Access.


>As to your insistence on the need for a "Set db = Nothing", this is
>also not needed.

See, here you go, again. I don't know what "insistence" you're
referring to. I really don't. I might have asked if you need to do
this using the dbengine cover that I suggested. But, since the Set is
simply setting the return value for the function, it would be lost 'on
the next line', I would imagine. If that's what you're referring to,
then thanks for the confirmation. But people are sort of talking about
everything and anything at once in these threads, so that "this is"
might not be what I think you think I mean you meant to say. But if
that's it, thanks.


>Neither is db.Close -- in fact, the latter caused
>bugs in prior versions of Access.

I wondered why I never saw dot-close used in M$ documentation for a
db, in the help files. It was just for the recordset object. It was
buggy? that's why?


>The only never you hit was in the mischaracterization of prior posts.

Well - okay. I'm not sure I understand what that means. But you did
seem angry. I haven't mischaracterized ANY "prior posts". And you
threw in the word, insistence, above - too, which itself is a
mischaracterization. So maybe someone here has been mischaracterizing.
But not me. And because, at some point, that's got nothing to do with
specific technical experience, and more just with simple comprehension
and number of other, personal things. Programmers are people, too. And
their foibles are the same displayed by all, on UseNet.


>SUMMARY: There is nothing to worry about here, and there is no need
>for you to use the code you posted. Just use CurrentDb.

Others seem to maintain, although maybe not at this point, that
there's really nothing unreliable with simply a cover to get the
dbengine pointer; that dbengine, in practical, actual use, just is
pretty reliable.

It was recently explained to me that dbengine doesn't simply return a
pointer to whatever the current db, for that index, happens to be, but
rather somehow takes a 'shapshot', or freezes a copy, of the db as it
was when opened. That's what you get when you use dbengine, which I
don't see clearly explained in the documentation, assuming that's even
the case (which perhaps it's not really).

I guess that raises the odd question, perhaps. If currentdb is fine,
and was designed to be fine, and to be reliable, what really, though,
is the objection to dbengine? If currentdb is useful, even if slow,
what on the other hand is actually wrong with the alternative? The
error that you suggested, apparently long ago, is also apparently not
one you feel would ever be a real problem, correct? If it were, for
the reason you gave, the simple name check I suggested would solve
that particular problem, in such rare cases (you just get the dot-name
from an autoexec call, so that even if you shift-start, you still run
the initialization cover).

Anyway. Didn't mean to upset you. Clearly I have. But a problem was
posed on this ng - it was, at one point. And I proposed a simple way
to solve it, as a problem, at one point, and that didn't seem as
messy, or potentially as unreliable, as trying to manage a global
currentdb collection. I have a feeling that, and other methods, would
all work. What I suggested just seemed cleaner, needed less code, and
might be more reliable - for the rare case, which rarely occurs.

Mark Johnson

unread,
Mar 12, 2003, 5:50:31 AM3/12/03
to
J Ohlson <nos...@nospam.com> wrote:

I know. I'm not even setting an object, just returning a reference
that is lost in 'the next line'. With the global reference though, it
would be set to currentdb, from the first form, or maybe an autoexec
macro. You could use it inline. But that collection might have to be
freed on exit; the = Nothing. Some seem to suggest that it won't
really hurt anything if you don't. But it might if you don't. And it
just seems messy; though it would be less so if you knew it didn't
matter. I like the idea, much better, of just using the cleaner
dbengine pointer, and then that cover I suggested for the rare case
that db(0) isn't what you think it is. I was told that dbengine does
not point to the current db, specified by workspace and db index, but
rather to some snapshot or 'collection' created when the db was first
loaded. But the more I consider that, the less likely it seems.

I see what you mean, as well, on the open and close. I had mentioned
that in another message, not this. And yes, you open a recordset. But
it's not what you do with the db, though 'micha' did mention something
about using close on a db, and that it was a source of problems.

Michael (michka) Kaplan

unread,
Mar 12, 2003, 9:18:11 AM3/12/03
to
"Mark Johnson" <1023...@compuserve.com> wrote...

> >My talk about the global var was an answer to your perf concerns.
>
> But messy by comparison to the simple one-time cover that I
suggested,
> is all.

Um, no. Single line ref versus that nightmare function, and the single
line wins every time.

> >has nothing to do with what I would recommend. Because proper
> >performance work involves behcmarking, and I honestly doubt there
is
> >ever a case that would see CurrentDb behind a perf hit.
>
> I've seen it myself, in repeated use of custom lookups. It may not
> seem noticeable. But at some level it gives the sense of the FE
being
> sluggish, or just somehow a 'step behind'. It could become annoying.
> But, no, that was not with the use of a global variable, set at some
> point on startup, and then managed for any updates, and closed on
> exit. That _would_ simply solve that particular problem, and I
agree.
> No, it was just the use of currentdb set to an object, as you see
> described in all the M$ documentation for Access.

I will shout for a moment, to point out the obvious.

THE ONE LINE GLOBAL VARIABLE SOLUTION SOLVES THIS PROBLEM WITH ONE
LINE OF CODE.

Any other solution is only good for people who (a) are paid by the
hour, (b) make more money by creating more code that they will have to
then maintain, and (c) have no one verifying that they are spending
their time productively.

If you have true perf concern, so use the global variable! Note that
this is exactly what wizards do. If its good enough for a project with
over 90,000 lines of complex VBA code, its good enough for your apps,
isn't it?

> >As to your insistence on the need for a "Set db = Nothing", this is
> >also not needed.
>
> See, here you go, again. I don't know what "insistence" you're
> referring to. I really don't. I might have asked if you need to do
> this using the dbengine cover that I suggested. But, since the Set
is
> simply setting the return value for the function, it would be lost
'on
> the next line', I would imagine. If that's what you're referring to,
> then thanks for the confirmation. But people are sort of talking
about
> everything and anything at once in these threads, so that "this is"
> might not be what I think you think I mean you meant to say. But if
> that's it, thanks.

Actually, I was referring to the generic issue of setting the ref to
nothing.

> >Neither is db.Close -- in fact, the latter caused
> >bugs in prior versions of Access.
>
> I wondered why I never saw dot-close used in M$ documentation for a
> db, in the help files. It was just for the recordset object. It was
> buggy? that's why?

No. It is because you do not Close what you did not Open.

> >The only never you hit was in the mischaracterization of prior
posts.
>
> Well - okay. I'm not sure I understand what that means. But you did
> seem angry. I haven't mischaracterized ANY "prior posts". And you
> threw in the word, insistence, above - too, which itself is a
> mischaracterization. So maybe someone here has been
mischaracterizing.
> But not me. And because, at some point, that's got nothing to do
with
> specific technical experience, and more just with simple
comprehension
> and number of other, personal things. Programmers are people, too.
And
> their foibles are the same displayed by all, on UseNet.

The mere NUMBER of your posts here implies insistence. The facvt that
you refuse to accept the notion that your code is overkill (rather
than a simple one line solution?) implies insistence. Refusal to
notice any of this while you continue to post over and over implies
insistence. Think about it....

> >SUMMARY: There is nothing to worry about here, and there is no need
> >for you to use the code you posted. Just use CurrentDb.
>
> Others seem to maintain, although maybe not at this point, that
> there's really nothing unreliable with simply a cover to get the
> dbengine pointer; that dbengine, in practical, actual use, just is
> pretty reliable.

The problems still exist. But do you let people run wizards in your
apps, from the UI? Do you let people compact the current db after
letting them run those wizards? The answer for most apps is NO.

> It was recently explained to me that dbengine doesn't simply return
a
> pointer to whatever the current db, for that index, happens to be,
but
> rather somehow takes a 'shapshot', or freezes a copy, of the db as
it
> was when opened. That's what you get when you use dbengine, which I
> don't see clearly explained in the documentation, assuming that's
even
> the case (which perhaps it's not really).

Not exactly, but since it does not change anything here, it is really
not worth worrying about.

> I guess that raises the odd question, perhaps. If currentdb is fine,
> and was designed to be fine, and to be reliable, what really,
though,
> is the objection to dbengine? If currentdb is useful, even if slow,
> what on the other hand is actually wrong with the alternative? The
> error that you suggested, apparently long ago, is also apparently
not
> one you feel would ever be a real problem, correct? If it were, for
> the reason you gave, the simple name check I suggested would solve
> that particular problem, in such rare cases (you just get the
dot-name
> from an autoexec call, so that even if you shift-start, you still
run
> the initialization cover).

People have reported problems in the past. The workaround for the
problems is simple, if you do it right. If you do complex solutions
such as yours, then it is not simple. Again, compare one line to
multiple lines.

> Anyway. Didn't mean to upset you. Clearly I have. But a problem was
> posed on this ng - it was, at one point. And I proposed a simple way
> to solve it, as a problem, at one point, and that didn't seem as
> messy, or potentially as unreliable, as trying to manage a global
> currentdb collection. I have a feeling that, and other methods,
would
> all work. What I suggested just seemed cleaner, needed less code,
and
> might be more reliable - for the rare case, which rarely occurs.

What collection? I am referring to single global variable, set on open
of the db. Its is inherently at least as reliable as the solution you
give, and less prone to bugs. At this point, the only thing upsetting
me is you stubborn refusal to see this. :-(

David W. Fenton

unread,
Mar 12, 2003, 9:23:21 AM3/12/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<5j8t6vk2ehn8gcs9h...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>1023...@compuserve.com (Mark Johnson) wrote in
>><bbqs6vs78s8n6h4v9...@4ax.com>:
>
>>>In calling it once, you also have to free the collection,
>>>correct? So when you exit, you need to call a cover function to
>>>set db = nothing? There's your "code", as well as the cover to
>>>set it, and create the global variable. If your objection is
>>>that, then I don't understand. It was suggested that failing to
>>>set to nothing could even cause problems. And if it's a
>>>collection that is not automatically updated, then wouldn't
>>>"extraneous code" be required to keep the collection current
>>>under those circumstances when object are added?
>
>>In regard to cleanup when closing the application, there is
>>absolutely no difference whatsoever between CurrentDB() and
>>DBEngine(0)(0) -- both are object variables that need to be
>>cleaned up if you want to ensure that your app will successfully
>>close when terminated properly.
>
>But if you use either inline, why would that be? In fact, it was
>explained to someone in one of those google threads, that a
>reference they assumed had been made was only for the one line in
>which they used currentdb inline, and directly. So, it would imply
>that the collection was automatically freed if not explictly Set
>to an object.

I was speaking of a global database variable pointing to the
current database -- it doesn't matter how you create it. And I
would recommend using such a persistent variable, whichever method
you're using to create the reference.

>So I asked about Setting the dbengine for a return. I didn't think
>that would make any difference, because it was immediately
>returned. But maybe there was something about using, Set. But I
>was told there was not. It didn't need to be freed. The function
>itself didn't require an "= nothing", before reuse.

Best practices are to always use a variable as not doing so can
create implicit references that can't be released (you didn't
create them). Remember, VBA uses reference counting for garbage
collection, and if you don't have control over releasing the
resources, you can never be sure if VBA will correctly clean up
after itself.

So, never do anything that could create implicit references. This
means:

1. always use variables for manipulating VBA and Access objects.

2. always close those objects and set the variables to Nothing when
you are finished with them.

3. this includes object variables used in For/Each loops such as:

Dim ctl As Control

For Each ctl in Me.Controls
[whatever]
Next ctl

Set ctl = Nothing

The use of the Control-type variable could conceivably leave an
implicit reference to the last control in the collection hanging
unless you explicitly destroy it.

These are best practices, and have been well-known to Access
programmers for a long time.

Therefore, most of us don't write code that operates directly on
the objects as you've described. Some folks do it in very special
circumstances where they've concluded that, logically, it cannot
create an unreleasable implicit reference. I simply do it as a
matter of course.

However, this applies only to items that return objects or
collections. I don't worry about CurrentDB().Name, for instance,
only about things like CurrentDB().OpenRecordset() or
CurrentDB().QueryDefs(1). Referring to properties seems to be safe.

>>A2. CurrentDB() is several times slower than DBEngine(0)(0), but
>>as there is no circumstance in which you'd need to create a
>>reference to the currently open MDB in any kind of loop, the
>>performance advantage of DBEngine(0)(0) makes no difference.
>
>Well, if you use repeated lookups, for example, then Trevor's
>example, and most others I would imagine, would have to open and
>close a currentdb each time, short of using it as a global
>variable, with covers to set and free on close, and update for

>object additions/deletions, etc. . . .

I've repeatedly demolished this argument -- there are no such
circumstances that can't be handled very simply without any complex
wrappers for handling the public variables.

> . . . It would be a little suite of
>global-db routines. . . .

Not necessary.

> . . . But even if used inline, there's still the


>creation of that collection, which apparently is time-consuming.

In my apps, 10K such references take 9 seconds. If you do it once,
while it's probably not exactly 1/10,000th that much time (the
first one probably has more overhead than the later ones), it is
obviously a very small amount of time. As part of the
initialization of an Access application, setting your global
variable is not going to be noticeably slower for an end user if
you use CurrentDB() instead of DBEngine(0)(0).

>But you just said, in another message, that dbengine doesn't
>return a pointer to the database, but to some sort of snapshot or

>collection when the db is first opened. . . .

No, I didn't say anything of the sort. Read the help files on
CurrentDB(), please, and then get back to us.

> . . . So that changes
>everything. . . .

Nothing changes, because you're wrong in asserting that I said
anything of the sort.

> . . . If you dynamically create an object, I assumed that a


>dbengine pointer would include that at any point after its
>creation. But you're saying even dbengine has to be reset after
>than, just like currentdb. That changes a lot.

In terms of collections, the collections for the pointers do not
automatically refresh once the pointers are created. If you change
the collections, db variables created from both methods will have
to be refreshed. And even a direct reference to DBEngine(0)(0) or a
variable newly initialized with DBEngine(0)(0) after collections
have been updated will need to have the relevant collections
refreshed. A direct reference to CurrentDB() or a variable
initialized with it after the collections have been altered will be
100% accurate.

Look, you clearly are not equipped here with enough information
about how this works to be advocating one position or another. You
are posting erroneous information and claiming that others like
myself have said those erroneous things.

Please read up on this and learn for yourself and then return to
the discussion properly equipped.

David W. Fenton

unread,
Mar 12, 2003, 9:30:53 AM3/12/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<0n7t6v87lh21nm5u3...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>sith...@besty.org.uk (Trevor Best) wrote in
>><s17r6v07oe4di6b6a...@4ax.com>:
>>
>>>On Mon, 10 Mar 2003 15:31:45 -0800, in message
>>><iq7q6vk7mvu5afp5l...@4ax.com>, Mark Johnson
>>><1023...@compuserve.com> wrote:
>
>>>>Just set as a private constant. You know what the name of your
>>>>own database is supposed to be.
>
>>>Until someone renames the database.
>
>>Or moves it.
>
>No, I can understand preferring a dynamic reference. That would
>require reading the db.name on startup - correct?

Yes. But this doesn't get you anything in the way of performance.

As my tests showed, DBEngine.OpenDatabase and CurrentDB() are
roughly the same in terms of speed in 10K iterations. Whether you
use DBEngine(0)(0).Name or CurrentDB().Name to initialize your name
variable, after that point, Opendatabase (using that cached name)
will be just as slow as CurrentDB(), so you haven't gained anything
at all.

Your whole argument about storing the db name in a variable is a
red herring, and always was.

>>Heavens, I always know *exactly* where my applications are going
>>to be run from. And if the clients don't like the choices I've
>>made, they can go hire someone who can write code that adapts to
>>*their* needs, instead of those of the programmer!!! Harrumph!
>
>But if you read the dot-name on startup, won't that tell you where

>the db is, and what it's called? . . .

You were advocating a global constant. I was just pointing out that
this is dumb.

I think the whole concept of using a variable for the name of the
db is dumb, in any case, as it gets you nowhere with the DBEngine
vs. CurrentDB problem. Yes, it allows you to check if
DBEngine(0)(0) is returning the right reference, but it's just as
easy to simply use a method that *always* returns the right
reference, since there's no need for repeatedly checking the
currently opened db in a loop.

> . . . . I mean, after all, the


>comparisons made and the problem mentioned for dbengine were based
>on a specific name and path. That's what was given in the examples
>to demonstrate the very problem.

And it can be avoided by using CurrentDB and has no performance
penalty that makes a difference, since there are no circumstances
where loops with CurrentDB in them would be justified.

>>I don't think Mark has too much experience with Access
>
>Yes. And I've said that, explictly, many times. I'm just trying to

>learn. . . .

Read the help files, please. They explain all of this.

> . . . If you remember, that's why I posted that full working


>example of a treeview, first using Dir, and later with the more
>robust and reliable fso, because I hadn't seen anything that
>simple and comprehensive anywhere on the web or the ngs for how to
>create and operate such a control. Now it's on groups.google. And
>it didn't take that long for me to learn how to use it. So
>learning is possible. But when people disagree on just how
>something actually works, then it's a whole 'nother step to
>working on any solution - if one can't agree on the problem.

Well, your erroneous description of what CurrentDB does is also now
on Google, because you misinterpreted something or other that I
wrote because of your own lack of experience and knowledge.

And your original "slow" version is on Google, too.

Please educate yourself on the subject.

Ask questions, sure, but please stop making definitive statements
about what is or is not preferable.

>>Many people with lots more experience than Mark and with more
>>experience than me have disagreed on this subject.
>
>Well, I don't see why. I mean - what's to disagree about? unless
>people are coming at this with different assumptions about how
>dbengine works, or how problems with it arise? The basis for any
>arguments would come from disagreement over how the procedures
>operate.

\/\/hatever you say, Mark.

You obviously understand the whole thing far better than I could,
and I've only been working on understanding the pros and cons of
the issue on a daily basis for, oh, I don't know, the last 6 or 8
years.

I bow to your superior intelligence.

David W. Fenton

unread,
Mar 12, 2003, 9:52:15 AM3/12/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<vi8t6vcqp21h756hd...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>He said to the pointer created with CurrentDB().
>
>>> . . . But why do that,
>>>since it's only a pointer? Wouldn't the only one which you'd
>>>save, globally, be the currentdb collection? And if you save it,
>>>globally, what would happen if you used temporary tables, or
>>>added queries, or tables, etc? Would they be part of that
>>>collection?
>
>>What are you talking about?
>
>That currentdb creates this collection, which takes time, and

>dbengine simply points to what is there, at the moment. . . .

No. DBEngine points at what *was* there, when the database was
opened. Unless you manually refresh DBEngine's collections, they
will not reflect changes to the collections.

> . . . That's how
>it was explained. . . .

So far as your limited understand goes?

> . . . You're saying that's simply not true.

Read the frigging help files, please. It explains this.

When you call CurrentDB() the collections are 100% current for the
instant when you call it. For DBEngine(0)(0), the collections are
current only for when the database was opened, and never get
updated unless you manually refresh them.

That means that if you have added or deleted items from collections
and create a local database variable, and you need to refer to the
collections of the database represented by that variable, you have
to refresh the collections for a variable created from DBEngine,
but *not* from CurrentDB, because the latter does it for you. In
code:

Dim dbCurrent As Database
Dim dbDBEngine As Database

[add something to a collection, such as a new QueryDef]

Set dbCurrent = CurrentDB()
Set dbDBEngine = DBEngine(0)(0)

' these two will report different numbers
Debug.Print dbCurrent.QueryDefs.Count
Debug.Print dbDBEngine.QueryDefs.Count

dbDBEngine.QueryDefs.Refresh

' these two will report the same numbers
Debug.Print dbCurrent.QueryDefs.Count
Debug.Print dbDBEngine.QueryDefs.Count

The one advantage of DBEngine is that you can refresh the
collections selectively. However, the more common situation is
something like this:

Dim db As Database

Set db = [create your reference however you like]

[add a new QueryDef]

db.QueryDefs.Refresh

[do something with the QueryDefs collection]

In that case, it doesn't matter what you use to initialize your
database variable -- if you change the collections after
initializing your variable, you have to refresh the relevant
collections either way.

>>The only difference is that *new* call to CurrentDB() after the
>>database has been opened and additions to/deletions from
>>collections made will have the most up-to-date collections.
>
>I understand. The effort was to remove that overhead, by simply

>calling dbengine every time. . . .

Congratulations. You've discovered America.

Of course, Columbus discovered it over 600 years ago.

> . . . Whatever it points to would be
>current. . . .

No, that's not true, unless you're utilizing CurrentDB() directly,
which is highly dangerous because it can create implicit
references.

> . . . The objection was given that a call to some cover
>function to return dbengine was "extraneous code". . . .

Yes, because you've created the need to do that by choosing not to
use CurrentDB() because of a performance hit that doesn't make any
difference whatsoever. Add to that the occasional inaccuracy of
DBEngine(0)(0) (well, it's never inaccurate, just returns
unexpected results), and it seems there's no real need to use
DBEngine(0)(0).

> . . . And yet to


>dynamically add objects, you'd require code to do so, not to
>mention the global var, the open and set covers, that wouldn't be

>necessary for just referring to the pointer. . . .

Regardless of what you use to create the database variable, you'd
obviously refresh the relevant collection(s) of it after adding to
it or deleting from it.

> . . . That's more lines,


>right there, than the simple one-time cover I suggested. But if
>dbengine simply doesn't work the way it was explained, then that
>changes things.

I don't know what you're going on about. I never saw the point of
your elaborate workaround code in the first place.

>>collections of a reference created with DBEngine(0)(0) will
>>include the items in the collections at the time the database is
>>opened forever, *regardless of when you create your reference*,
>>unless you manually refresh the collections.
>
>I was told that dbengine simply returns a pointer to the current
>db as it is, not some snapshot as it was opened. If that's not the
>case, then there's no difference in terms of having to update
>either.

Why don't you read the help files instead of going on "what you
were told," especially since your ability to accurately process
"what you were told" has been demonstrated to be imperfect.

>>So, if you open a database, add some querydefs or whatever, and
>>then in DAO set a database variable with a reference derived from
>>DBEngine(0)(0), the collections will *not* know anything about
>>your newly created querydefs, because they were not there when
>>the database was opened.
>

>I didn't know that. . . .

The rest of us did.

Free clue: the people who've got opinions on this are not religious
zealots.

> . . . If that's simply the case, then I don't see an


>advantage of dbengine over simply the "extraneous code" needed for
>a global db set with currentdb. But that's not how I understood
>it, previously. I don't believe anyone explained it, that way.

No one is responsible for remedying the holes in your knowledge.

>>CurrentDB(), on the other hand, refreshes the
>>collections, and will know about any objects at the time the
>>reference was returned from CurrentDB().
>
>Because it creates an entirely new collection, every time, which
>is probably why people say it's important to close any database
>objects set using this method.

Eh? What are you talking about?

The object created by DBEngine(0)(0) and by CurrentDB() is
identical, except that CurrentDB refreshes the collections. Both
are memory structures reflecting the structure of the db being
referred to. There's nothing special about the references created
in that fashion -- they are equivalent. However, the degree to
which the structures created by those references accurately reflect
the current state of the db they refer to is not equivalent.

>>I've never had to really worry about this, in any event, as I
>>almost never add any new objects to a fornt end, in order that I
>>can avoid bloat (so the front end never has to be compacted).
>
>There was that wizard/compact problem mentioned before I really
>had much interest in this, that you see on groups.google. So that
>was a case where dbengine literally would fail, but not currentdb.

Yes. That's true. That was the nail in the DBEngine coffin, for me.

>>Trevor's t-functions (replacements for the built-in domain
>>aggregate functions) all were written with DBEngine(0)(0), most
>>likely because they were created in Access 2, when that was the
>>recommended methodology (did CurrentDB() even exist then? I can't
>>remember!).
>
>>After I pointed out how to add an optional parameter for a global
>>db variable, Trevor has agreed that CurrentDB() could be just as
>>fast as DBEngine(0)(0), and that it would still leave the code
>>completely independent of the app in which it is called.
>
>I didn't know that. I thought he was saying that he would continue
>to use dbengine, and that it never posed a problem for him.

He agreed that my solution had advantages. I don't know if he said
he was gonig to alter his functions.

>But if dbengine _doesn't_ return a current pointer, but rather
>will not point to a database with dynamically created objects,
>that it is also creating some sort of collection - but somehow
>doing so much faster than currentdb - then locally, even, I can
>see what advantage there'd be. And the reason for preferring it
>was simply that there was no memory to free up, used globally or
>otherwise, and no collection to deal with if set locally. But,
>again, that's now how I understood dbengine.

Look, you're missing a key point, here:

No one who has any experience with VBA's problems with garbage
collection (i.e., not releasing resources when they go out of
scope) uses either DBEngine(0)(0) or CurrentDB() directly in
anything but trivial code and in circumstances where they return
properties and not objects.

So, the real discussion is about what happens when you do this:

Dim db As Database

Set db = [use your method of choice]

Once you've *done* that, the two are equivalent, because they both
return the same kind of object. The only difference is in how
accurate the collections may be *at the time the Set db statement
is executed.

That's all.

If you're not altering your collections, the two are equivalent,
because you don't really care if the collections are up-to-date.

The differences are in:

1. speed. CurrentDB() is slower, because of the collection refresh.

2. accuracy. DBEngine(0)(0) will occasionally be wrong.

You make your choice.

David W. Fenton

unread,
Mar 12, 2003, 9:55:48 AM3/12/03
to
nos...@nospam.com (J Ohlson) wrote in
<Xns933C6FFC61...@195.67.237.51>:

Not true.

I've discovered that you closing a db object reference before
setting to Nothing releases resources.

Try this:

Dim db As Database
Dim rs As Recordset

Set db = CurrentDB()
Set rs = db.OpenRecordset([your SQL here])

Set db = Nothing

Debug.Print rs.RecordCount

rs.Close
Set rs = Nothing

Set db = CurrentDB()
Set rs = db.OpenRecordset([your SQL here])

db.Close
Set db = Nothing

Debug.Print rs.RecordCount

The 1st rs.RecordCount will work, while the second will tell you
that the "object is invalid or no longer set." This means that not
closing your db leaves memory resources allocated.

I have been told that db.Close on a reference created with
DBEngine(0)(0) can have unpredictable results, including crashing
the application. The same thing does not happen with references
created with CurrentDB().

David W. Fenton

unread,
Mar 12, 2003, 9:59:08 AM3/12/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<uq3u6vgi78dcb7ngp...@4ax.com>:

>J Ohlson <nos...@nospam.com> wrote:
>
>>Mark Johnson <1023...@compuserve.com> wrote in
>>news:vi8t6vcqp21h756hd...@4ax.com:
>
>>>>CurrentDB(), on the other hand, refreshes the
>>>>collections, and will know about any objects at the time the
>>>>reference was returned from CurrentDB().
>
>>> Because it creates an entirely new collection, every time,
>>> which is probably why people say it's important to close any
>>> database objects set using this method.
>
>>You mean it's important to set the database objects to Nothing?
>>You don't .Open a database object here, so there's nothing to
>>.Close. Compare with recordsets.
>
>I know. I'm not even setting an object, just returning a reference

>that is lost in 'the next line'. . . .

This is a bad idea.

> . . . With the global reference though,


>it would be set to currentdb, from the first form, or maybe an
>autoexec macro. You could use it inline. But that collection might

>have to be freed on exit; . . .

Naturally, as does any reference, regardless of its scope.

> . . the = Nothing. Some seem to suggest that
>it won't really hurt anything if you don't. . . .

Who is suggesting that?

> . . . But it might if you
>don't. . . .

Unclosed references are a common cause of the "database won't
close" error. This is very, very well-known, and is the reason why
everyone worth her salt recommends explicit cleanup.

> . . . And it just seems messy; though it would be less so if you


>knew it didn't matter. I like the idea, much better, of just using
>the cleaner dbengine pointer, and then that cover I suggested for

>the rare case that db(0) isn't what you think it is. . . .

You're suggesting, I take it, always using DBEngine(0)(0) directly?
Well, good luck. You'll have very buggy applications.

> . . . I was told


>that dbengine does not point to the current db, specified by
>workspace and db index, but rather to some snapshot or
>'collection' created when the db was first loaded. But the more I
>consider that, the less likely it seems.

Please educate yourself.

You've been told nothing of the sort.

>I see what you mean, as well, on the open and close. I had
>mentioned that in another message, not this. And yes, you open a
>recordset. But it's not what you do with the db, though 'micha'
>did mention something about using close on a db, and that it was a
>source of problems.

You are obviously in over your head here. Please stop posting until
you've learned what you need to know, because you're polluting the
Google archive with misinformation.