Create one function for all DAO Recordset connections

[KitCaz]

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

 

[KitCaz]

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

 

[KitCaz]

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?

 

[KitCaz]

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

 

[KitCaz]

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

 

[KitCaz]

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]

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.

 

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