Create one function for all DAO Recordset connections

[David W. Fenton]

Of course, it does. But I think it's not a matter for the OP in
the first line.

The more the function is going to be used, the more it needs to be
robust and efficient.

This is the normal behavior.

Yes, of course it's the normal behavior, but you have to know that
even if your variable/function/property is initialized with
CurrentDB it needs to be refreshed if you're changing collections.
But a new variable assignment from CurrentDB() will *not* need to
have the collections refreshed, because you're getting an entirely
new instance, with already-refreshed collections.

It's just if you're using one set *before* the collections were
changed that you need to refresh, just as you would with a db
variable initialized with DBEngine(0)(0).

That's the problem of CurrentDb. It changes, but not that much:

Dim db1 As DAO.Database
Dim db2 As DAO.Database

Set db1 = CurrentDb
Set db2 = CurrentDb

If db1 Is db2 Then
MsgBox "equal"
Else
MsgBox "NOT equal"
End If

That's not a difference in the database pointed to by CurrentDB --
it's only a difference in the *memory address* of the pointer to it.

And that is *irrelevant*.

Again, there is never any reason to us CurrentDB() in a loop, as the
database it refers to can never be different from one iteration of
the loop to the next. Thus, there is no cumulative performance
penalty in using CurrentDB() over DBEngine(0)(0) that makes a
difference in real-world application programming. It only makes a
difference if you set up an artificial structure that should never
happen in a well-designed application.

 

[Alex Dybenko]

Hi David,

Again, there is never any reason to us CurrentDB() in a loop, as the
database it refers to can never be different from one iteration of
the loop to the next. Thus, there is no cumulative performance
penalty in using CurrentDB() over DBEngine(0)(0) that makes a
difference in real-world application programming. It only makes a
difference if you set up an artificial structure that should never
happen in a well-designed application.

can't agree here. I have discovered this in real-world application, imagine
code like:

For i = 0 to 1000
Call MySub()
Next i


Sub MySub
Set db = CurrentDB()
[do the rest]
end sub

or you have a recursion function, which calls itselt, where you also do Set
db = CurrentDB(), you can easy reach the limit

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[DAVID]

You've got MyRecordset defined as a DAO recordset.
That hasn't bought you much flexibility at all. If
you are going to change datasources, do you still
want to use a DAO recordset? If you still want to
use a DAO recordset, aren't you going to use a DAO
database?

Are you going to define everything else as an
"object" and depend on the fact that dao and ado
recordsets both have "fields" collections?

I've got code that hides open recordsets, but I use
it to cache or search frequently used recordsets:
I don't expose the recordset that I open:
vrate = vGetExchangeRate("AUD","USD",vDate)

I've got code that returns an array, and code
that returns a combo-box list.

I've got code that replaces Dlookup in library
databases, but it returns a field value, not
a recordset.

When I want a DAO recordset, I use DAO openrecordset...

Some people use special code to replace
CurrentDB. I don't: I design code that
where that doesn't matter.

Some people use special code to refer to
their backend database. I don't because
my backend database is sometimes not a
DAO database, so the direct connect optimisations
are not interchangeable. You have to use
higher level constructs if you want that.

Also, I'm not sure there is any point to defining that
function with optional parameters and default
values. Either use it to redifine the defaults,
or require all parameters.


(david)

 

[Stefan Hoffmann]

hi Alex,

can't agree here. I have discovered this in real-world application,
imagine code like:
or you have a recursion function, which calls itselt, where you also do
Set db = CurrentDB(), you can easy reach the limit

Yup, this is normal code reuse. And that is the point for using *any*
implementation of CurrentDbC or dbLocal.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi David,

No, any performance difference will be completely subsumed in
human-level interactions which are several orders of magnitude
greater than the difference between CurrentDB() and DBEngine(0)(0).

On this level, yes. But on a lower level - call it user experience - it
will be recognized, even if it is not measurable with a stop watch. Just
make any method call in a application one millisecond slower. I'm sure,
you will _feel_ it.

What logic is there to this loop:

For i = 0 to 1000
Set db = CurrentDB()
[do something with db]
Next i

This just demonstrates that the implementation of CurrentDbC is _faster_
than dbLocal. As any demonstration it lacks of actual use or sense.

But as Alex already wrote: You don't know, how often a procedure is
called during run time.


mfG
--> stefan <--

 

[David W. Fenton]

Some people use special code to replace
CurrentDB. I don't: I design code that
where that doesn't matter.

How do you do that?

 

[David W. Fenton]

[quoting me:]

Again, there is never any reason to us CurrentDB() in a loop, as
the database it refers to can never be different from one
iteration of the loop to the next. Thus, there is no cumulative
performance penalty in using CurrentDB() over DBEngine(0)(0) that
makes a difference in real-world application programming. It only
makes a difference if you set up an artificial structure that
should never happen in a well-designed application.

can't agree here. I have discovered this in real-world
application, imagine code like:

For i = 0 to 1000
Call MySub()
Next i

Sub MySub
Set db = CurrentDB()
[do the rest]
end sub

or you have a recursion function, which calls itselt, where you
also do Set db = CurrentDB(), you can easy reach the limit

As Stefan says, this is why one uses a persistent pointer to the
currently opened database.

I would write the code you give as:

For i = 0 to 1000
Call MySub(dbLocal)
Next i

Sub MySub (db As DAO.Database)
[do the rest]
end sub

....where dbLocal is my function that returns the reference from a
static variable.

Again, there is no code that does what you are suggesting that is
not badly designed code.

 

[David W. Fenton]

On this level, yes. But on a lower level - call it user experience
- it will be recognized, even if it is not measurable with a stop
watch. Just make any method call in a application one millisecond
slower. I'm sure, you will _feel_ it.

I am certain no user will *ever* notice anything.

What logic is there to this loop:

For i = 0 to 1000
Set db = CurrentDB()
[do something with db]
Next i

This just demonstrates that the implementation of CurrentDbC is
_faster_ than dbLocal. As any demonstration it lacks of actual use
or sense.

Huh? I didn't use your function. I used CurrentDB. I would expect
your CurrentDbC or my dbLocal would be faster, but would not be used
in this situation, anyway. The reason is because you wouldn't need
to set a db variable to anything, but just use your property or my
function directly.

But as Alex already wrote: You don't know, how often a procedure
is called during run time.

Well-designed code conserves resources and doesn't make unnecessary
calls.

For instance, my replacements for the domain aggregate functions,
which are based on Trevor Best's t-functions, all have an optional
database object parameter. That way, I can pass an existing database
object to them, or when I don't, the code will set the database
variable to CurrentDB(). This also makes for more useful code as it
can then be used on database other than the current one or its
linked tables.

That is good code design.

And that's what I've been talking about in this thread -- don't
write code that will be inefficient in the first place and you won't
have to worry about how it is used.

 

[Guest]

3 reasons I can think of why this could turn out useful later in your
development...

1. If you might want to do something to the recordset immediately after
you've opened it. We found out that we did need to do just that (some 9 years
after a major application was first written - by then we had over 1500
invocations of our OpenRecordset procedure so adding a few lines to that
procedure was a easy option for us.)

2. If you decided to port your VBA code into VB6 (or even VB.NET...yes, DAO
can still be used easily in .Net although nobody admits it) you won't have a
CurrentDb...you'll have to explicitly create a database object. You can just
change the Reference to CurrentDb to your explicitly created one in one place.

3. Better still, for the VB6 scenario, put the OpenDatabase stuff and the
OpenRecordset stuff and the closing of them into one module where you can
control the scope of the database objects (yes, objects, as you may find one
day you need to access more than one database)

In many cases some forward thinking in a design can save a lot of time later
when things are upsized. By the way, no need to worry about the "rs"
recordset - you don't need it at all. Replace all the instances of "rs" with
"MyRecordset" and get rid of the Dim and the last Set.