The DBengine(0)(0) problem

662 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.

David W. Fenton

unread,
Mar 12, 2003, 10:10:17 AM3/12/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<hb3u6v03d368e9a6h...@4ax.com>:

>"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. . . .

I posted the solution for that for code (pass an optional db
parameter, already initialized), and there's never any need for it
in SQL, as you can do the lookup with the SQL.

If you saw a performance hit, then your code was bad or you were
using the wrong methods to get your information.

> . . . 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. . . .

Any poorly designed application can be 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.

Perhaps that's because the examples are intended to stand on their
own, independent of context?

Do you give *any* thought whatsoever to these issues before
posting?

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

I disagree with MichKa on this one. I think that the global db
should be de-initialized on application exit, both closing it and
setting it to Nothing.

>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. . . .

What are you talking about?

> . . . 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.

It's not "people" who are confused here -- it's *you*.

>>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?

I've been using db.Close on db variables initialized with CurrentDB
in A97 and A2K forever. It is not buggy. However, if the reference
is created with DBEngine(0)(0), you could have problems.

>>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. . . .

Nothing of the sort was explained to you.

Both DBEngine(0)(0) and CurrentDB return an object of type Database
that is a memory structure reflecting the structure of the database
pointed to. Though the objects are of the same type, they have
different relationships to the physical state of the object they
represent, with DBEngine not necessarily having current information
on collections, and CurrentDB() always being 100% current *at the
time the reference is returned*.

> . . . 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? . . .

It is inaccurate, and it requires extra refreshing.

Of course, given that you don't need to call either one of them
more than once in any given application, it seems that it's better
to be safe than sorry.

> . . . If currentdb is


>useful, even if slow, what on the other hand is actually wrong

>with the alternative? . . .

1. it's wrong in certain circumstances that can't be controlled by
the programmer.

2. it can cause errors that CurrentDB references do not
(DBENgine(0)(0).Close, for instance).

> . . . 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).

I don't know about Michael, but I don't want to take the risk that
a user of one of my apps may use a wizard, compact and then have
the problem with DBEngine(0)(0) pointing to the wizard instead.

Why risk something that you can avoid with no downside?

>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.

Using CurrentDB() solves the two problems of DBEngine.

Re-using a single persistent db variable solves the performance
problem of CurrentDB().

Most of the issues you've raised are created by your desire to
avoid CurrentDB(), which is simply irrational.

rkc

unread,
Mar 12, 2003, 4:22:40 PM3/12/03
to

"David W. Fenton" <dXXXf...@bway.net> wrote in message
news:933C6E780df...@24.168.128.74...

> 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.

The horse is dead. Time to stop beating it.

David W. Fenton

unread,
Mar 12, 2003, 9:45:11 PM3/12/03
to
r...@YabbaDabbaDoo.rochester.rr.com (rkc) wrote in
<AyNba.18159$yc5....@twister.nyroc.rr.com>:

That's when the fun begins.

Mark Johnson

unread,
Mar 13, 2003, 1:16:40 AM3/13/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>1023...@compuserve.com (Mark Johnson) wrote in
><hb3u6v03d368e9a6h...@4ax.com>:
>>"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
>>wrote:

>>>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. . . .

>I posted the solution for that for code

It was about a speed difference. That was the comment.


>parameter, already initialized), and there's never any need for it
>in SQL, as you can do the lookup with the SQL.

Right. If you have SQL, why would you use lookup? The rework of
dlookup that I use, is just like tlookup from Trevor, in that it uses
SQL. It's not that SQL is necessarily faster than FindFirst. But it's
better integrated, and wouldn't pose any problem between DAO and ADO.

>If you saw a performance hit, then your code was bad or you were
>using the wrong methods to get your information.

Or probably just like the examples from the very Access help files.


>>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. . . .

>Any poorly designed application can be annoying.

And a sluggish application, however well you think it's designed, can
grate, as well. People like to click on that screen very quickly. I've
seen it repeatedly, in various contexts, different offices. The speed
at which the operator runs that console, whether a clerk or a
researcher, can be something of a point of pride. It's not an unusual
thing, in other words.


>Do you give *any* thought whatsoever to these issues before
>posting?

You've had difficulty explaining yourself in this sort of thread -
maybe not in other threads, on other subjects. But you also were quite
opinionated on the whole area of the web, and the browser wars, and
such. And you didn't seem to much like disagreement in that, either.
And your insulting method is just not convincing. It isn't on any ng,
on any subject. It says more about yourself.


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

>I disagree with MichKa on this one. I think that the global db
>should be de-initialized on application exit, both closing it and
>setting it to Nothing.

You would write - db.close? and then set db = Nothing? on exit.


>>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. . . .

>What are you talking about?

That was replying to 'michka'.


>I've been using db.Close on db variables initialized with CurrentDB
>in A97 and A2K forever. It is not buggy. However, if the reference
>is created with DBEngine(0)(0), you could have problems.

Okay.


>Both DBEngine(0)(0) and CurrentDB return an object of type Database
>that is a memory structure reflecting the structure of the database
>pointed to. Though the objects are of the same type, they have
>different relationships to the physical state of the object they
>represent, with DBEngine not necessarily having current information
>on collections

If dbengine doesn't record an object, then what does?


>and CurrentDB() always being 100% current *at the
>time the reference is returned*.

And that creates the overhead. And you can measure the difference,
very easily, between currentdb and dbengine.


>>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? . . .

>It is inaccurate, and it requires extra refreshing.

But what's a specific example? How would you show that dbengine is in
a way, secretly, currentdb - or suffers from those problems? Or are
you just confusing the two, at this point? What, specifically, are you
talking about? Specifically.


>2. it can cause errors that CurrentDB references do not
>(DBENgine(0)(0).Close, for instance).

When would you use dbengine(0)(0).close? That would be an attempt to
programatically close the database that is running?

Would this be the one example you've been referring to, above?


>> . . . 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).

>I don't know about Michael, but I don't want to take the risk that
>a user of one of my apps may use a wizard, compact and then have
>the problem with DBEngine(0)(0) pointing to the wizard instead.

That's why you need something just like I suggested, which addresses
that specific problem.


>Re-using a single persistent db variable solves the performance
>problem of CurrentDB().

The overhead. But not that it is a static reference, and other things.


>Most of the issues you've raised are created by your desire to
>avoid CurrentDB(), which is simply irrational.

Or the "desire" to say otherwise, is "simply irrational". I would
think both, and other, solutions would be fine, if you are able to
manage everything. But using the dbengine, directly, just seems
cleaner, more reliable, and doesn't introduce potential problems.

Mark Johnson

unread,
Mar 13, 2003, 1:19:27 AM3/13/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>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:

>>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.

They're not. I ran this in writing a substitute for dlookup. And
repeatedly setting and freeing a db reference is MUCH slower with
currentdb than dbengine. In fact, there was much less difference
between FindFirst and an SQL string than these two.


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

Well, and straw burns nicely, I guess. But what I said was that the
idea of a global ref from currentdb seemed to solve that problem of
always setting and freeing, as typically described in the
documentation supplied with Access. As far as that went, you'll see I
agreed. It was a good idea. Should be a standard mention in all the
documentation. But you run the risk of abusing that variable, of not
closing it, or of having it get out of sync, in certain cases. Again,
all this might be exceptional. But it might be the exception you
forgot about just when Murphy decides to lay down his law. What I
proposed is more robust, and just a cleaner solution, overall.


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

Well, I agree that if one is prone to change the db name, as one often
does when writing it, developing it, it's better to get the name
dynamically, on start-up. So I did.


>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.

But you don't have to. There's no loop. You get the loop in what - .1
percent of 1 percent of all cases. In fact, 'michka' is saying, and
perhaps has just been saying all along, it's such a small possibility,
you might as well forget it. But, just in case, you loop. But you'll
never loop. But just in case, it's there. Problem, rare problem, was
mentioned. Here's a solution.

As for dbengine - you don't need a global reference. There's no
collection there. You don't have to set anything. It's a ready to use
pointer.


>>>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.

Obviously they don't. And I don't appreciate a condescending attitude.
But I'll only add, that I am trying to learn this stuff as I go along,
even if your or other people's egos get bruised, just for that fact.
Nothing I can do about that. That's on you, not me. I've said
repeatedly I'm not expert. For you to present that as some revelation
is insulting, because it suggests you've never really read anything
I've written. So, instead, I try to be fair. I don't exaggerate. I
read what people write in these message. And I explain what wasn't
explained to me. I try to be helpful and provide if not a definitive
answer, then one which works and works well for people in a similar
situation. I think it's only right. And I wish others had done the
same for me, when I was looking for similar answers.


>Well, your erroneous description of what CurrentDB does

What "erroneous description"? What are you talking about?


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

I posted a slow version of a directory/file app using a treeview. That
was for using Dir, without a collection, and reading everything twice,
instead of fso, the first time. And the fso works, in some cases,
faster than the standard file open/save. Your reply, if I recall, was
to disparage the fso absolutely, entirely, and as part of the
scripting dll, which you didn't much like, itself.


>Please educate yourself on the subject.

I did. And if you disagree, or see some logical error, in that - you
ought to say so, specifically. But you might be prepared, too, for the
possibility that I might consider a decision in that to be reasonable.
I might provide reasons supporting such decisions. And that may mean I
wouldn't agree with your criticism. It can happen.


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

Why? Are you trying to control what people say, now?

What if someone doesn't agree with you? Then what? As the saying goes
- you're startin to 'creep me out'.

>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.

Which means you are sarcastically trying to say I'm stupid, so that
whatever disagreement can't possibly be reasonable. I can't have a
reason for saying something, because I'm not capable of it, to begin
with.

If that's how you deal, socially, with people - there's nothing I can
say. But, I would suggest, even so, that you lighten up, and
reconsider your tone. I may respect your specific skill, which I don't
have. But you can learn to respect people, too.

Mark Johnson

unread,
Mar 13, 2003, 1:28:36 AM3/13/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>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:

>>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.

Why? Your previous objection was that an object is declared and then
set with For Each, implicitly. But nothing is set in the case, here.


>Unclosed references are a common cause of the "database won't
>close" error.

So that would be something you'd expect to see, at what point?
Wouldn't the case, here, be that a reference isn't created, but that
one already available, in this case the primary system object, is
passed as an argument. And you don't need to 'free up' something like
that, because you have no handle, no variable, no name to refer to
which you could free up if you wanted to.


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

You don't mean 'good luck', of course. But it's easy to say, perhaps,
because you don't like the idea because it wasn't yours. I don't know.
But you have to give specifics when you say something like that. If
it's 'buggy', then surely you can why. And if people don't agree with
those reasons, then maybe it's not.


>You are obviously in over your head here. Please stop posting

You don't go on UseNet and tell people not to post . . to UseNet, just
because you disagree with them, or think that maybe you're ultimately
wrong about something, and don't want to be embarrassed any further.

You hve to think about these things, clearly. You owe it to the people
that would pay you, and even to those that would read messages on
UseNet. You have to give it your best.

Mark Johnson

unread,
Mar 13, 2003, 1:46:28 AM3/13/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>I've discovered that you closing a db object reference before


>setting to Nothing releases resources.

> 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.

If set with currentdb. Nobody argues that. It isn't a question. And
the second object assignment fails because you essentially took back
your Dim declaration; it should fail on the assignment. But I wonder
how this is able to return even a record count, though. It would
appear that the db instance is stored like a vector table, and that
the dataset is independently referenced, afterward. And I can sort of
see the sense of that, at least in implementing it. But that could
change in subsequent releases. It would also seem that this should
check to see if the collection is still in use before trying to access
the stored dataset, even if the dataset is intact and reliable. I
would think that freeing the collection is an indication that objects
created by reference to that collection should also be discarded.


>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().

Of course, not. CurrentDB is the object. It's a snapshot of it. I'm
just wondering when you would programatically try to close the db
which is running the code that would close it? This is a case of
creating another database and thinking that using that somehow removes
the link to the database one opened with? But the code isn't running
on db(0)(1), but on (0)(0).

Mark Johnson

unread,
Mar 13, 2003, 2:33:31 AM3/13/03
to
dXXXf...@bway.net (David W. Fenton) wrote:

>1023...@compuserve.com (Mark Johnson) wrote in
><vi8t6vcqp21h756hd...@4ax.com>:
>>dXXXf...@bway.net (David W. Fenton) wrote:

>>>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.

>So far as your limited understand goes?

Well, but I've been asking for any specific example, for a long time,
now. And all you've produced is pointless insult, as your 'argument'.


Finally, you get around to it:

>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.

Okay. You've said that. And I said I couldn't find anything like that
in the help files.

And:

>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

What you mean to say is that dbengine will report one short. And you
did kind of leave out the important part - how you're assigning,
creating that query def. You need to be specific in this.


Public Function xx()
Dim db As Database, qdf As QueryDef

Set qdf = DBEngine(0)(0).CreateQueryDef("qry1", _
"SELECT tblGroupsList.anID, tblGroupsList.txCategory " & _
"FROM tblGroupsList ORDER BY tblGroupsList.txCategory;")

Set db = CurrentDb()

' these two will report different numbers

Debug.Print db.QueryDefs.Count
Debug.Print DBEngine(0)(0).QueryDefs.Count

qdf.Close
Set db = Nothing
End Function


But it reports the same number, and puts a new query in the database.
They report the same thing.


> 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.

I understand. It means being aware of that global db variable,
whatever you named it, and either covering programmatic object
additions, or remembering this in each case. It could get confusing.
It could be something one forgets to do. Rememember, you don't Dim the
db, in the context, here. It's already been set globally.


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

But in what way? how so? In the example you gave, above, it didn't
work as you thought it did. It wasn't as you described. That's why I
asked you to be specific. You might be assuming things that aren't
true. With specific code, it's easy enough to check.


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

The 'workaround' is more straightforward than what you've proposed, as
explained to you. Both require a global and initiation. Both do not
require the maintainance and closure that the collections object
requires.


>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.

That's quite a comedy routine you've got going.


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

Well, you can't say that of yourself. Myself, I don't care. Whatever
works best, is best. But you - you've got this religious mission
you're on. You're committed to the 'cause' of something as
inconsequential as whether or not to use a global db or not. So just
think about what you're doing. Wish you would.


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

>Eh? What are you talking about?

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

Etc.

>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.

Agreed.


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

Which is why I proposed a simple cover to fix the specific problem.
And a global cover, which you so zealousy promote, would be fine, but
presents more complexity, and I think needlesly so. That's the only
difference.


>You make your choice.

Okay.


Mark Johnson

unread,
Mar 13, 2003, 3:50:43 AM3/13/03
to
Mark Johnson <1023...@compuserve.com> wrote:

>your Dim declaration; it should fail on the assignment. But I wonder
>how this is able to return even a record count, though.

And the answer was just provided by, Paul, in another message:

A DAO recordset's Parent property points to its database.
Keeping the recordset alive keeps the database alive also.

David W. Fenton

unread,
Mar 13, 2003, 9:26:12 AM3/13/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<7i807v4mb6aev61bv...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>1023...@compuserve.com (Mark Johnson) wrote in
>><hb3u6v03d368e9a6h...@4ax.com>:
>>>"Michael \(michka\) Kaplan"
>>><mic...@spamless.trigeminal.nospamcom> wrote:
>
>>>>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. . . .
>
>>I posted the solution for that for code
>
>It was about a speed difference. That was the comment.

There are no real-world speed differences.

You only see a speed difference when you write code that does
things in an inefficient fashion.

Why choose your methods on the basis of tests that don't represent
best practices in coding?

>>parameter, already initialized), and there's never any need for
>>it in SQL, as you can do the lookup with the SQL.
>
>Right. If you have SQL, why would you use lookup? The rework of
>dlookup that I use, is just like tlookup from Trevor, in that it
>uses SQL. It's not that SQL is necessarily faster than FindFirst.
>But it's better integrated, and wouldn't pose any problem between
>DAO and ADO.

That's not what I'm talking about.

We were talking about calling a lookup function in a non-code
context (i.e., one in which you could not pass an already
initialized db variable). This would include a form or a report or
a query. In all cases, you have perfectly good methods for getting
the result from the SQL itself. If you don't, you could easily
write a wrapper function around your lookup function and have that
wrapper function deal with passing a global db variable.

As to loops in code, we all agree there's no need to repeatedly
initialize a db variable in that context, no?

So, there is no case whatsoever in which you'd need to repeatedly
re-initialize a db variable.

So, the performance difference between the two methods of doing so
ends up being a few milliseconds. And the faster method is not 100%
accurate.

>>If you saw a performance hit, then your code was bad or you were
>>using the wrong methods to get your information.
>
>Or probably just like the examples from the very Access help
>files.

I explained this elsewhere.

>>>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. . . .
>
>>Any poorly designed application can be annoying.
>
>And a sluggish application, however well you think it's designed,

>can grate, as well. . . .

I am absolutely certain that no user could ever tell the difference
between an application that used a global db variable initialized
with CurrentDB() and the exact same app initialized with
DBEngine(0)(0). I would expect that an application coded with a
single initialization of a global db variable with CurrentDB()
would be faster than an app that used DBEngine(0)(0) to repeatedly
initialize db variables in context. How much slower, I don't know.
But you're also introducing multiple possible points of failure by
repeatedly calling it.

> . . . People like to click on that screen very


>quickly. I've seen it repeatedly, in various contexts, different
>offices. The speed at which the operator runs that console,
>whether a clerk or a researcher, can be something of a point of
>pride. It's not an unusual thing, in other words.

You are arguing with a straw man. No one has suggested anything
other than a single global db variable initialized when the app is
open.

There are *no* contexts in which you need, ever, to repeatedly
initialize a db variable. Even if you choose to use a lookup
function in a query, you can write a wrapper function to initialize
the variable and pass it to your lookup function.

>>Do you give *any* thought whatsoever to these issues before
>>posting?
>
>You've had difficulty explaining yourself in this sort of thread -

I have? Everyone else understands what I'm talking about. You are
the only one who repeatedly misinterprets my statements, mostly
because you don't have sufficient experience to interpret them.

>maybe not in other threads, on other subjects. But you also were
>quite opinionated on the whole area of the web, and the browser
>wars, and such. And you didn't seem to much like disagreement in
>that, either. And your insulting method is just not convincing. It
>isn't on any ng, on any subject. It says more about yourself.

Yes, let's turn the conversation to an attack on *me*, instead of
admitting that you don't have a frigging clue about what you're
talking about.

At least you didn't do that in the web browser discussion, though
you certainly never paid the slightest bit of attention to anything
I said.

>>>>As to your insistence on the need for a "Set db = Nothing",
>>>>this is also not needed.
>
>>I disagree with MichKa on this one. I think that the global db
>>should be de-initialized on application exit, both closing it and
>>setting it to Nothing.
>
>You would write - db.close? and then set db = Nothing? on exit.

That's what I do, yes.

It works.

I've been doing it for years.

[]

>>I've been using db.Close on db variables initialized with
>>CurrentDB in A97 and A2K forever. It is not buggy. However, if
>>the reference is created with DBEngine(0)(0), you could have
>>problems.
>
>Okay.

That's all you have to say? Every time someone points out problems
with the choice you are supporting, you just ignore them. Yet you
want to make the *one* "problem" with the other alternative a
deal-breaker.

>>Both DBEngine(0)(0) and CurrentDB return an object of type
>>Database that is a memory structure reflecting the structure of
>>the database pointed to. Though the objects are of the same type,
>>they have different relationships to the physical state of the
>>object they represent, with DBEngine not necessarily having
>>current information on collections
>
>If dbengine doesn't record an object, then what does?

You mean if the collections of DBEngine don't reflect the current
state of the collections, what does? The real collections, in the
real MDB. When you refresh a collection, what you're doing is
telling the memory structure you're utilizing to check the real MDB
and update its internal structures to mirror those of the real
database. This is the same as a refresh or requery in a recordset.

So, if you want current information:

DBEngine(0)(0).QueryDefs.Refresh
Set db = DBEngine(0)(0)

will give you current information. Using CurrentDB(), you wouldn't
need the first line, as it refreshes the collections before
returning the reference.

Or:

Set db = DBEngine(0)(0)
[add a QueryDef]
db.QueryDefs.Refresh

It doesn't matter whether you use CurrentDB() or DBEngine(0)(0) to
initialize your database variable, though. Either way, if you
change the collections and then need to work with those
collections, you have to refresh the collectios for the db variable
you're using.

There is 100% equivalence here between db variables initialized by
both methods.

>>and CurrentDB() always being 100% current *at the
>>time the reference is returned*.
>
>And that creates the overhead. And you can measure the difference,
>very easily, between currentdb and dbengine.

But the point is, you call it only *once*.

>>>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? . . .
>
>>It is inaccurate, and it requires extra refreshing.
>
>But what's a specific example? How would you show that dbengine is
>in a way, secretly, currentdb - or suffers from those problems? Or
>are you just confusing the two, at this point? What, specifically,
>are you talking about? Specifically.

You asked what the objection is to DBEngine(0)(0). I answered: it's
not reliable.

Your object to CurrentDB() is a relative speed difference of quite
a bit (because of the overhead of refreshing collections).

With a global variable initialized once, the speed difference
amounts to a few milliseconds, which a user will never notice.

If you change the collections, you refresh them for the global
variable, just as you would if you were using DBEngine(0)(0). That
means that, on the issue of refreshing collections, the
initialization methods are irrelevant.

That very clearly and specifically answers your original question.
Your response to that makes no sense to me. I don't know why you
think that "dbengine is in a way, secretly, currentdb." That's a
nonsensical statement. It does *not* "suffer from [the] problems"
of DBEngine(0)(0) (one problem, actually), as CurrentDB() is always
accurate. The downside is that it's slower, but the point that
everyone has been hammering away at, trying to convince you, is
that the difference makes no difference to a real application
because it is only going to be called once.

There are absolutely no loops that would require re-initialization.

None.

>>2. it can cause errors that CurrentDB references do not
>>(DBENgine(0)(0).Close, for instance).
>
>When would you use dbengine(0)(0).close? That would be an attempt
>to programatically close the database that is running?

Yes. And I have no idea what would happen. Likewise, db.Close when
the reference was created with DBEngine(0)(0) is reported to be
problematic, while no one has reported problems with the same
procedure when the reference was created with CurrentDB().

>Would this be the one example you've been referring to, above?

No. It's completely irrelevant.

But it shows that references created with DBEngine(0)(0) cannot be
cleaned up as reliably as those created with CurrentDB().

>>> . . . 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).
>
>>I don't know about Michael, but I don't want to take the risk
>>that a user of one of my apps may use a wizard, compact and then
>>have the problem with DBEngine(0)(0) pointing to the wizard
>>instead.
>
>That's why you need something just like I suggested, which
>addresses that specific problem.

The point is that there is *no need* for your suggestion. You need
only use CurrentDB().

>>Re-using a single persistent db variable solves the performance
>>problem of CurrentDB().
>
>The overhead. But not that it is a static reference, and other
>things.

A "static" reference is what DBEngine(0)(0) returns whenever you
call it. And if you initialize a global variable with it, you get
exactly the same "problems" as when initializing with CurrentDB().
So, there is no difference between the two methods in this regard.

In other words, the performance objection has not real-world
downside.

>>Most of the issues you've raised are created by your desire to
>>avoid CurrentDB(), which is simply irrational.
>
>Or the "desire" to say otherwise, is "simply irrational". I would
>think both, and other, solutions would be fine, if you are able to
>manage everything. But using the dbengine, directly, just seems
>cleaner, more reliable, and doesn't introduce potential problems.

\/\/hatever you say, Mr. Access genius.

David W. Fenton

unread,
Mar 13, 2003, 9:40:59 AM3/13/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<6n807v0lfvphrl1t4...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>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:
>
>>>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.
>
>They're not. I ran this in writing a substitute for dlookup. And
>repeatedly setting and freeing a db reference is MUCH slower with

>currentdb than dbengine. . . .

Can you *read*?

DBEngine(0)(0) is very different from
DBEngine.OpenDatabase([database name]). The latter is the same as
CurrentDB() in terms of performance.

Of course, I'm just repeating what I already wrote.

> . . . In fact, there was much less difference


>between FindFirst and an SQL string than these two.

You didn't read what I wrote -- you responding as though I'd said
DBEngine(0)(0) instead of DBEngine.OpenDatabase.

>>Your whole argument about storing the db name in a variable is a
>>red herring, and always was.
>
>Well, and straw burns nicely, I guess. But what I said was that
>the idea of a global ref from currentdb seemed to solve that
>problem of always setting and freeing, as typically described in
>the documentation supplied with Access. As far as that went,
>you'll see I agreed. It was a good idea. Should be a standard
>mention in all the documentation. But you run the risk of abusing
>that variable, of not closing it, or of having it get out of sync,

>in certain cases. . . .

How will it get out of sync?

DBEngine(0)(0) is *always* out of synch until you do something to
rectify the problem, so why the objection to CurrentDB() for
something that is just as much of a problem with a persistent
reference as it is with DBEngine(0)(0) regardless of whether you're
using a persistent reference or calling it repeatedly?

> . . . Again, all this might be exceptional. But it


>might be the exception you forgot about just when Murphy decides
>to lay down his law. What I proposed is more robust, and just a
>cleaner solution, overall.

Eh? What are you going on about? Assuming you are altering your
collections and need to work with them in code, collections have to
be manually refreshed for any new reference created with
DBEngine(0)(0). For any persistent variable, it doesn't matter how
you create it -- the collections will need to be refreshed.

Collections are a red herring here. They only explain why
CurrentDB() is slower. CurrentDB() is only better in this regard if
you are calling it anew each time you create a db reference.

I have been arguing, at all times, against calling CurrentDB() more
than one time per application session. So, in regard to collection
refreshing, the issues are 100% equivalent between the two methods
once you limit the use of CurrentDB() to creation of the persistent
single reference.

>>You were advocating a global constant. I was just pointing out
>>that this is dumb.
>
>Well, I agree that if one is prone to change the db name, as one
>often does when writing it, developing it, it's better to get the
>name dynamically, on start-up. So I did.

And you can avoid the need for even that by simply using
CurrentDB().

With no loss of functionality.

With no extra coding.

You add a few milliseconds to the startup time, though. If that's
too horrible for you to contemplate, then you know nothing about
real-world applications.

>>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.
>
>But you don't have to. There's no loop. You get the loop in what -

>.1 percent of 1 percent of all cases. . . .

There's never ever any need for a loop.

> . . . In fact, 'michka' is saying,


>and perhaps has just been saying all along, it's such a small
>possibility, you might as well forget it. But, just in case, you
>loop. But you'll never loop. But just in case, it's there.
>Problem, rare problem, was mentioned. Here's a solution.

MichKa is quite clearly not talking about loops.

>As for dbengine - you don't need a global reference. There's no
>collection there. You don't have to set anything. It's a ready to
>use pointer.

Uh huh.

You're saying that using a global variable set at application
opening (regardless of the method used) will be no faster than
repeatedly calling DBEngine(0)(0)?

How do you know that?

Have you timed one call to it to 10,000 calls to it?

And have you considered that each time you call DBEngine(0)(0) you
run the risk of getting a reference to a wizard?

If you're calling it only once to initialize your global variable,
and doing it as the application opens, yes, I'd agree that it
probably makes no difference whatsoever which method you use, as
there's simply not chance for the wizard to run.

However, if you run a wizard and compact, you still run the risk on
startup of getting the wizard reference.

But, yes, the possibility is very remote.

Of course, with CurrentDB(), it is impossible.

[]

David W. Fenton

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

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>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:
>
>>>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.
>
>Why? Your previous objection was that an object is declared and
>then set with For Each, implicitly. But nothing is set in the
>case, here.

Have you worked with the VBA development kit, i.e., the tools that
allow you to install VBA in an application as the programming
language? People who *have* done so tell us that implicit
references are a problem. This is why the code examples in the
Access help files always use variables for these things. Those
examples also fail to clean up after themselves, something that was
found out a long time ago to be problematic.

>>Unclosed references are a common cause of the "database won't
>>close" error.
>
>So that would be something you'd expect to see, at what point?

You may never see it.

But leaving references open can, under some circumstances, cause
that problem.

Cleanup of all object references in the appropriate scope avoids
the problem.

>Wouldn't the case, here, be that a reference isn't created, but
>that one already available, in this case the primary system
>object, is passed as an argument. And you don't need to 'free up'
>something like that, because you have no handle, no variable, no
>name to refer to which you could free up if you wanted to.

I bow to your superior knowledge of the inner workings of VBA and
DAO. My years of writing working code mean nothing in comparison to
your superior accuity.

>>You're suggesting, I take it, always using DBEngine(0)(0)
>>directly? Well, good luck. You'll have very buggy applications.
>
>You don't mean 'good luck', of course. But it's easy to say,
>perhaps, because you don't like the idea because it wasn't yours.

I discarded it a long time ago.

[]

>>You are obviously in over your head here. Please stop posting
>
>You don't go on UseNet and tell people not to post . . to UseNet,

>just because you disagree with them, . . .

It's not that I disagree with you. It's that you repeatedly post
misleading and wrong information that others may stumble on in
Google. This is irresponsible.

[]

David W. Fenton

unread,
Mar 13, 2003, 9:47:49 AM3/13/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<m8907vouu469h69sb...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>nos...@nospam.com (J Ohlson) wrote in
>><Xns933C6FFC61...@195.67.237.51>:
>>>Mark Johnson <1023...@compuserve.com> wrote in
>>>news:vi8t6vcqp21h756hd...@4ax.com:
>
>>I've discovered that you closing a db object reference before
>>setting to Nothing releases resources.
>
>> 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.
>

>If set with currentdb. Nobody argues that. . . .

Change the CurrentDB() to DBEngine(0)(0). The result will be the
same.

> . . . It isn't a question.


>And the second object assignment fails because you essentially
>took back your Dim declaration; it should fail on the assignment.
>But I wonder how this is able to return even a record count,
>though. It would appear that the db instance is stored like a
>vector table, and that the dataset is independently referenced,
>afterward. And I can sort of see the sense of that, at least in
>implementing it. But that could change in subsequent releases. It
>would also seem that this should check to see if the collection is
>still in use before trying to access the stored dataset, even if
>the dataset is intact and reliable. I would think that freeing the
>collection is an indication that objects created by reference to
>that collection should also be discarded.

I haven't the first clue what you're going on about because you're
using terminology very inconsistently.

>>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().
>
>Of course, not. CurrentDB is the object. It's a snapshot of it.

Uh, so is a db variable initialized with DBEngine(0)(0).

>I'm just wondering when you would programatically try to close the

>db which is running the code that would close it? . . .

The .Close is closing the memory structure represented by the
variable it is attached to. The actual db it refers to can't be
closed.

> . .. This is a case


>of creating another database and thinking that using that somehow
>removes the link to the database one opened with? But the code
>isn't running on db(0)(1), but on (0)(0).

No, that's not it.

David W. Fenton

unread,
Mar 13, 2003, 9:48:04 AM3/13/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<1jh07vc21fd05d5g6...@4ax.com>:

No, it doesn't.

Try the code I posted.

David W. Fenton

unread,
Mar 13, 2003, 10:02:40 AM3/13/03
to
1023...@compuserve.com (Mark Johnson) wrote in
<laa07vs62nniaub1q...@4ax.com>:

>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>1023...@compuserve.com (Mark Johnson) wrote in
>><vi8t6vcqp21h756hd...@4ax.com>:
>>>dXXXf...@bway.net (David W. Fenton) wrote:
>
>>>>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.
>
>>So far as your limited understand goes?
>
>Well, but I've been asking for any specific example, for a long
>time, now. And all you've produced is pointless insult, as your
>'argument'.
>
>
>Finally, you get around to it:

The paragraph below simply repeats information anyone can read in
the help files. I assumed it wasn't necessary to repeat the
obvious.

>>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.
>
>Okay. You've said that. And I said I couldn't find anything like
>that in the help files.

It's there.

>And:
>
>>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
>

>What you mean to say is that dbengine will report one short. . . .

I don't know. It will report something different, depending on what
has happened since you opened the MDB.

> . . . And


>you did kind of leave out the important part - how you're
>assigning, creating that query def. You need to be specific in
>this.

Why? The point is you need a reference to the database to do it

>Public Function xx()
> Dim db As Database, qdf As QueryDef
>
> Set qdf = DBEngine(0)(0).CreateQueryDef("qry1", _
> "SELECT tblGroupsList.anID, tblGroupsList.txCategory " &
> _ "FROM tblGroupsList ORDER BY
> tblGroupsList.txCategory;")
>
> Set db = CurrentDb()
>
> ' these two will report different numbers
> Debug.Print db.QueryDefs.Count
> Debug.Print DBEngine(0)(0).QueryDefs.Count
>
> qdf.Close
> Set db = Nothing
>End Function

I don't know. I've never created a querydef with one db reference
and compared it to another db reference.

>But it reports the same number, and puts a new query in the
>database. They report the same thing.

I've said all along that the collection refreshing issue is pretty
much irrelevant.

Try the same code with a non-Jet object (i.e., not a table or
query). You may find differences. I don't know, since I never
create objects in a front end.

>> 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.
>
>I understand. It means being aware of that global db variable,
>whatever you named it, and either covering programmatic object
>additions, or remembering this in each case. It could get
>confusing. It could be something one forgets to do. Rememember,
>you don't Dim the db, in the context, here. It's already been set
>globally.

I am able to remember these kinds of things. I just look them up in
the application's main module, along with all the other globals.

The argument from "forgetting" means you'd never use any globals,
ever, or any custom code, or anything that you'd have to remember
about a specific application.

That's nonsensical.

>>No, that's not true, unless you're utilizing CurrentDB()
>>directly, which is highly dangerous because it can create
>>implicit references.
>
>But in what way? how so? In the example you gave, above, it didn't
>work as you thought it did. It wasn't as you described. That's why
>I asked you to be specific. You might be assuming things that
>aren't true. With specific code, it's easy enough to check.

There are actually two classes of collections, Jet collections and
Access collections. Tables and QueryDefs are Jet objects, whereas
Forms, Reports, Macros and Modules are Access objects. Perhaps the
difference is in those collections.

I don't know, and I don't really care, because it's all a red
herring, despite that fact that, yes, you've shown that something I
wrote was not true in your example case. Of course, no one would
ever write code like that, so it doesn't matter.

But you do get a brownie point for catching it.

>>I don't know what you're going on about. I never saw the point of
>>your elaborate workaround code in the first place.
>
>The 'workaround' is more straightforward than what you've
>proposed, as explained to you. Both require a global and
>initiation. Both do not require the maintainance and closure that
>the collections object requires.

Eh?

You would write code that repeatedly calls DBEngine(0)(0) and
therefore runs the risk of returning and invalid reference in many
parts of the code, vs. using a practice that will never be
inaccurate.

I'm glad I'm not your customer.

[]

>>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.
>
>Agreed.

But it's a speed difference that will never make a difference in a
real-world application, unless you're writing bad code.

>>2. accuracy. DBEngine(0)(0) will occasionally be wrong.
>
>Which is why I proposed a simple cover to fix the specific
>problem. And a global cover, which you so zealousy promote, would
>be fine, but presents more complexity, and I think needlesly so.
>That's the only difference.

You are simply wrong. Period.

End of thread.

Trevor Best

unread,
Mar 14, 2003, 3:22:57 AM3/14/03
to
On Wed, 12 Mar 2003 14:55:48 GMT, in message
<933C611CCdf...@24.168.128.74>, dXXXf...@bway.net (David W.
Fenton) wrote:

>I have been told that db.Close on a reference created with
>DBEngine(0)(0) can have unpredictable results, including crashing
>the application.

Last time I tried, it gave me "Invalid Operation" error.

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

(replace sithlord with trevor for email)

Mark Johnson

unread,
Mar 14, 2003, 5:05:32 AM3/14/03