Create one function for all DAO Recordset connections

[Guest]

I've got scads of DAO "set rs = db.openrecordset(..." calls throughout my
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]

Hi,
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

 

[Stefan Hoffmann]

hi,

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

 

[Guest]

OK, thanks Alex. I read your article and will make my DB reference universal
and avoid the multiple connections.

 

[Guest]

Stefan,

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?

 

[Guest]

rather, I replace all my "currentdb." references to "currentDBc.", right?

 

[Stefan Hoffmann]

hi Kit,

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.
This must be

Public Property Get CurrentDbC() As DAO.Database


mfG
--> stefan <--

 

[Guest]

Re: "OO". Duh.

THANKS!

hi Kit,

Object oriented programming.

No, you use CurrentDbC.

This must be

Public Property Get CurrentDbC() As DAO.Database


mfG
--> stefan <--

 

[David W. Fenton]

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]

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]

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]

hi David,

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

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

 

[Alex Dybenko]

Hi David,
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...
--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

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

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]

hi Chris,

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]

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.

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.

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]

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.

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]

hi David,

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]

hi David,

Implementation details matter!

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

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]

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

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.