Create one function for all DAO Recordset connections
KitCaz
application, and I thought that it might be a good idea to centralize the
connection for all of these calls into a single function so that if/when I
change my datasource (e.g. from MS Access to SQL) I can change one place.
Assuming this is a good idea (and you can tell me if it isn't), I created a
function (leaving out error processing):
Public Function MyRecordset(rsString As String, Optional intType As Integer,
Optional intOptions As Integer) As DAO.Recordset
Dim rs As DAO.Recordset
If intType <> 0 And intOptions <> 0 Then
Set rs = CurrentDb.OpenRecordset(rsString, intType, intOptions)
Else
If intOptions <> 0 Then
Set rs = CurrentDb.OpenRecordset(rsString, , intOptions)
Else
If intType <> 0 Then
Set rs = CurrentDb.OpenRecordset(rsString, intType)
Else
Set rs = CurrentDb.OpenRecordset(rsString)
End If
End If
End If
Set MyRecordset = rs
End Function
With this function, I was planning to change all my "set
rs=db.openrecordset(.." calls to "set rs=MyRecordset(..".
Is this a sound approach? I find that I cannot close/erase my rs recordset
variable in my MyRecordset function (otherwise there's nothing to pass out)
so that bothers me, but maybe this is an overhead I need to live with?
Other approaches welcome...
Alex Dybenko
do not see a big sense for such function, then only thing - that you can use
database type variable instead of CurrentDB, to avoid this error:
http://alexdyb.blogspot.com/2005/12/be-careful-using-currentdb.html
--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
"KitCaz" <Kit...@discussions.microsoft.com> wrote in message
news:C777E79F-57BE-4EA8...@microsoft.com...
Stefan Hoffmann
KitCaz wrote:
> With this function, I was planning to change all my "set
> rs=db.openrecordset(.." calls to "set rs=MyRecordset(..".
> Is this a sound approach? I find that I cannot close/erase my rs recordset
> variable in my MyRecordset function (otherwise there's nothing to pass out)
> so that bothers me, but maybe this is an overhead I need to live with?
This is not really necessary as lon as you don't use some OO approach.
> Other approaches welcome...
Try a simple one, place it in a standard module:
Private m_CurrentDb As DAO.Database
Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
With this property you don't need the
Dim db As DAO.Databse
Set db = CurrentDb
in each of your methods any more. Due to the nature of CurrentDb you
will gain some speed from it.
mfG
--> stefan <--
KitCaz
and avoid the multiple connections.
KitCaz
I'm not sure what you meant by "some OO approach" (I supposed I should know
what "OO" means but I don't).
At any rate, thank you so much for the sample.
So I don't need to make "Private m_CurrentDb As DAO.Database" public
(because it's in a std module and thus scoped for the entire app)? Scoping
and I are slow to become fast friends. :)
Also, once I've created the propertly then I replace all my "currentdb."
references to "m_currentdb" and I'm set, right?
KitCaz
Stefan Hoffmann
KitCaz wrote:
> I'm not sure what you meant by "some OO approach" (I supposed I should know
> what "OO" means but I don't).
Object oriented programming.
> Also, once I've created the propertly then I replace all my "currentdb."
> references to "m_currentdb" and I'm set, right?
No, you use CurrentDbC.
>> Property Get CurrentDbC() As DAO.Database
This must be
Public Property Get CurrentDbC() As DAO.Database
mfG
--> stefan <--
KitCaz
THANKS!
David W. Fenton
news:uLYGpX2p...@TK2MSFTNGP05.phx.gbl:
> do not see a big sense for such function, then only thing - that
> you can use database type variable instead of CurrentDB, to avoid
> this error:
> http://alexdyb.blogspot.com/2005/12/be-careful-using-currentdb.html
Well, that's just silly. When would you ever deploy code like that?
Why would you ever need even 250 references to the same database?
This is just a variant of the CurrentDB() vs. DBEngine(0)(0)
argument -- it makes no sense because the issues only arise when you
loop in a way that makes no sense for real-life code.
Now, you *can* replace all those Set db = CurrentDB() calls with a
function that always returns a pointer to the CurrentDB. The idea
was suggested by Michael Kaplan many years ago and my implementation
is posted after my signature.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Public Function dbLocal(Optional ysnInitialize As Boolean = True) As
DAO.Database ' 2003/02/08 DWF added comments to explain it to
myself! ' 2005/03/18 DWF changed to use Static variable instead
' uses GoTos instead of If/Then because:
' error of dbCurrent not being Nothing but dbCurrent
' being closed (3420) would then be jumping back into
' the middle of an If/Then statement
On Error GoTo errHandler
Static dbCurrent As DAO.Database
Dim strTest As String
If Not ysnInitialize Then GoTo closeDB
retryDB:
If dbCurrent Is Nothing Then
Set dbCurrent = CurrentDb()
End If
' now that we know the db variable is not Nothing,
' test if it's Open
strTest = dbCurrent.Name
exitRoutine:
Set dbLocal = dbCurrent
Exit Function
closeDB:
If Not (dbCurrent Is Nothing) Then
Set dbCurrent = Nothing
End If
GoTo exitRoutine
errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, _
vbExclamation, "Error in dbLocal()"
Resume exitRoutine
End Select
End Function
David W. Fenton
news:1D39AC61-3C49-40EB...@microsoft.com:
> I read your article and will make my DB reference universal
> and avoid the multiple connections.
Not necessary. Alex's point is about calling CurrentDB in a loop --
it's not relevant to real-life programming. See my reply to Alex.
David W. Fenton
news:u6gUPY2p...@TK2MSFTNGP05.phx.gbl:
> Private m_CurrentDb As DAO.Database
>
> Property Get CurrentDbC() As DAO.Database
>
> If m_CurrentDb Is Nothing Then
> Set m_CurrentDb = CurrentDb
> End If
>
> Set CurrentDbC = m_CurrentDb
>
> End Property
Can you use a static variable in property declaration? If so, you
could get rid of the dependency on the module-level variable.
You might also want to consider what happens when your application
exits. If you put your property in a class module, it would be
harder to refer to (you'd have to create a wrapper module), but then
you could have a class terminate action that clears your variable.
See my implementation of something similar in reply to Alex.
Stefan Hoffmann
David W. Fenton wrote:
> Can you use a static variable in property declaration? If so, you
> could get rid of the dependency on the module-level variable.
I don't know the original author of this hack, may be its also Kaplan,
but using the module level variable and a property is the fastest
solution. See the simple test code below.
> You might also want to consider what happens when your application
> exits. If you put your property in a class module, it would be
> harder to refer to (you'd have to create a wrapper module), but then
> you could have a class terminate action that clears your variable.
My property is not in a class module. It is placed in a normal module.
Due to the nature of CurrentDb I don't think it is necessary to
explicily free the reference when terminating the application.
mfG
--> stefan <--
--
Option Compare Database
Option Explicit
Private m_CurrentDb As DAO.Database
Public Sub Test()
Const MAX_LOOP As Long = 1000000
Dim LoopCount As Long
Dim TickCount As Long
Dim db As DAO.Database
TickCount = GetTickCount
For LoopCount = 1 To MAX_LOOP
Set db = CurrentDbC
Next LoopCount
Debug.Print "module level:"; GetTickCount - TickCount; " ms"
TickCount = GetTickCount
For LoopCount = 1 To MAX_LOOP
Set db = CurrentDbCStatic
Next LoopCount
Debug.Print "static inline:"; GetTickCount - TickCount; " ms"
TickCount = GetTickCount
For LoopCount = 1 To MAX_LOOP
Set db = dbLocal
Next LoopCount
Debug.Print "function:"; GetTickCount - TickCount; " ms"
TickCount = GetTickCount
For LoopCount = 1 To MAX_LOOP
Set db = dbLocalSimple
Next LoopCount
Debug.Print "simple function:"; GetTickCount - TickCount; " ms"
Debug.Print
End Sub
Public Property Get CurrentDbC() As DAO.Database
If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb
End Property
Public Property Get CurrentDbCStatic() As DAO.Database
Static s_CurrentDb As DAO.Database
If s_CurrentDb Is Nothing Then
Set s_CurrentDb = CurrentDb
End If
Set CurrentDbCStatic = s_CurrentDb
End Property
Public Function dbLocalSimple() As DAO.Database
Static s_CurrentDb As DAO.Database
If s_CurrentDb Is Nothing Then
Set s_CurrentDb = CurrentDb
End If
Set dbLocalSimple = s_CurrentDb
End Function
Alex Dybenko
of course nobody will deploy such code, but several times I saw that people
use currentdb in subs and functions, and some function could be called 250
times, in a query or in recursion, and then you get this error also.
OP did the same...
"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns9946925B6FEB7f9...@127.0.0.1...
KitCaz
reply better than me (Stefan, if so, and if you have a response please share
it).
Alex' webpage simply states "The workaround – is to declare a public
variable dbs as dao.database, set it to CurrentDB at program startup and then
use it instead of CurrentDB."
Is this what you are referring to? I'm just not sure where to look on your
site (since you stated "See my implementation of something similar in reply
to Alex."). Can you provide / point me to more detail?
As for "what happens when my application exits": can't I set the
module-level variable to nothing when my main form closes?
Stefan Hoffmann
KitCaz wrote:
> David, thanks for the feedback. I think maybe Stefan will understand your
> reply better than me (Stefan, if so, and if you have a response please share
> it).
Davids point is some kind of tech talk. It's just about implementation
details.
You may choose either David's function or my property. Both provide the
same core functionality: avoiding multiple calls to CurrentDb.
When you will dig into MSDN and the online help searching for CurrentDb
you will find something like the following:
Each call to CurrentDb creates a new object, it will not only return a
simple reference. Due to this fact using CurrentDbC or dbLocal will call
it normally only once during application live time and thus save a lot
of time.
It does it in my projects, i'm using a lot of CurrentDbC.Execute ""
calls to avoid the use of queries.
> Alex' webpage simply states "The workaround – is to declare a public
> variable dbs as dao.database, set it to CurrentDB at program startup and then
> use it instead of CurrentDB."
The point Alex' is trying to show: multiple calls of CurrentDb will
result in use of many resources, e.g. memory, and it also shows some
limitations in Access/Jet due to poor implementation.
mfG
--> stefan <--
David W. Fenton
> David W. Fenton wrote:
>> Can you use a static variable in property declaration? If so, you
>> could get rid of the dependency on the module-level variable.
>
> I don't know the original author of this hack, may be its also
> Kaplan, but using the module level variable and a property is the
> fastest solution.
I wrote mine based on Michael's suggestion, and I used a function,
because that's what he suggested, I'm pretty sure. Had he suggested
a property, I'm pretty sure that's what I would have used.
>>See the simple test code below.
I don't understand your test code. Why are you setting a db
variable? Why not just use the function or property directly?
I do this all the time:
Set rs = dbLocal.OpenRecordsets(...)
That's the whole point -- you use the function directly.
I see no reason why you couldn't use the property exactly the same
way.
>> You might also want to consider what happens when your
>> application exits. If you put your property in a class module, it
>> would be harder to refer to (you'd have to create a wrapper
>> module), but then you could have a class terminate action that
>> clears your variable.
>
> My property is not in a class module. It is placed in a normal
> module. Due to the nature of CurrentDb I don't think it is
> necessary to explicily free the reference when terminating the
> application.
It's a db variable that was set with CurrentDB(). If you need to set
this one to Nothing:
Dim db As DAO.Database
Set db = CurrentDB()
...
Set db = Nothing
then naturally, you need to set the private variable to Nothing
before your application terminates. I suggested a class module as a
good place to put that termination code, but it does mean you'd need
a wrapper function to use your property (because it would then be a
property of the class instance).
The function seems much more transparent and easy to use.
And I don't really care if it's faster to use a property, to be
honest -- worrying about that is just recapitulating the old
CurrentDB() vs. DBEngine(0)(0) debate, which was stupid, because
you'd never use CurrentDB() in a context in which the difference
could accumulate to anything significant. That is, it's logically
stupid to loop multiple times and set variables to CurrentDB(), as
the value of CurrentDB() can't change during the scope of the loop.
Thus, there would have to be many milliseconds difference to make a
real difference. As we know, DBEngine(0)(0) is many times faster
than CurrentDB() (because DBEEngine(0)(0) doesn't refresh the
collections), but IT DOESN"T MATTER because you have to call it
hundreds of times (or more) to see the difference, and that's simply
not a real-world situation.
Except for the very stupid.
David W. Fenton
> KitCaz wrote:
>> David, thanks for the feedback. I think maybe Stefan will
>> understand your reply better than me (Stefan, if so, and if you
>> have a response please share it).
>
> Davids point is some kind of tech talk. It's just about
> implementation details.
Implementation details matter!
This is a piece of code that I've worked over many times because I
use it in all my apps. Thus, I want it to be as robust as possible.
> You may choose either David's function or my property. Both
> provide the same core functionality: avoiding multiple calls to
> CurrentDb.
Not only that, though -- both our versions also initialize
themselves (even if there's a code reset), something that the
original suggestion does *not* do (just setting a global db variable
on app startup is not good enough for me).
> When you will dig into MSDN and the online help searching for
> CurrentDb you will find something like the following:
>
> Each call to CurrentDb creates a new object, it will not only
> return a simple reference. Due to this fact using CurrentDbC or
> dbLocal will call it normally only once during application live
> time and thus save a lot of time.
> It does it in my projects, i'm using a lot of CurrentDbC.Execute
> "" calls to avoid the use of queries.
You do have to be carefult if you're deleting or adding to any of
the databases collections, because they won't be refereshed on your
cached database variable -- you'd need to do that manually.
>> Alex' webpage simply states "The workaround ? is to declare a
>> public variable dbs as dao.database, set it to CurrentDB at
>> program startup and then use it instead of CurrentDB."
>
> The point Alex' is trying to show: multiple calls of CurrentDb
> will result in use of many resources, e.g. memory, and it also
> shows some limitations in Access/Jet due to poor implementation.
It's poor implementation to have a problem when someone does
something stupid that they should never do? Given that the value of
CurrentDB() can't change within the scope of a loop, it's obvious
that CurrentDB() should never be called within a loop. Thus the fact
that doing so causes a problem is one of those issues that I hope
Microsoft wastes no time on "fixing."
Stefan Hoffmann
David W. Fenton wrote:
> As we know, DBEngine(0)(0) is many times faster
> than CurrentDB() (because DBEEngine(0)(0) doesn't refresh the
> collections), but IT DOESN"T MATTER because you have to call it
> hundreds of times (or more) to see the difference, and that's simply
> not a real-world situation.
Over the application lifetime, some are running for years, this will
save some time...
> Except for the very stupid.
Huh?
mfG
--> stefan <--
Stefan Hoffmann
David W. Fenton wrote:
>>> David, thanks for the feedback. I think maybe Stefan will
>>> understand your reply better than me (Stefan, if so, and if you
>>> have a response please share it).
>> Davids point is some kind of tech talk. It's just about
>> implementation details.
> Implementation details matter!
Of course, it does. But I think it's not a matter for the OP in the
first line.
>> It does it in my projects, i'm using a lot of CurrentDbC.Execute
>> "" calls to avoid the use of queries.
> You do have to be carefult if you're deleting or adding to any of
> the databases collections, because they won't be refereshed on your
> cached database variable -- you'd need to do that manually.
This is the normal behavior.
> It's poor implementation to have a problem when someone does
> something stupid that they should never do? Given that the value of
> CurrentDB() can't change within the scope of a loop, it's obvious
> that CurrentDB() should never be called within a loop. Thus the fact
> that doing so causes a problem is one of those issues that I hope
> Microsoft wastes no time on "fixing."
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
mfG
--> stefan <--
David W. Fenton
> David W. Fenton wrote:
>> As we know, DBEngine(0)(0) is many times faster
>> than CurrentDB() (because DBEEngine(0)(0) doesn't refresh the
>> collections), but IT DOESN"T MATTER because you have to call it
>> hundreds of times (or more) to see the difference, and that's
>> simply not a real-world situation.
>
> Over the application lifetime, some are running for years, this
> will save some time...
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).
>> Except for the very stupid.
>
> Huh?
What logic is there to this loop:
For i = 0 to 1000
Set db = CurrentDB()
[do something with db]
Next i
Since the value returned by CurrentDB() cannot change within the
scope of the loop, it shouldn't be set within the loop itself. So,
this code will work just as well IN ALL CASES:
Set db = CurrentDB()
For i = 0 to 1000
[do something with db]
Next i
In other words, there is no loop that initializes a db variable with
CurrentDB() that is properly written that can ever benefit from
usinng the faster DBEngine(0)(0). Plus there is issue that after
running a wizard, DBEngine(0)(0) might point to the wizard database
instead of to the MDB/MDE open in the Access UI.
David W. Fenton
> David W. Fenton wrote:
>>>> David, thanks for the feedback. I think maybe Stefan will
>>>> understand your reply better than me (Stefan, if so, and if you
>>>> have a response please share it).
>>> Davids point is some kind of tech talk. It's just about
>>> implementation details.
>> Implementation details matter!
>
> 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.
>>> It does it in my projects, i'm using a lot of CurrentDbC.Execute
>>> "" calls to avoid the use of queries.
>> You do have to be carefult if you're deleting or adding to any of
>> the databases collections, because they won't be refereshed on
>> your cached database variable -- you'd need to do that manually.
>
> 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).
>> It's poor implementation to have a problem when someone does
>> something stupid that they should never do? Given that the value
>> of CurrentDB() can't change within the scope of a loop, it's
>> obvious that CurrentDB() should never be called within a loop.
>> Thus the fact that doing so causes a problem is one of those
>> issues that I hope Microsoft wastes no time on "fixing."
>
> 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
> 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
DAVID
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
Alex Dybenko wrote:
> 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
David W. Fenton wrote:
>> Over the application lifetime, some are running for years, this
>> will save some time...
> 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
news:OIr5jOOr...@TK2MSFTNGP05.phx.gbl:
> 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
> David W. Fenton wrote:
>>> Over the application lifetime, some are running for years, this
>>> will save some time...
>>
>> 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.
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.
Colin
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.
--
Colin