The DBengine(0)(0) problem
Mark Johnson
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
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
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
<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
>' 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
>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.
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
> 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
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
<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
> 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
>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
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
>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
<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
>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
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
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
>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
>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
>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.
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
>>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
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
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
> >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
>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
>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
>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
<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
>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
>"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
"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
<AyNba.18159$yc5....@twister.nyroc.rr.com>:
That's when the fun begins.
Mark Johnson
>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
>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
>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
>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. 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
>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
>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
>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
>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
>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
>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
No, it doesn't.
Try the code I posted.
David W. Fenton
>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
<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
>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:
>>>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).
That wasn't the comparison.
>>You've had difficulty explaining yourself in this sort of thread -
>I have? Everyone else understands what I'm talking about.
I think "everyone else" can indeed judge for themselves.
>>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.
"Frigging clue", and so on, suggests you don't seem sufficiently
self-aware to see the insults, the constant stream of insults, that
you include in various messages. And I'm just suggesting that it's not
only not convincing, but might suggest things about yourself that you
might prefer others would not see.
>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.
I disagreed with you.
>>>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?
. . . .
>>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.
Which descend from 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.
>You asked what the objection is to DBEngine(0)(0). I answered: it's
>not reliable.
And I asked - but what's a specific example ? etc. . . . .
>With a global variable initialized once, the speed difference
>amounts to a few milliseconds, which a user will never notice.
I'm sure. I've said, repeatedly, in many messages, now, that it seems
a good alternative, and certainly addresses that particular problem.
But then it introduces some of its own. And what I suggested did not.
>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.
Yes, if you meant something of the sort, I think it would be.
>>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().
But why would you do that with dbengine, more specifically? With
currentdb, you don't have the db, but a pointer to a separate
collection taken from the db. You might want to free up that memory,
whether the reference was assigned locally or globally. Thus you would
need to close it, as you said you disagreed with 'michka' on that.
>>>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().
I just think it's less messy, more elegant, to simply set and forget,
rather than worry about managing the global ref.
>\/\/hatever you say, Mr. Access genius.
Again, I respect all of your specific expertise, which I don't have.
But you need to understand that people also bring experience of their
own from however many years of programming other languages and
platforms. And I did mention that I've at least had some passing
familiarity with Access since version 1. And people can learn.
Learning is possible. If you resent that in people, then, and I've
said this before - it's on you, and not me. There's nothing I can do
about that. But you ought to be a little less condescending, because
contrary to what you imagine, it doesn't make you look like the
experience professional that you might imagine it would.
Mark Johnson
>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:
>>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
I know. You've been this condescending in more than a few messages,
now. I just suggests you can't address the issue, for some reason. It
doesn't make you look like 'more of an expert'. It really doesn't.
>>>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.
Well, others can judge, here. But I'll be interested to see how you
replied in that message where you finally were specific about this,
and the specific example didn't work as you thought it would.
Mark Johnson
>1023...@compuserve.com (Mark Johnson) wrote in
><1jh07vc21fd05d5g6...@4ax.com>:
>
>>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.
>No, it doesn't.
Take it up with, Paul, then. What he says, there, makes perfect sense.
I couldn't understand why, when you prematurely closed db in your
example, you were still able to reference the rs object. What Paul,
said, should be part of the Access help file, frankly - word for word.
Mark Johnson
>>If set with currentdb. Nobody argues that. It isn't a question.
>Change the CurrentDB() to DBEngine(0)(0). The result will be the
>same.
The result is the same. That's the point. Perhaps what happened is
that in debugging, or coming up with an example, you didn't close
something. Perhaps the numbers would start to diverge in that case.
But it you just run it the first time, as above, the numbers reported
are the same, and a new query is stored in the db.
>>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.
This was answered, and you disagreed with the guy who answered - big
surprize - as:
A DAO recordset's Parent property points to its database.
Keeping the recordset alive keeps the database alive also.
And it makes sense to me.
>>Of course, not. CurrentDB is the object. It's a snapshot of it.
>Uh, so is a db variable initialized with DBEngine(0)(0).
But the problem you've been suggesting, in numerous messages, is not
one which you can reproduce with the specific code you suggested, as
seen above.
>>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.
Dbengine is the object from which the others descend. Currentdb
creates a collection which needs to be freed.
Mark Johnson
>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:
>>>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.
Where in the "help files"?
>>>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.
But where? Because what you show, below, doesn't work the way you
though it did.
>>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
But it doesn't.
>>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?
And this is why:
>>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.
Well then you didn't say what you meant, or were trying to be
'clever'. I don't know. I tested actual code, that actually ran. And
it didn't do what you said it would.
As I wrote:
>>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 specifically said - a query def - above.
>>> [add a new QueryDef]
Etc.
Specifically, cut and paste the code you used to test. I did. And what
it showed you didn't work as you thought it would. So if you're doing
something fundamentally different, cut and past the entire procedure,
and let's see it.
>The argument from "forgetting" means you'd never use any globals,
I wonder, though, if in testing whatever specific procedure, you
didn't raise some errors, and prematurely terminate execution.
Perhaps, objects weren't freed, and the numbers reported did start to
diverge. Perhaps in that circumstance. You should double-check, and
see.
>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.
Well, fine. Let's see the specific example that actually shows these
differently reported numbers. There'd be no question. You'd run the
code. That's what it would show. There's a real bottom line in
discussing anything with VBA. The code will show - it will tell.
>I'm glad I'm not your customer.
I just wonder if some reading these threads begin to consider what you
do, in light of that. But that's why I sincerely ask you to be careful
about your attitude, to test and be specific, to give it your best,
and if you disagree, do so in a civil and professional manner.
>>>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.
Which is code language, though, meaning not "bad code" but that which
doesn't incorporate currentdb as suggested in the very Access help
files, but rather incorporates it exclusively as a global variable set
when opening the db.
>>>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.
And _I_ don't know what more to say, here. I really don't. This is
getting repetitive, because you won't consider a contrary opinion, and
new information. You presented an example, instead, of what a problem
might be. That was fine. I expected it would show what you indicated.
And the example showed nothing of the sort. So . . . I don't know.
You're trying to say something. But I don't think you've been
expressing yourself very well. Maybe there are problems. Maybe there's
some assumptions being made, maybe in both approaches, that are
unwise, and that would fail in subsequent releases of Access. But I
think either way, or even other ways, all work well. I just think
trying to set, close, manage a global reference is messy, and
needlessly cumbersome, and prone to certain errors. Locally
controlling access to a pointer seems more reliable, and more logical.
Mark Johnson
>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:
>>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*?
A lot of things, yes. For instance, when Paul explained about the
retention of a db reference if an object for it remained open, I read
and considered it would be true. You read that, and vehemently
disagreed. And I can't imagine why.
>> . . . 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.
But I've been talking exclusively about dbengine(wrk)(db). That's it.
That's the issue, here. That's what I used in what I suggested as a
solution to the compact/wizard problem, and whatever else.
>>>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?
Currentdb is a static collection. It's not automatically updated, but
a snapshot of the db, at a certain point and time. Dbengine,
conversely, always points to the current db as it is.
>DBEngine(0)(0) is *always* out of synch until you do something to
>rectify the problem
In what specific way? What code, what procedure, can you provide that
concretely demonstrates that supposed flaw?
>I have been arguing, at all times, against calling CurrentDB() more
>than one time per application session.
I understand that. That's you solution, in fact - don't do as
suggested in all the Access files, but rather create and set, once for
all, a global ref using currentdb. I, and others, have suggested
potential problems with that approach. 'Michka', insisted upon the
solution you prefer, as well, but simplified matters by also saying
that there simply was no need to close that global reference. And that
does, in fact, simplify your solution, in that regard. But you didn't
want to agree with him.
>>>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.
There is . . no loop. There is no loop. Only in the infinitessimal
number of cases, where this dbengine problem arises, will the loop
address that problem, directly. Otherwise - there's no 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.
Neither is anybody else.
>>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.
>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)?
Not at all. Setting as global solves all that collection creation
problem. I'm getting tired of saying this, over and over again. That's
not the issue. The issue is how to manage that global, keep it in
sync, even close, which you don't have to worry about in the approach
I suggested as an alternative.
>How do you know that?
I know that straw burns. I don't know at what precise temperature.
Paul
> dXXXf...@bway.net (David W. Fenton) wrote:
>>> A DAO recordset's Parent property points to its database.
>>> Keeping the recordset alive keeps the database alive also.
>
>> No, it doesn't.
>
> Take it up with, Paul, then. What he says, there, makes perfect sense.
Think again, can't you guess David's problem with handling that
statement in context?
David W. Fenton
>dXXXf...@bway.net (David W. Fenton) wrote:
>>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).
>
>That wasn't the comparison.
If you think that, then your reading comprehension skills are just
about zero, as it is the only thing I've ever been discussing,
since it's the only method that makes any sense whatsoever.
TC
I don't pretent to have read all of this thread. The complexity of the issue
does not seem to warrant it! So how about we all agree on a a final
position?
Here's what I think (as I said, not having read all the posts):
WHICH ONE TO USE:
- CurrentDb() is demonstrably slower than DbEngine(0)(0).
- The difference is irrelevant if you only execute CurrentDb() once, & store
that reference in a global variable.
- Access 2000 (2002?) Help suggests that CurrentDb() is preferable in
multi-user applications.
ERROR TRAPPING:
- There is no point in providing any error trapping for a call to
CurrentDb() or a reference to DbEngine(0)(0) - any more than there is any
point in providing error trapping for a statement like "MyVar = 1", where
MyVar is an integer variable. If anything goes wrong with such statements,
there is a catastropic error in the environment, which will surely cause
everything else to fail, big time.
CLOSING & DEALLOCATING:
- Variables referencing CurrentDB() &/or DbEngine(0)(0) can safely be set to
nothing, when you have done with them.
- Neither of those variables should ever be explicitly .Close'd.
CONCLUSION:
- set a global variable to CurrentDb();
- use that global where-ever you need a reference to the current database;
- set that global to empty (if you want to) when you've finished with it -
but do not .Close it.
Yes? No?
TC
Brendan Reynolds
no 'final' position.
I've never found a need for a global reference to the current database. I
create the reference as and when I need it and destroy it when I'm finished
with it. Would the code be faster with a global reference? Perhaps, but if
the operation already appears instantaneous to the user, who cares?
Of course, if and when I encounter a situation where using the global
reference makes a noticeable difference to the user, then I'll use it.
--
Brendan Reynolds
bren...@indigo.ie
"TC" <a...@b.c.d> wrote in message news:1047727379.373618@teuthos...
TC
"Brendan Reynolds" <bren...@indigo.ie> wrote in message
news:s5Eca.2761$pK2....@news.indigo.ie...
> You're joking, TC, right? We will never all agree on anything, and there
is
> no 'final' position.
Good point! :-)
> I've never found a need for a global reference to the current database. I
> create the reference as and when I need it and destroy it when I'm
finished
> with it. Would the code be faster with a global reference? Perhaps, but if
> the operation already appears instantaneous to the user, who cares?
I agree - unless the ref is used in a loop, in which case there can
certainly be a visible performance impact.
> Of course, if and when I encounter a situation where using the global
> reference makes a noticeable difference to the user, then I'll use it.
Here's a thought. It was said that some of the old Burroughs(?) machines had
a big coil of cable in the back of one of the cabinets. When you paid to
upgrade from mainframe v-whatever to mainframe v-whatever-plus-one, the
accountsant removed another k$x from your wallet, & the engineer just came
in a covertly removed the big coilf of cable - thereby upping the CPU speed
accordingly! Thus, perhaps use CurrentDb() initially, then change back to
DbEngine(0)(0) when the user pays to speeed the app up!
Just joking...
TC
rkc
"TC" <a...@b.c.d> wrote in message news:1047727379.373618@teuthos...
>
> I don't pretent to have read all of this thread. The complexity of the issue
> does not seem to warrant it! So how about we all agree on a a final
> position?
I quit reading the thread when it was clear that baseball bats would be the
only way to settle the disagreement.
> CONCLUSION:
> - set a global variable to CurrentDb();
> - use that global where-ever you need a reference to the current database;
> - set that global to empty (if you want to) when you've finished with it -
> but do not .Close it.
No. I do any access to the backend that is not bound to a form through
a class object. Any connection to the backend is initiated and and terminated
in the obvious procedures.
David W. Fenton
>CLOSING & DEALLOCATING:
>
>- Variables referencing CurrentDB() &/or DbEngine(0)(0) can safely
>be set to nothing, when you have done with them.
>
>- Neither of those variables should ever be explicitly .Close'd.
MichKa, who knows far more about Access than I ever will, says this
second one is correct.
However, I uniformly close them. I *never* use DBEngine(0)(0),
though, only CurrentDB(). Apparently there is a difference between
how db.Close reacts, depending on which of the two methods you've
used to initialize it.
David W. Fenton
<s5Eca.2761$pK2....@news.indigo.ie>:
>You're joking, TC, right? We will never all agree on anything, and
>there is no 'final' position.
>
>I've never found a need for a global reference to the current
>database. I create the reference as and when I need it and destroy
>it when I'm finished with it. Would the code be faster with a
>global reference? Perhaps, but if the operation already appears
>instantaneous to the user, who cares?
>
>Of course, if and when I encounter a situation where using the
>global reference makes a noticeable difference to the user, then
>I'll use it.
Have you considered number of connections?
If you're using CurrentDB() to get to data stored in linked tables,
will this create multiple connections to the back end database if
you have more than one db reference active at one time?
I really don't know for sure, but I do know that with a single
global db variable, it isn't an issue.
And I would say that it makes a certain sense to not continually
re-initialize something that is the same every time.
I mean, if use a DoubleQuotes constant you put it in a global
module, you don't repeatedly declare it within each subroutine that
utilizes it.
So, it just seems to me to make good sense.
And I do believe it *does* speed up db execution.
Another advantage is that it takes the place of opening that
connection to a back end table that people recommend for speed.
I guess that's probably why I've never gotten any extra benefit
from trying that!
Brendan Reynolds
news:933F678F8df...@24.168.128.78...
You may very well be right, David, but I just haven't experienced any of
these problems.
Now I'm not saying that there aren't situations in which these things may
make a difference, and I will certainly be grateful that I had the benefit
of these discussions if and when I encounter such a situation.. But I will
say that in my experience, more often than not, whether we use CurrentDb()
or DBEngine(0)(0), and whether we use a global database variable or create
and destroy one as and when we need it, makes no perceptible (to the user)
difference - and there are usually other areas of the application we could
be spending our time on that will make such a difference.
--
Brendan Reynolds
bren...@indigo.ie
Michael (michka) Kaplan
ever seen. Though I admit that I do not spent time in the
alt.britney.spears group so perhaps my list is skewed.
Here are some facts. Every one of them is completely true and verified
(in many cases by actually looking at Access/Jet/DAO sources):
1) Calling db.Close in Access when db is either DBEngine(0)(0) or
CurrentDB is a *no-op*. It does nothing. This is due to the fix for an
old bug in Access 2.0, where that call would still fail but would
cause problems with any open objects like recordsets. So they moved
the "able to close the db?" check to the beginning of the method from
the end. Any attempt to call will now not cause problems but it will
literally do NOTHING. If this text is not clear then you are welcome
to do it. Just as you are welcome to add any useless method call to
your app. But it will not do anything.
2) Setting db to Nothing in Access when db is either DBEngine(0)(0) or
CurrentDB will do the same thing as in any VBA reference variable --
it will lower the reference count to the object by one, and if the ref
count is 0 then it will destroy the object. Since the current database
has a permanent reference internally in Access, this will not destroy
the object and thus never have an effect.
3) There have been bugs in the past with DAO Recordset objects (and
especially Access RecordsetClone objects) where a dangling reference
would hold Access open due to the fact that the object would for some
reason think it could not close itself so it would hang open and the
orphan object would later hold Access open. There has never been any
such bug with a DATABASE object -- only with Recordset objects.
4) The only "official" benefit that CurrentDb has over
DBEngine.Workspaces(0).Databases (a.k.a. DBEngine(0)(0) is the fact
that all of the collections are guaranteed to up to date. This is easy
to do internally in Access but is a bit harder to do from DAO -- I
believe a function in DAO that had all collections up to date would be
about 8 times slower then CurrentDB (which is itself over 5000 times
slower than DBEngine(0)(0). Note that if you do not need collections
up to date that this benefit is not too terribly important. It is
terribly important in Access wizards, which create objects and then
have to be able to count on them being present. It has proven
important in other apps I have written, enough so that I have my own
methods of getting the current db that will selectively refersh
particular collections.
5) One "non-official" benefit that CurrentDb has over DBEngine(0)(0)
is some known cases where the latter is not pointing to the current
database but is instead pointing to a wizard db. The
DBEngine.Workspaces(0).Databases collection is one where if the
current db is closed, as it is during a compact, and for some reason
another db is considered open, then the current db may not be the
zeroeth item in the collection anymore. I have never seen a problem
with this in a production app *unless* the user was able to both run
wizards and compact the db. But as an author of wizards, that I have
written for both Trigeminal and Microsoft, I can state that this can
be an important scenario as a *lot* of applications.
6) One official downside to CurrentDb over DBEngine(0)(0) is the perf
hit, which can be over 5000 times slower. However, in the majority of
cases this is not an issue since you simply need to not call it in a
loop. You can use a single refcounted global like Access wizards do,
or you can do something less official than that if you are a normal
persom. But either way this is a problem that any reasonabl
intelligent person can solve with a lot less thought than most
problems (if they understand all of the issues).
7) Extensive argument over which method is better on the incomplete
basis of the current thread here is stupid. Each has advantages and
disadvantages. The 100% solution is a very simple one:
Private m_db As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If (m_db Is Nothing) Then
Set m_db = CurrentDb
End If
Set CurrentDbC = m_db
End Property
Why is this the "CurrentDbC" property proc best? Because it is the
most generic and handles the most issues and problems. After all, a
global can be erased if someone hits the code reset button in VBE.
DBEngine(0)(0) might not be the current db. CurrentDb is expensive if
called repeatedly. And so on.
If you need a particular collection to be refreshed due to there being
any doubt that the object may be there, then you should refresh it.
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
Mark Johnson
wrote:
>This has got to be one of the 10 most ridiculous conversations I have
>ever seen.
There were two problems mentioned:
1) Speed difference between dbengine and currentdb, not being in
question, the question was whether it was something noticeable to a
viewer. I said that I noticed a 'sluggishness', maybe imperceptible,
but real nonetheless. The numbers reported, perhaps, because they were
so different, suggested it was worse that it was, maybe. But it was
noticeable, only if the currentdb was also set and freed, repeatedly,
if not in a loop, then in mostly uninterrupted calls in short order.
So to avoid that delay, you proposed, and others agreed, with a global
ref, once for all, using currentdb, correct? Below, you simply suggest
just a private ref, and using a property, which I think was suggested,
before, as well. If there is a problem, it would solve that problem.
2) Dbengine(wrk)(db) could suffer from inaccuracy in the presense of
so-called, 'wizards', and using db compacting. You were the first to
mention the problem, on UseNet, in fact, years ago. I proposed a
solution to deal with that directly, rather than manage the overhead
of a global ref. I understand that dynamically adding objects is not
so infrequent as the use of wizards? Even so, to update even the
local, below, not a global, wouldn't you still, nevertheless, also
need another function or property not just to see that the db is in
fact set, but that if any objects are added by vba, that it is
current, as well?
>2) Setting db to Nothing in Access when db is either DBEngine(0)(0) or
>CurrentDB will do the same thing as in any VBA reference variable --
>it will lower the reference count to the object by one, and if the ref
>count is 0 then it will destroy the object. Since the current database
>has a permanent reference internally in Access, this will not destroy
>the object and thus never have an effect.
The 'reference count' being more than one, means currentdb is used
locally, set to an object, but perhaps set also in that way in a
called procedure? So two or more db obects could be opened at once?
That is, not a global ref, but the local references you see described
in the documentation? You do need to free the ref by setting it to
NOTHING, is that right? if we were still talking a global variable,
here - or even the local you propose, below?
>4) The only "official" benefit that CurrentDb has over
>DBEngine.Workspaces(0).Databases (a.k.a. DBEngine(0)(0) is the fact
>that all of the collections are guaranteed to up to date.
This is what I didn't understand. So use of dbengine(wk)(db) does NOT
point to the current db, in its entirety, necessarily, but rather only
CurrentDb is "guaranteed", at the time it's set, or at least created,
to include all objects, and particularly those
dynamically/programatically created? If dbengine is that primary
object, and it necessarily includes all sub-objects, then how could an
object create by vba be 'hidden' from the primary object? I could
understand it not being reported to currentdb, because particularly
the global suggestion, previously (but even the local, below), is set
when the db is opened, before the objects are created in code. But
dbengine?
It might just seem such an obvious or minor point to you. But I don't
see how dbengine could not know about any object, created dynamically
or otherwise, since you have to follow the chain down to that object
from dbengine, and from nowhere else.
>to do internally in Access but is a bit harder to do from DAO -- I
>believe a function in DAO that had all collections up to date would be
>about 8 times slower then CurrentDB (which is itself over 5000 times
>slower than DBEngine(0)(0).
In the comparisons I ran, it only seemed twice as slow. I mean, that's
enough. Are you using 5000 as a qualititive tag, a metaphor, for it
being slower, just generally?
>Note that if you do not need collections
>up to date that this benefit is not too terribly important. It is
>terribly important in Access wizards, which create objects and then
>have to be able to count on them being present. It has proven
>important in other apps I have written, enough so that I have my own
>methods of getting the current db that will selectively refersh
>particular collections.
You refresh the collection to update, only selectively, part of that
stored collection created originally using currentdb? So that instead
of destroying, freeing memory, allocating for a brand new collection,
creating the collection, etc., you simply use that existing global db
object, and add - or delete - only what's changed?
If you wouldn't consider it proprietary, could you post the procedure
you use for that?
>5) One "non-official" benefit that CurrentDb has over DBEngine(0)(0)
>is some known cases where the latter is not pointing to the current
>database but is instead pointing to a wizard db. The
>DBEngine.Workspaces(0).Databases collection is one where if the
>current db is closed, as it is during a compact, and for some reason
>another db is considered open, then the current db may not be the
>zeroeth item in the collection anymore. I have never seen a problem
>with this in a production app *unless* the user was able to both run
>wizards and compact the db. But as an author of wizards, that I have
>written for both Trigeminal and Microsoft, I can state that this can
>be an important scenario as a *lot* of applications.
It's not so rare.
And the "for some reason". Might it just not be that during compacting
the current db is closed ('automation' of Access?) and forgotten as a
reference, and then reopened by an algorithm that's responsible for
adding new db references. If so, it would go to the end of the list,
not having been remembered?
>6) One official downside to CurrentDb over DBEngine(0)(0) is the perf
>hit, which can be over 5000 times slower. However, in the majority of
>cases this is not an issue since you simply need to not call it in a
>loop. You can use a single refcounted global like Access wizards do,
>or you can do something less official than that if you are a normal
>persom. But either way this is a problem that any reasonabl
>intelligent person can solve with a lot less thought than most
>problems (if they understand all of the issues).
>The 100% solution is a very simple one:
Mark Johnson
>>> No, it doesn't.
The context was his example of closing the local db variable and then,
in the next line, referring to the rs that was set using that. And I
wondered how in closing the db it didn't signal that those objects set
in reference to it shouldn't also be discarded. And then I read what
you wrote - that keeping the recordset 'alive' keeps . . .
CDB
thoroughgoing and competent statement.
We are thankful for a lifeline out of the dismal quagmire, MichKa.
Clive
"Michael (michka) Kaplan" <mic...@spamless.trigeminal.nospamcom> wrote in
message news:3e73...@news.microsoft.com...
David W. Fenton
wrote in <3e73...@news.microsoft.com>:
>1) Calling db.Close in Access when db is either DBEngine(0)(0) or
>CurrentDB is a *no-op*. It does nothing.
Depending on scope, it may clear unreleased resources being used by
children of the db variable that have not themselves been
explicitly closed. But it is only a cleanup in case things that
should have been cleaned up explicitly are not.
As it does no harm and can clear resources that otherwise might not
get released, I use it.
Michael (michka) Kaplan
> Depending on scope, it may clear unreleased resources being used by
> children of the db variable that have not themselves been
> explicitly closed. But it is only a cleanup in case things that
> should have been cleaned up explicitly are not.
Incorrect. This is the bug that was fixed in Access 95 and beyond.
Unless you are writing things in Access 2.0, then nothing will be
closed when you call Close because it will immediately understand that
it cannot close.
> As it does no harm and can clear resources that otherwise might not
> get released, I use it.
The former is true; the latter is not.
Look, do it if you are suspicious, but lets not spread FUD without
proof of random ref. situations in DAO.
Michael (michka) Kaplan
"Mark Johnson" <1023...@compuserve.com> wrote...
> So to avoid that delay, you proposed, and others agreed, with a
global
> ref, once for all, using currentdb, correct? Below, you simply
suggest
> just a private ref, and using a property, which I think was
suggested,
> before, as well. If there is a problem, it would solve that problem.
It DOES solve the problem, so scratch the IF there, guy. The reason it
solves the problwm is that you have a single CurrentDb call, ever.
Then if someone resets the code (invalidating the ref) then it gets
called again, ONCE. Solved your problem and a common additional
problem in one swell floop.
> 2) Dbengine(wrk)(db) could suffer from inaccuracy in the presense of
> so-called, 'wizards', and using db compacting. You were the first to
> mention the problem, on UseNet, in fact, years ago. I proposed a
> solution to deal with that directly, rather than manage the overhead
> of a global ref.
What the heck are you talking about? Can you truly look at the simple
proc I posted and claim that it has more "overhead" then the nightmare
proc you posted?
> I understand that dynamically adding objects is not
> so infrequent as the use of wizards? Even so, to update even the
> local, below, not a global, wouldn't you still, nevertheless, also
> need another function or property not just to see that the db is in
> fact set, but that if any objects are added by vba, that it is
> current, as well?
Huh? Your code does not do this, so to whatever extent one needs to
check if objects have been added, it will not be solved. If you (a)
care about an object being there and (b) have a good reason to believe
it may not be, then the Refresh method is your friend.
> The 'reference count' being more than one, means currentdb is used
> locally, set to an object, but perhaps set also in that way in a
> called procedure? So two or more db obects could be opened at once?
> That is, not a global ref, but the local references you see
described
> in the documentation? You do need to free the ref by setting it to
> NOTHING, is that right? if we were still talking a global variable,
> here - or even the local you propose, below?
NO, you do not ever need to set it to Nothing. EVER. This is done for
you when you exit the procedure.
Please re-read the above sentence at least 10 times. I am not joking.
> This is what I didn't understand. So use of dbengine(wk)(db) does
NOT
> point to the current db, in its entirety, necessarily, but rather
only
> CurrentDb is "guaranteed", at the time it's set, or at least
created,
> to include all objects, and particularly those
> dynamically/programatically created? If dbengine is that primary
> object, and it necessarily includes all sub-objects, then how could
an
> object create by vba be 'hidden' from the primary object? I could
> understand it not being reported to currentdb, because particularly
> the global suggestion, previously (but even the local, below), is
set
> when the db is opened, before the objects are created in code. But
> dbengine?
Sigh.... why do you make things so complicated? Please re-read your
paragraph above, perhaps you will see the parse errors.
There are many perf enhancements in DAO that were done starting in 3.0
to make sure that collections are not refreshed before they are used.
Thats all. Its simple. So keep it so:
1) Proc AA adds an index to table1
2) Proc AA calls Proc BB
3) Proc BB assumes that the new index is visible in the table
Well, the change that happened is that the assumption in #3 may be
invalid. Thats all.
> It might just seem such an obvious or minor point to you. But I
don't
> see how dbengine could not know about any object, created
dynamically
> or otherwise, since you have to follow the chain down to that object
> from dbengine, and from nowhere else.
See above.
> >to do internally in Access but is a bit harder to do from DAO -- I
> >believe a function in DAO that had all collections up to date would
be
> >about 8 times slower then CurrentDB (which is itself over 5000
times
> >slower than DBEngine(0)(0).
>
> In the comparisons I ran, it only seemed twice as slow. I mean,
that's
> enough. Are you using 5000 as a qualititive tag, a metaphor, for it
> being slower, just generally?
No, I mean for real. It is really that much slower. If you see only a
2x difference then you are doing something wrong.
> You refresh the collection to update, only selectively, part of that
> stored collection created originally using currentdb? So that
instead
> of destroying, freeing memory, allocating for a brand new
collection,
> creating the collection, etc., you simply use that existing global
db
> object, and add - or delete - only what's changed?
What the hell are you talking about?
If you want to make sure the TableDefs collection is complete, and you
have reason to believe it may not be, then you call
db.TableDefs.Refresh. Thats all. KEEP IT SIMPLE.
> If you wouldn't consider it proprietary, could you post the
procedure
> you use for that?
Use the one I posted. A complex procedure can wait for an actual
scenario, and you not understanding the simple case is not a scenario
suggesting the need for something harder.
> It's not so rare.
I never said it was.
> And the "for some reason". Might it just not be that during
compacting
> the current db is closed ('automation' of Access?) and forgotten as
a
> reference, and then reopened by an algorithm that's responsible for
> adding new db references. If so, it would go to the end of the list,
> not having been remembered?
No, that is not it, and it is not possible.
ACCESS does not open objects as DAO objects internally, and does not
have the issues that you refer to here, at all.
Mark Johnson
wrote:
>Sigh, some people never learn. :-(
Or like being rude, even.
>"Mark Johnson" <1023...@compuserve.com> wrote...
>> before, as well. If there is a problem, it would solve that problem.
>It DOES solve the problem, so scratch the IF there, guy.
If there is a problem, it would solve the problem. We're agreed - guy.
That was me agreeing . . with you.
>> 2) Dbengine(wrk)(db) could suffer from inaccuracy in the presense of
>> so-called, 'wizards', and using db compacting. You were the first to
>> mention the problem, on UseNet, in fact, years ago. I proposed a
>> solution to deal with that directly, rather than manage the overhead
>> of a global ref.
>What the heck are you talking about? Can you truly look at the simple
>proc I posted and claim that it has more "overhead" then the nightmare
>proc you posted?
What "nightmare proc", again? Didn't I ask you that, previously?
And, also, I had simply asked if you need to free up the reference,
even if local, as you have it. You've answered categorically, below.
And I also had asked if you had a routine to cover any updating to the
structure, since you suggested it would not be neceesary to free and
recreate it for any dynamic change in objects to the db.
>> I understand that dynamically adding objects is not
>> so infrequent as the use of wizards? Even so, to update even the
>> local, below, not a global, wouldn't you still, nevertheless, also
>> need another function or property not just to see that the db is in
>> fact set, but that if any objects are added by vba, that it is
>> current, as well?
>Huh? Your code does not do this
Doesn't have to. But if you have a collection, you've already said as
much. But you also added that, instead of freeing and recreating the
object, you simply modified it, selectively. I wondered what that
procedure looked like, or whatever lines of code.
>so to whatever extent one needs to
>check if objects have been added, it will not be solved.
Why would you need to do that with dbengine? That's what I don't
understand. How can you add an object, and NOT have dbengine, at some
level down, refer to it?
>> in the documentation? You do need to free the ref by setting it to
>> NOTHING, is that right? if we were still talking a global variable,
>> here - or even the local you propose, below?
>NO, you do not ever need to set it to Nothing. EVER. This is done for
>you when you exit the procedure.
But . . . that's perfectly fine. Okay. No problem with that. As you
say, below - read that 10 times. It's fine. No problem. So the local
db reference, in your property, created using currentdb, is just
simply freed, no harm done, when you close the db? You don't have to
worry about setting it to Nothing. And that makes things easier.
Are you suggesting that all those lines where you set the object to
nothing, as shown in the Access help files for ex, in all subs and
procedures, are simply redundant? and shouldn't have been mentioned in
the documentation, for any good reason?
>Please re-read the above sentence at least 10 times. I am not joking.
Neither am I. Why would I disagree? It's perfectly fine with me. The
online documentation, then, is what is confusing everyone.
>> This is what I didn't understand. So use of dbengine(wk)(db) does NOT
>> point to the current db, in its entirety, necessarily, but rather only
>> CurrentDb is "guaranteed", at the time it's set, or at least created,
>> to include all objects, and particularly those dynamically/programatically
>> created? If dbengine is that primary object, and it necessarily
>> includes all sub-objects, then how could an object create by vba
>> be 'hidden' from the primary object? I could understand it not
>> being reported to currentdb, because particularly the global
>> suggestion, previously (but even the local, below), is set
>> when the db is opened, before the objects are created in code. But
>> dbengine?
>Sigh.... why do you make things so complicated? Please re-read your
>paragraph above, perhaps you will see the parse errors.
C'mon. There's nothing but question marks there. What you said seemed
very confusing. I wanted a clarification. Those are questions, not
statements.
Anyway - the answer:
>There are many perf enhancements in DAO that were done starting in 3.0
>to make sure that collections are not refreshed before they are used.
>Thats all. Its simple. So keep it so:
>1) Proc AA adds an index to table1
>2) Proc AA calls Proc BB
>3) Proc BB assumes that the new index is visible in the table
>Well, the change that happened is that the assumption in #3 may be
>invalid. Thats all.
Okay.
Using collection.append, I assume you mean? And this isn't a case of
Append causing the error, obviously. In other words, if the object
being appended is complete, then "addition of a new object" does not
occur "immediately"? I'm just asking.
>> >about 8 times slower then CurrentDB (which is itself over 5000
>> >slower than DBEngine(0)(0).
>> In the comparisons I ran, it only seemed twice as slow. I mean, that's
>> enough. Are you using 5000 as a qualititive tag, a metaphor, for it
>> being slower, just generally?
>No, I mean for real. It is really that much slower. If you see only a
>2x difference then you are doing something wrong.
That's the only difference I saw. Setting and freeing repeatedly, each
time through the loop, couple of hundred times maybe, was about twice
as slow as simply setting the dbengine pointer; certainly nowhere near
a two and half orders of magnitude slower. And I should forget the set
to Nothing, even at that, correct? it's redundant?
>> You refresh the collection to update, only selectively, part of that
>> stored collection created originally using currentdb? So that
>instead of destroying, freeing memory, allocating for a brand new
>collection, creating the collection, etc., you simply use that existing
>global db object, and add - or delete - only what's changed?
>What the hell are you talking about?
"I have my own methods of getting the current db that will selectively
refersh particular collections."
Since the context was the currentdb collection, you suggested
something of your "own methods" to refresh only parts of the
collection. And the answer is simply:
>If you want to make sure the TableDefs collection is complete, and you
>have reason to believe it may not be, then you call
>db.TableDefs.Refresh. Thats all. KEEP IT SIMPLE.
Yes, that is simple.
How is that "my own method"? It suggested something you had created as
a workaround, of some sort. You had mentioned it. And that's all I was
asking, just in good faith. What were _you_ talking about, in other
words, if you like.
>Use the one I posted. A complex procedure can wait for an actual
>scenario, and you not understanding the simple case is not a scenario
>suggesting the need for something harder.
When you wrote - what are you talking about - you knew, then? And you
just had to blurt that out? Again, I don't understand. I really don't.
If you know of a "complex procedure", of your "own methods", why the
bluster, at all? If you don't want to share, that's fine. Certainly no
one expects or demands that. I was just simply asking, a very innocent
question.
>> It's not so rare.
>I never said it was.
No, you're saying it's not so rare. But some in considering this whole
thing have, I believe, suggested it's too rare to worry about. That
was the reference. You disagree with them. On this, I really can't
imagine why I'd disagree with . . you. Again, agreement.
>> reference, and then reopened by an algorithm that's responsible for
>> adding new db references. If so, it would go to the end of the list,
>> not having been remembered?
> No, that is not it, and it is not possible.
>ACCESS does not open objects as DAO objects internally, and does not
>have the issues that you refer to here, at all.
That was to "that the object would for some reason think . . ."
But I had suggested just generally that however referred to, DAO, ADO,
or otherwise, that reference was not merely discarded when compacting
the db, but in being completely discarded would be treated as the next
object, or reference, or whatever it was, before, when read back in by
such 'automation'? But that can't be, correct? I was just wondering
aloud, when you said - for some reason.
Mark Johnson
wrote:
>Look, do it if you are suspicious, but lets not spread FUD without
>proof of random ref. situations in DAO.
'Michka'. Do you have a site of your own that might contain some
helpful reminders, procedures, downloads for people using MS Access?
Brendan Reynolds
"Michael (michka) Kaplan" <mic...@spamless.trigeminal.nospamcom> wrote in
<snip>
> NO, you do not ever need to set it to Nothing. EVER. This is done for
> you when you exit the procedure.
Thanks for all the info, Michael. After all this, I find I'm left with just
one question (well, for now, anyway! :-)
Just about everything I've read has always said that while object references
will be destroyed when they go out of scope, it is 'good programming
practise' to explicitly destroy all object references when you're finished
with them.
OK, I've always done that, and it's no big deal - just a few lines of code.
But as a result of this conversation I find myself revisiting the question -
why exactly do we do this? What is it that makes this 'good programming
practise'? Is it a myth? Is it a hold-over from the past? Or are there
still potential problems with some object references, just not this
particular one?
--
Brendan Reynolds
bren...@indigo.ie
Michael (michka) Kaplan
languages have objects that are designed to lower refcounts and/or
destroy themselves automatically, and in those languages one could
leak memory.
VB/VBA/VBScript is not one of them, though.
Now if tcan be useful to free up an object if you have more to do in a
function and want the resources freed right away, before the end of
the proc. It is just the idea of freeing up locals at the end of the
proc that makes no sense....
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Brendan Reynolds" <bren...@indigo.ie> wrote in message
news:wbida.3137$pK2....@news.indigo.ie...
Michael (michka) Kaplan
> What "nightmare proc", again? Didn't I ask you that, previously?
The proc you posted is comparatively nightmarish. It is much more
complex, much more prone to bugs and problems due to complexity, and
much less necessary.
> And I also had asked if you had a routine to cover any updating to
the
> structure, since you suggested it would not be neceesary to free and
> recreate it for any dynamic change in objects to the db.
Correct -- you would call the Refresh method on any collection if you
were not sure whether it is up to date.
> Doesn't have to. But if you have a collection, you've already said
as
> much. But you also added that, instead of freeing and recreating the
> object, you simply modified it, selectively. I wondered what that
> procedure looked like, or whatever lines of code.
A call to the Refresh method.
> Why would you need to do that with dbengine? That's what I don't
> understand. How can you add an object, and NOT have dbengine, at
some
> level down, refer to it?
Who was talking about DBEngine? I am talking about Database objects --
note that DBEngine(0)(0) is a Database.
> Are you suggesting that all those lines where you set the object to
> nothing, as shown in the Access help files for ex, in all subs and
> procedures, are simply redundant? and shouldn't have been mentioned
in
> the documentation, for any good reason?
It is redudant and it is not needed.
> C'mon. There's nothing but question marks there. What you said
seemed
> very confusing. I wanted a clarification. Those are questions, not
> statements.
Confusing questions, guy.
> Using collection.append, I assume you mean? And this isn't a case of
> Append causing the error, obviously. In other words, if the object
> being appended is complete, then "addition of a new object" does not
> occur "immediately"? I'm just asking.
No, I mean any additions at all -- other users adding objects or
indexes or fields. Doing something through a wizard or through ADO or
ODBC or another process on the machine (another copy of the app?).
> >> >about 8 times slower then CurrentDB (which is itself over 5000
> >> >slower than DBEngine(0)(0).
>
> >> In the comparisons I ran, it only seemed twice as slow. I mean,
that's
> >> enough. Are you using 5000 as a qualititive tag, a metaphor, for
it
> >> being slower, just generally?
>
> >No, I mean for real. It is really that much slower. If you see only
a
> >2x difference then you are doing something wrong.
>
> That's the only difference I saw. Setting and freeing repeatedly,
each
> time through the loop, couple of hundred times maybe, was about
twice
> as slow as simply setting the dbengine pointer; certainly nowhere
near
> a two and half orders of magnitude slower. And I should forget the
set
> to Nothing, even at that, correct? it's redundant?
Correct. There is something wrong with your code. Use the following
for comparison:
Declare Function GetTickCount Lib "kernel32" () As Long
Private m_db As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If (m_db Is Nothing) Then
Set m_db = CurrentDb
End If
Set CurrentDbC = m_db
End Property
Public Function DBEngineWithRefresh() As DAO.Database
Dim db As DAO.Database
Set db = DBEngine(0)(0)
db.TableDefs.Refresh
db.QueryDefs.Refresh
db.Relations.Refresh
db.Properties.Refresh
db.Containers.Refresh
Set DBEngineWithRefresh = db
End Function
Public Sub TestItNow()
Dim db As DAO.Database
Dim lTime As Long
Dim i As Long
Const TIMES = 10000
lTime = GetTickCount()
For i = 1 To TIMES
Set db = CurrentDb
Next i
Debug.Print "CurrentDb took " & _
(GetTickCount() - lTime) & " ms."
lTime = GetTickCount()
For i = 1 To TIMES
Set db = CurrentDbC
Next i
Debug.Print "CurrentDbC took " & _
(GetTickCount() - lTime) & " ms."
lTime = GetTickCount()
For i = 1 To TIMES
Set db = DBEngine(0)(0)
Next i
Debug.Print "DBEngine(0)(0) took " & _
(GetTickCount() - lTime) & " ms."
lTime = GetTickCount()
For i = 1 To TIMES
Set db = DBEngineWithRefresh()
Next i
Debug.Print "DBEngineWithRefresh took " & _
(GetTickCount() - lTime) & " ms."
End Sub
RESULTS:
CurrentDb took 4587 ms.
CurrentDbC took 10 ms.
DBEngine(0)(0) took 30 ms.
DBEngineWithRefresh took 40198 ms.
> How is that "my own method"? It suggested something you had created
as
> a workaround, of some sort. You had mentioned it. And that's all I
was
> asking, just in good faith. What were _you_ talking about, in other
> words, if you like.
I was referring to more complex methods that can be used if one is
doing complex things that are much less common. They detract from this
thread and we should drop them here as they are not relevant. I was
merely pointing out that for more complex scenarios one can do more
complex things.
> But I had suggested just generally that however referred to, DAO,
ADO,
> or otherwise, that reference was not merely discarded when
compacting
> the db, but in being completely discarded would be treated as the
next
> object, or reference, or whatever it was, before, when read back in
by
> such 'automation'? But that can't be, correct? I was just wondering
> aloud, when you said - for some reason.
Since I still do not understand what you mean by this, lets assume the
answer is NO.
Trevor Best
<wbida.3137$pK2....@news.indigo.ie>, "Brendan Reynolds"
<bren...@indigo.ie> wrote:
If you trust Microsoft to clean up after you then don't do it,
otherwise do it :-)
--
Often statistics are used as a drunken man uses lampposts -
for support rather than illumination.
(replace sithlord with trevor for email)
Brendan Reynolds
--
Brendan Reynolds
bren...@indigo.ie
"Michael (michka) Kaplan" <mic...@spamless.trigeminal.nospamcom> wrote in
message news:3e7611b5$1...@news.microsoft.com...
Bri
Let me first of all say thank you for clarifying this issue (at least it
clarifed things for me).
This one section of your response has me wondering if this method should be used
for all global variables? I have some global string variables that I setup in
the Open event of the Main Menu Form that are derived from values in a Constants
Table (eg. Path to files of interest to the application). Anyway, my use of
these global variables is not the question, rather, it is if I should/could use
your Property Get method to set/reset these variables as well?
Thanks,
Bri
"Michael (michka) Kaplan" wrote:
<snip>
> 7) Extensive argument over which method is better on the incomplete
> basis of the current thread here is stupid. Each has advantages and
> disadvantages. The 100% solution is a very simple one:
>
> Private m_db As DAO.Database
>
> Public Property Get CurrentDbC() As DAO.Database
> If (m_db Is Nothing) Then
> Set m_db = CurrentDb
> End If
>
> Set CurrentDbC = m_db
> End Property
<snip>
Douglas J. Steele
Invaluable.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Mark Johnson" <1023...@compuserve.com> wrote in message
news:emab7v0b55n0ua95m...@4ax.com...
David W. Fenton
>"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
>wrote:
>
>>Sigh, some people never learn. :-(
>
>Or like being rude, even.
Perhaps the fact that you keep getting the same reactions from
different people should clue you into something about yourself.
Lyle Fairfield
news:9341D34E4df...@24.168.128.78:
> 1023...@compuserve.com (Mark Johnson) wrote in
><8g1b7vg82eogd9tn8...@4ax.com>:
>
>>"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
>>wrote:
>>
>>>Sigh, some people never learn. :-(
>>
>>Or like being rude, even.
>
> Perhaps the fact that you keep getting the same reactions from
> different people should clue you into something about yourself.
I rather like Michka of late. Putting the past behind, I have to smarten up
and accept and appreciate him for what he is: a very bright and
knowledgeable Access guru, who expresses himself in an incisive but
entirely acceptable way in keeping with his ability.
I can change my position and my appreciation of someone.
--
Lyle
Mark Johnson
>1023...@compuserve.com (Mark Johnson) wrote in
><8g1b7vg82eogd9tn8...@4ax.com>:
>>"Michael \(michka\) Kaplan" <mic...@spamless.trigeminal.nospamcom>
>>wrote:
>>>Sigh, some people never learn. :-(
>>Or like being rude, even.
>Perhaps the fact that you keep getting the same reactions from
>different people should clue you into something about yourself.
I disagreed with you, and gave a host of reasons for it. It was for
those reasons that I disagreed with you. It was when you became
specific that you found what you thought would work, didn't work. I
think some of what you said on the subject of the web, and other
things, probably would equally not stand up to scrutiny. And that's
fine. And we can disagree. But if you want to be condescending, or
even same of 'michka', you better expect that person to point it out
to you. It would be supremely foolish to expect anything less.
Again, I have no reason to disagree with 'Michka', just for example,
on this notion of not setting an object to Nothing, contrary to the
documention. He says it's automatically done for you when you exit the
procedure. And I'm glad I know that, now. I didn't, before, because I
had followed the online documentation, instead.
As Lyle just pointed out: "have to smarten up and accept and
appreciate him for what he is: a very bright and knowledgeable Access
guru", speaking of 'michka'. But "who expresses himself in an . . .
entirely acceptable way", which isn't always acceptable. Oh, well.
Can't agree on everything, all the time, with any one, perhaps. Such
is life.
Mark Johnson
wrote:
>"Mark Johnson" <1023...@compuserve.com> wrote...
>> What "nightmare proc", again? Didn't I ask you that, previously?
>The proc you posted is comparatively nightmarish. It is much more
>complex, much more prone to bugs
?
>and problems due to complexity, and much less necessary.
?
As you like to say - what are you talking about? Again, a two line
cover function. That's not 'complexity'. It's a two line cover
function. Maybe in some way, it's wrong. I don't know. But 'complex'?
Maybe you meant something else.
>> Why would you need to do that with dbengine? That's what I don't
>> understand. How can you add an object, and NOT have dbengine, at
>> some level down, refer to it?
>Who was talking about DBEngine?
That was the context. But you were thinking of:
>I am talking about Database objects
>note that DBEngine(0)(0) is a Database.
And any objects dynamically added to one of those would, obviously, be
pointed to, at some point, by dbengine, new database or otherwise. It
would have to. Currentdb, conversely, being a snapshot as it were, has
to be updated/refreshed.
> Const TIMES = 10000
>End Sub
>RESULTS:
Just wanted to say, and just the obvious, that's a frightening
difference. Worst case, three and half orders of magnitude - we're
starting to talk distances between stars, here. It makes you wonder
how anyone could recommend currentdb, at all, without either using
globally and maintained, or used as a property call, as you suggest;
but at any rate, not constantly opened when needed.
I wonder if there's an estimated time function for currentdb,
somewhere in a M$ archive, somewhere? Maybe some of the collections
would have to be factored differently. But it might be interesting to
see such a formula.
Someone else, here, posted that M$ is even strongly discouraging the
use of dbengine with the latest release of Access. What they propose -
worst case, true - is only three and half orders of magnitude slower
than the alternative.
Mark Johnson
>I rather like Michka of late. Putting the past behind, I have to smarten up
>and accept and appreciate him for what he is: a very bright and
>knowledgeable Access guru
I thought he was the 'expert's expert, here. He knows stuff, and how
to do stuff, right, from experience coupled with insider experience.
And it's no sort of license to lash out without expecting someone to
say - guy, don't do that. Etc.
>who expresses himself in an incisive but
>entirely acceptable way in keeping with his ability.
And be a friend. And don't encourage bad behavior. There's that, too.
>I can change my position and my appreciation of someone.
1000 times, etc., yes.
Speaking of those with experience in Access, you know, I wonder about
Getz, and the others, who I see used to post here. I don't see them,
anymore. For instance, Getz has in that 'bible' for A2K that you don't
use the so-called, 'bang', the exclamation point. You use quotes and
parentheses to refer to collections. He says the 'bang' is just
needless overhead and that the quote/paren is sort of the native form
to which 'bang's are converted, anyway. I seem to recall a recent
couple of threads where 'bang' was recommended, particularly over dot
which was causing problems in the latest release? And it pops to mind
because there was no Getz, Litwin, et al to defend the contrary
position. Just sort of noticeable by their absence, perhaps. They used
to post here, correct?
Lyle Fairfield
Many of the more capable posters of a few years ago never contribute,
or contribute infrequently, today.
Consensus here used to be a good guideline towards best practice.
Today it is not. What appears to be consensus is often the collective
opinion of a small group of regular posters who respond to and support
each other in a long thread. Challenging this group can have
unpleasant consequences.
As for bang and dot, in the case of a Recordset, I posted some
comparisions a few years ago and concluded the undocumented .Collect,
eg .Collect(Index) ->.Collect("fldDateofTransaction") or .Collect(3),
property of the Recordset was fastest for obtaining the value of one
of a recordset's fields.
Dimitri Furman pointed out that declaring a field enabled a faster way
as in
Dim fldDOT as DAO.Field (or ADODB)
'open the recordset
Set fldDOT = rcs.Fields("fldDateofTransaction")
then use
fldDOT.Value or just fldDOT to get the value.
Today I use Collect for a one time getting of a field's value
but always Declare and set a Field Object for any repeated or looping
getting of a field's value.
This resulted in my learning something and for me is an example of how
the group can work well. But I haven't seen much of that recently.
Mark Johnson <1023...@compuserve.com> wrote in message news:<so5d7v8fi71ihuc6k...@4ax.com>...
Michael (michka) Kaplan
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Bri" <n...@here.com> wrote in message
news:3E763D88...@here.com...
Michael (michka) Kaplan
> >RESULTS:
>
> >CurrentDb took 4587 ms.
> >CurrentDbC took 10 ms.
> >DBEngine(0)(0) took 30 ms.
> >DBEngineWithRefresh took 40198 ms.
>
> Just wanted to say, and just the obvious, that's a frightening
> difference. Worst case, three and half orders of magnitude - we're
> starting to talk distances between stars, here. It makes you wonder
> how anyone could recommend currentdb, at all, without either using
> globally and maintained, or used as a property call, as you suggest;
> but at any rate, not constantly opened when needed.
Well, note that the solution I give is even faster than
DBEngine(0)(0) -- due to the fact that it does not have to go through
the colleciton hierarchy -- although both are blazingly fast.
But in most apps it does not really make a difference, to tell you the
truth.
Let me make one (confusing?) suggestion, that often helps me in
add-ins:
Private m_dbCur As DAO.Database
Private m_dbCode As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If (m_dbCur Is Nothing) Then
Set m_dbCur = CurrentDb
End If
Set CurrentDbC = m_dbCur
End Property
Public Property Get CodeDbC() As DAO.Database
If (m_dbCode Is Nothing) Then
Set m_dbCode = CodeDb
End If
Set CodeDbC = m_dbCode
End Property
Note that in a normal db they are the same and you would only need
one. But in a library or wizard db you may need either or both.
The solution is tremendously faster, even though it seldom makes a
difference.
> I wonder if there's an estimated time function for currentdb,
> somewhere in a M$ archive, somewhere?
Please do me one favor, its a minor thing, but when people muck with a
name I just feel a lot less like being helpful. Its MS, not M$.
Thanks.... :-)
> Maybe some of the collections
> would have to be factored differently. But it might be interesting
to
> see such a formula.
Not sure what you would be hoping for here, exactly. The test function
I gave gives times, what kind of function did you have in mind?
> Someone else, here, posted that M$ is even strongly discouraging the
> use of dbengine with the latest release of Access. What they
propose -
> worst case, true - is only three and half orders of magnitude slower
> than the alternative.
Wedll, the bug got slighly worse in later versions -- I do not know if
its worse in 2002 then it was in 2000 (I suppose you could blame me if
its the latter, someone else is its the former! <grin>). Again, it
seldom makes a difference, anyway.
Caesar_Ancheta
co-presented by Getz [NOT MS-Access - oriented]. I can only conclude
that he jumped on the .NET bandwagon over 3 years ago.
Draw your own conclusions.
lyle...@yahoo.com (Lyle Fairfield) wrote in message news:<42d36dfd.03031...@posting.google.com>...
Michael (michka) Kaplan
while still in VBA' type stuff, as well as migration strategies.
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"Caesar_Ancheta" <caesar_...@yahoo.com> wrote in message
news:5d34f091.03031...@posting.google.com...
Mark Johnson
wrote:
>Please do me one favor, its a minor thing, but when people muck with a
>name I just feel a lot less like being helpful. Its MS, not M$.
>Thanks.... :-)
Thanks for the platform:
You see, that's why they were despised by many 'valley' fims during
the 'dot.com' days. That's why they were routinely credited with
unfair, unethical and predatory practices. And that's also the whole
point of the company, and that one, particularly. In fact, apart from
yourself, I doubt anyone at M$ would quarrel with that emphasis. The
question isn't about a money-making enterprise making money, but how
they go about it. Gates and Balmer and others would argue that people
just don't understand the 'fairness', as they see it, from their point
of view. But what seems to be their emphasis, from the outside looking
in - or otherwise?
How about just agreeing to disagree? I don't think we'll agree, on
that. But even if you work, or worked, for the firm, it doesn't demand
that you forfeit your right to free speech, or to hold your own
opinion of the firm, particularly if it's critical. I've worked for
various firms of which I was critical, particularly of HQ operations
and schemes; not just 'the boss', and that. Unlikely as it would seem,
if I ever went to work for M$, I doubt my opinion on its past would
change. And if I couldn't say what needed to be said, particularly
inside the 'citadel', I would be very upset. And you could understand
that, right? and it being a quite reasonable response?
There is this notion out there of a brain-washed M$ cult - called,
'Microsofties'. Have you heard the term? I would think you'd rather
object to that. Because what that suggests isn't mere greed, but
gullibility and incompetence. The cultist won't criticize the 'supreme
leader', but is in only in awe or fear of him, or will go to war; as
we're seeing. It's not 'his problem', but the guy's down the corridor.
There's no sense of responsibility, 'ownership', because he's told his
work product belongs to some else. The emperor always gets to go
around naked, until everything crashes around him. No one would speak
their mind. You can't have it both ways - a cult, and competitiveness,
at the same time. You can lock in 'channels' and markets. But the
customers will complain about product, as they should.
Frankly, I like the M$ products because I don't use an alternative.
LINUX has always been disappointing. OS2 was beaten out as the 'next
big thing', in the early 90s by the surprizingly good Win3.1; though
IBM still clings to all things not M$. Without more competition, I
guess M$ looks pretty good. To criticize it, in that vacuum - which
I'm telling you is there - is to say there are an absence of rainbows,
when no one has ever seen a rainbow.
So . . thanks for the platform.
Michael (michka) Kaplan
<snip self-righteous diatribe>
Since you did not see fit to do the one minor thing I requested, I
will not answer further questions.
The bottom line is that if you so say "fuck off" about a company when
someone from the company is helping you. Not if you want that help to
continue.
And you ignoring such a minor request, to the point of trying to
justify your right to continue saying something that offends the
person you are conversing with, has the distinct "feel" of a fuck off.
CDB
time on and plonked him.
If our Don wanted to continue goading people, he could develop a more
literate persona - just like this Mark Johnson.
Clive
"Michael (michka) Kaplan" <mic...@spamless.trigeminal.nospamcom> wrote in
message news:3e77c57c$1...@news.microsoft.com...
CDB
Clive
"Lyle Fairfield" <lyle...@yahoo.com> wrote in message
news:42d36dfd.03031...@posting.google.com...
CDB
I changed to using a class module, and have been pleased, but...
does not this approach rely on an implied instantiation of the class? Is it
safe? Or have I missed part of the implementation?
Clive
"Michael (michka) Kaplan" <mic...@spamless.trigeminal.nospamcom> wrote in
message news:3e772e05$1...@news.microsoft.com...
Mark Johnson
>MichKa, I decided some days ago that Mark Johnson was not worth spending
>time on and plonked him.
>
>If our Don wanted to continue goading people, he could develop a more
>literate persona - just like this Mark Johnson.
In other words, I don't have an opinion, because it's something always
to be ignored and ridiculed. And yours is just such an overdone method
of not addressing matters. If it makes you happy to put people down
like that, there's nothing I can say. But you convince such a small
group of desperate people with that approach. I'm just telling you -
it doesn't work. Free advice. Instead . . deal with issues, even with
people who disagree with you. You can always agree to disagree.
Mark Johnson
>Sadly, you speak truly.
But you love it, CDB. Be honest. YOU . . were the one who just went
out of his way to put down anything and everything I might write,
here, just because something about it bothered you. You are one of
those guilty precisely of that which Lyle mentioned. So . .
Mark Johnson
wrote:
>"Mark Johnson" <1023...@compuserve.com> wrote...
><snip self-righteous diatribe>
Complaints about M$ behavior and past practices. Many agree. Maybe
some don't. Such is the nature of free speech.
>Since you did not see fit to do the one minor thing I requested
Since you did not bother to consider the complaint I explained, in
detail:
>I will not answer further questions.
Because one person dared to criticize the practices of M$ that brought
it into disrepute in the industry, and before the Justice Dept.
>The bottom line is that if you so say "fuck off" about a company
But _I_ didn't. You just did. That's you, saying that, to me and
everyone else, here. Don't project. Don't put that on me. That's you.
That's all you.
What I wrote was that M$ engaged in bad practices, that have been
historically criticized. I suggested that, perhaps, the emperor isn't
being told what's going on. How could that be?
This is also what I wrote:
There is this notion out there of a brain-washed M$ cult - called,
'Microsofties'. Have you heard the term? I would think you'd rather
object to that. Because what that suggests isn't mere greed, but
gullibility and incompetence. The cultist won't criticize the 'supreme
leader', but is in only in awe or fear of him, or will go to war; as
we're seeing. It's not 'his problem', but the guy's down the corridor.
There's no sense of responsibility, 'ownership', because he's told his
work product belongs to some else. The emperor always gets to go
around naked, until everything crashes around him. No one would speak
their mind. You can't have it both ways - a cult, and competitiveness,
at the same time. You can lock in 'channels' and markets. But the
customers will complain about product, as they should.
>And you ignoring such a minor request
I'm critical of the company, after all. You could be, too, even just
in part. Most people are. Doesn't mean they don't use M$ products.
Everyone here, does. This is a ng dedicated to a M$ product, after
all.
>to the point of trying to
>justify your right to continue saying something that offends the
>person you are conversing with, has the distinct "feel" of a fuck off.
And that's what you're doing. What I wrote was that I was critical of
M$. I don't agree with much of what they've done. I'm disappointed
with much of what they produce. Many people are. And I explained all
that, taking a number of paragraphs to do so - which you seem not even
to have bothered to read.
I don't know what it is you imagine, in all this. It's very bad
behavior. It's very juvenile. Maybe that's how you lived your life, to
date. I don't know. But you're a big boy. And this is only UseNet. You
post. You don't. It's up to you. And if you want to curse this ng, or
the least criticism of M$, I'd tell you not to - but you're going to
do what you're going to do, regardless of my advice.
J Ohlson
> "CDB" <al...@delete.wave.co.nz> wrote:
>
>>MichKa, I decided some days ago that Mark Johnson was not worth spending
>>time on and plonked him.
>>
>>If our Don wanted to continue goading people, he could develop a more
>>literate persona - just like this Mark Johnson.
>
- Marks reply to CDB snipped -
Who are you talking to, Mark? He plonked you - means he put you in his
killfile. He won't see your reply, unless someone quotes it. And I didn't.
John
J Ohlson
> Thanks for the platform:
<Sigh>
Nobody gave you a "platform". This isn't the ng for general MS-bashing.
Sometimes MS is critizised here, in a specific way, when it's on topic and
relevant. In what you wrote, there's no edge because it's all in general,
it's off topic and it could have been summarized in one or two sentences.
Ask yourself, if you were not just talking to yourself here, but addressing
Michael Kaplan and this newsgroup, what there is for him or us to reply to.
"No, Mark, I disagree with you, the customers will not complain about the
product" or "Yes, you're absolutely right, the way MS go about making money
isn't fair".
I think the platform you are seizing here in cdma, is the platform of
arguing for the argument's sake, and of talking to yourself in long,
untrimmed posts that lead nowhere. That's quite a hopeless position, and I
don't understand why you're so eager to get that position. For the rest of
us who will have to spend time skimming throught the crap, the bland
statements, the confused thoughts and the desinformation, it is not
constructive.
Many people here have "dealt with it", as you call it, from polite hints to
being frank and rude, but you won't listen. I see no hope for a change to
the better, so
- plonk -
rkc
"CDB" <al...@delete.wave.co.nz> wrote in message news:b590ne$5vt$1...@news.wave.co.nz...
> MichKa, I decided some days ago that Mark Johnson was not worth spending
> time on and plonked him.
>
> If our Don wanted to continue goading people, he could develop a more
> literate persona - just like this Mark Johnson.
If there is one thing Don has never been, it is less literate than some of this
thread.
Mark Johnson
>Mark Johnson <1023...@compuserve.com> wrote in
>news:u6bf7v4aq85p35078...@4ax.com:
>> Thanks for the platform:
><Sigh>
>Nobody gave you a "platform".
Sigh - that's my word, here. Some people just don't handle criticism
all that well. It's so needless.
This is annoying, having to do this. I'm forced to reply, in this
threadlet showing some surprizingly 'raw' reaction to a legitimate
criticism of M$ practices, and only just in passing. Here's how it
read:
------------------------
I wonder if there's an estimated time function for currentdb,
somewhere in a M$ archive, somewhere? Maybe some of the collections
would have to be factored differently. But it might be interesting to
see such a formula.
Someone else, here, posted that M$ is even strongly discouraging the
use of dbengine with the latest release of Access. What they propose -
worst case, true - is only three and half orders of magnitude slower
than the alternative.
------------------------
Funny, perhaps. But the question is innocent, enough. I thought he'd
reply with some info on such an estimated time function, maybe. I
thought it might be interesting, at any rate, particularly as you see
so few metrics, generally, in this area.
Instead, this was part of the reply:
------------------------
Please do me one favor, its a minor thing, but when people muck with a
name I just feel a lot less like being helpful. Its MS, not M$.
Thanks.... :-)
------------------------
Sigh, again. And to which I replied to the implied threat, over this
"minor thing":
------------------------
Thanks for the platform:
You see, that's why they were despised by many 'valley' fims during
the 'dot.com' days. That's why they were routinely credited with
unfair, unethical and predatory practices.
------------------------
And I added, among other things:
------------------------
Unlikely as it would seem,
if I ever went to work for M$, I doubt my opinion on its past would
change. And if I couldn't say what needed to be said, particularly
inside the 'citadel', I would be very upset. And you could understand
that, right? and it being a quite reasonable response?
-------------------------
And the reply to that:
-------------------------
<snip self-righteous diatribe>
-------------------------
And which was something I'd hoped wasn't true in his case, as I had
described in that message which so offended him. He proved me right.
But so what? And to which he added:
-------------------------
The bottom line is that if you so say "fuck off" about a company
-------------------------
So. I clearly said nothing of the sort. I suggested, rather, that was
even a bit of projection, on his part, and that such was what he
himself was trying to do with regard to myself and others on this very
ng.
>This isn't the ng for general MS-bashing.
And find me somebody who is. I'm certainly not. I use M$ products, and
much more than just Access; as most everyone else, here. But I don't
agree with everything M$ has done. I made that clear.
>Sometimes MS is critizised here, in a specific way, when it's on topic and
>relevant.
It was. He made an issue of it. You continue to do so. I replied as
appropriate. See above. And where are all the 'specifics', below?
I must have missed them in your little screed.
>I think the platform you are seizing here in cdma, is the platform of
>arguing for the argument's sake, and of talking to yourself in long,
>untrimmed posts that lead nowhere.
It just seems to me that you are trying to offer some criticism, that
isn't supported by those messages.
>That's quite a hopeless position, and I
>don't understand why you're so eager to get that position. For the rest of
>us who will have to spend time skimming throught the crap, the bland
>statements, the confused thoughts and the desinformation, it is not
>constructive.
What confused thoughts and disinformation? You mention specifics. And
then you drop the ball.
What do you mean, specifically? Could you possibly have meant
anything? at all.
>Many people here have "dealt with it", as you call it, from polite hints to
>being frank and rude, but you won't listen. I see no hope for a change to
>the better, so
>- plonk -
Goodbye, then. And I really hope your approach to this kind of thing
proves more successful, otherwise.
Mark Johnson
If it was 'michka', I think he fled; which is a crying shame. I
understand he really was and is the expert's expert. For myself,
constructive criticism never hurts. One can only learn. I believe
that.
Michael (michka) Kaplan
either way....
--
MichKa [MS]
This posting is provided "AS IS" with
no warranties, and confers no rights.
"CDB" <al...@delete.wave.co.nz> wrote in message
news:b590ng$5vt$3...@news.wave.co.nz...