Question from Access 2007 VBA by Hennig et al

[JGPatrick]

Hennig et al is raising more questions for me than it is answering, as you
might guess from my previous two posts.

Hennig et al say the way to establish a reference to the default database is
to create a DAO.database property:

*****
Public dbC as DAO.database

Public Property Get CurrentDbC() As DAO.Database
If (dbC Is Nothing) Then Set dbC = CurrentDb()
Set CurrentDbC = dbC
End Property
*****

and then use the property, for eg:

*****
Sub Prac()
Dim Dbs As DAO.Database
Set Dbs = CurrentDbC
End Sub
******

My question is, why wouldn't you just do the following, which simply uses
the built-in CurrentDb function:

Sub Prac()
Dim Dbs As DAO.Database
Set Dbs = CurrentDb()
End Sub

 

[Banana]

I'm sure Henning et al gave their reasoning in the book, which I've not
read myself but I will guess at the reason. It'd be similar to the
age-old debate between CurrentDb() and DBEngine(0)(0) which give you the
same references but with different effects.

When you call CurrentDb(), it automatically refreshes all collections
for you so thus a call to CurrentDb() will be on average slower than
DBEngine(0)(0) but if you call DBengine(0)(0), you may run in cases
where a newly created object does not exist because the collection
wasn't refreshed. On the flip side, I've seen code fail when referencing
CurrentDb and trying to delete objects but does not do this completely
(e.g it only delete every other object rather than each object).

Anyway, I suspect Henning et al recommends this primarily so you have a
most current reference without need to refreshing it every time you call
if you called CurrentDb() in every and each procedure where you need.
Also, note that the procedure is self-healing so that you need not worry
if the reference gets lost- it'll just get it back for you automatically.

FWIW, another alternative is to use a With CurrentDb...End With which
will accomplish similar thing but without needing to hold a persistent
reference which may become outdated when changes are made to it.

Just my $0.02.

 

[Douglas J. Steele]

The property sees whether the public dbC object has been instantiated, and
only instantiates it if it isn't already instantiated. That's slightly more
efficient than instantiating it each time.

 

[Marshall Barton]

Hennig et al is raising more questions for me than it is answering, as you
might guess from my previous two posts.

Hennig et al say the way to establish a reference to the default database is
to create a DAO.database property:

*****
Public dbC as DAO.database

Public Property Get CurrentDbC() As DAO.Database
If (dbC Is Nothing) Then Set dbC = CurrentDb()
Set CurrentDbC = dbC
End Property
*****

and then use the property, for eg:

*****
Sub Prac()
Dim Dbs As DAO.Database
Set Dbs = CurrentDbC
End Sub
******

My question is, why wouldn't you just do the following, which simply uses
the built-in CurrentDb function:

Sub Prac()
Dim Dbs As DAO.Database
Set Dbs = CurrentDb()
End Sub

As Lyle Fairfield has succinctly explained it

"CurrentDb Is CurrentDb is *always* false"

Note that Currentdb is a built-in ***function*** that
constructs a refreshed *copy* of the DbEngine(0)(0) object
each time it is called. [It's not always DbEngine(0)(0) but
that's a different issue]

The reason that the book suggests using your own
function/property to do that is because it only uses
CurrentDb the first time it is called. The reason some
people feel the need to do that is because CurrentDb is
somewhere between 5,000 and 10,000 times slower than just
referring to DbEngine(0)(0) and because you will be using
the same copy all the time so you do not need to declare it
everywhere you use it. But, on modern machines, the time
difference is a very small fraction of a second and you will
not notice it unless you use CurrentDb inside a loop.

The downside is that the copy of CurrentDb may need to be
refreshed if you are not using the Refresh method after
adding items to collections that need to be refreshed. In
my book, forgetting to use Refresh and relying on CurrentDb
to mask the problem is a very poor practice so, IMO, this is
not a valid argument against using what the book said.

 

[David C. Holley]

Also there are times where CurrentDB can't be used such as when you're
working with transactions.

 

[David C. Holley]

That approach comes in handy more often than not. I buildt an app that
relying on pulling the Windows ID of the current user. It worked fine on my
personal laptop, but when I ran it on a machine connected to the network, it
literally ground to a halt, presumably because ActiveDirectory came into
play. I then shifted to the approached described where the code only gets
the value if it doesn't already have it.

 

[David W. Fenton]

Hennig et al say the way to establish a reference to the default
database is to create a DAO.database property:

*****
Public dbC as DAO.database

Public Property Get CurrentDbC() As DAO.Database
If (dbC Is Nothing) Then Set dbC = CurrentDb()
Set CurrentDbC = dbC
End Property
*****

and then use the property, for eg:

*****
Sub Prac()
Dim Dbs As DAO.Database
Set Dbs = CurrentDbC
End Sub
******

People have explained what the code does and explained why. Here
after my sig is an alternative that is much more self-contained (not
public module-level variable), and has substantially more error
cases handled.

Ask if you want further explanation of specific parts of the code.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function dbLocal(Optional bolCleanup As Boolean = False) _
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 bolCleanup 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
'dbCurrent.close
Set dbCurrent = Nothing
End If
GoTo exitRoutine

errHandler:
Select Case Err.Number
Case 3420 ' Object invalid or no longer set.
Set dbCurrent = Nothing
If Not bolCleanup 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

 

[JGPatrick]

I would like to thank the posters on this topic for helping me understand
better the value of checking for instantiation first. The book's code now
makes sense to me.
And thanks to David Fenton for sharing his code.