Question about CurrentDb usage

[emanning]

Using A2003. I'm just about to start major modifications on a mdb
created in 2003. There is a lot of this type of code structure
(below) throughout the mdb wherever a recordset or querydef is
referenced. "dbs" is dim-ed globally as Database in a module. My
question is: does dbs need to be set and closed after every usage of
recordset and querydef? Could I just set it once when the user opens
the mdb then close it when the user closes the mdb? I'm getting an
error message #91 in one of these structures (Object variable or With
block variable not set) and I've discovered that dbs was closed
somewhere else in code.

Set dbs = CurrentDb

<...code that either opens a recordset or creates a
querydef...>

dbs.Close
Set dbs = Nothing

If it's better practice to structure the code this way, then what's
the code to determine if dbs is still open?

Thanks for any help or advice.

[Rich P]

Here is how you "declare" (or dim) dbs globally: In a standards code
module just under

Option Compare Database
Option Explicit

Public dbs As DAO.Database

The thing about VBA is that by default - it uses a protocol called
Late-binding. So if dbs gets instantiated in one procedure without the
dbs.close statement, and then dbs gets instantiated again in another
procedure -- that will just kill the original instantiation
automatically. Another way to manage dbs would be a statement like this
at the end of a procedure:

Set dbs = Null

I generally will declare a dbs object within a procedure. It is just
shorthand notation for currentdb.

CurrentDB.OpenRecordset vs dbs.OpenRecordset. CurrentDB is like a
global dbs (it is actually global-er - more global).

In lower level languages like VB.Net/C#/C++ (languages that are closer
to the operating system machine language) which don't use late binding
by default you could have a problem with a global reference to something
like dbs (well, VB.Net may still use latebinding as default - but if you
set option strict - then it is early binding). So in VBA, which is one
of the furthest languages from the OS (one of the highest level
languages second I think only to MS macro language) I don't think you
need to worry too much about a global dbs object. However, you may
consider eliminating the global dbs and declare it inside each procedure
that uses a dbs object - for safety and good practice.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Ken Snell [MVP]]

See
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1720818

Good discussion with links about use of CurrentDb.
--

Ken Snell
http://www.accessmvp.com/KDSnell/

"emanning" <eman...@kumc.edu> wrote in message
news:3d753b88-fbd0-49a7...@j14g2000yqm.googlegroups.com...

[paii, Ron]

"emanning" <eman...@kumc.edu> wrote in message
news:3d753b88-fbd0-49a7...@j14g2000yqm.googlegroups.com...

Your error is caused by dbs.Close, you only need the Set dbs = Nothing line.

Set dbs = CurrentDB only sets a reference to an already open db.

Only close what you open.

[David W. Fenton]

emanning <eman...@kumc.edu> wrote in
news:3d753b88-fbd0-49a7...@j14g2000yqm.googlegroups.co
m:

Closing a database variable initialized with CurrentDB won't do
anything except invalidate the pointer stored in the variable. By
itself, it should not cause an error. But if you close the database
and then try to use the database variable again without
re-initializing it, you will encounter the error. It's not caused by
closing, but by failing to initialize.

The code you're looking at seems inadequate to me. It looks like
somebody had a decent idea (use a global variable and intialize
once) but didn't know how to do it reliably, so repeatedly
re-initializes (though, apparently, not as often as necessary). On
advice from Michael Kaplan given in this newsgroup, I long ago wrote
a wrapper function to serve this purpose, and the code for it is
after my signature.

With that, you don't have to initialize, and don't have to close
anything. The only thing you ever have to do is clean it up in your
app's shutdown routine (and even skipping that won't always cause
problems).

One other caveat:

If you're initializing a db variable with DBEngine(0)(0), a .Close
*will* cause an error. This is a difference because this is a
pointer to the same object, whereas each time you initialize with
CurrentDB, you get a different memory structure that is distinct
from the actual structures used by Access to interact with the
database currently open in the UI.

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

[Tony Toews [MVP]]

Rich P <rpn...@aol.com> wrote:

>The thing about VBA is that by default - it uses a protocol called
>Late-binding.

Huh? How is late binding related to a dao object?

>In lower level languages like VB.Net/C#/C++ (languages that are closer
>to the operating system machine language)

Given that there is the CLR between the .Net languages and the OS I'd say that those
are further away from the hardware.

>which don't use late binding

This statement doesn't make a lol of sense to me either.

>However, you may
>consider eliminating the global dbs and declare it inside each procedure
>that uses a dbs object - for safety and good practice.

Or just use Currentdb which I use everywhere.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

[hbinc]

Hi emanning,

On the use of CurrentDb there are many discussions, many different
opinions, many wild tales.
For me counts the practice.

These are the rules that I use:
1. I try to avoid globals. At second thought more globals can be
removed than at first thought.
2. I use dbs or a database variable only inside Subs or Functions, and
only to refer to external databases.
3. I use CurrentDb everywhere in the Subs and Functions for the
OpenRecordSet or Execute or other methods.

With this way of working I have never met any problem.
I even did a test some time ago to use CurrentDb in a loop more than
100,000 times, and compared it with a database variable.
It did not give any problems, and I did not find the reported loss of
performance.

Still using A97, but with enhanced functionality.

HBInc.