pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
umm...
currentdb
or
dbengine(0)(0)
Sorry, forgot one little bit:
CurrentDB() refreshes all collections when called, so it's a heavier
hit, performance-wise, than DBEngine(0)(0). However, that difference
would only become apparent in a loop where you were calling
CurrentDB() multiple times (i.e., hundreds or thousands of times in
a row).
Given that this is not a real-world scenario (during the running of
the loop, CurrentDB() can't possibly change its return value, so
there's no purpose in not calling it once before you enter the loop,
in which case we're talking milliseconds of difference between the
two calls, a difference that only becomes apparent when you are
doing something that is stupid to begin with).
However, all that said, you can reduce even that performance
difference to nothing at all by using a global variable to store a
reference to the currently opened MDB, which you set from
CurrentDB() when you open your application.
I wrap this in a self-healing function, because that survives code
resets. The code for it is appended after my signature.
I use dbLocal in any code where I'd be referring to CurrentDB()
properties, or whenever I'd normally be doing Set db = CurrentDB().
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc
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
' would (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
'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 ysnInitialize Then
Resume retryDB
Else
Resume closeDB
End If
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in mdlWB.dbLocal()"
Resume exitRoutine
End Select
End Function
