By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

A97 syntax for specifying currently open database in DAO...

P: n/a
MLH
Suppose I'm in an open database (northwind.mdb). Is there
a shorter form of doing the following after I've dim'd dbsNorthwind
as database...

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

???

Or, should I always use the above syntax in DAO situations where
I just want to move about a recordset in the database updating
records as I see fit?
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
umm...
currentdb
or
dbengine(0)(0)

Nov 13 '05 #2

P: n/a
MLH
Thanks pietlinden.

umm...
currentdb
or
dbengine(0)(0)


Nov 13 '05 #3

P: n/a
pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
umm...
currentdb
or
dbengine(0)(0)


The latter can fail in certain cases (immediately after a wizard has
been run).

CurrentDB().Name will never return anything other than the name of
db that the human user thinks is open in the user interface.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

P: n/a
David W. Fenton wrote:
pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

umm...
currentdb
or
dbengine(0)(0)

The latter can fail in certain cases (immediately after a wizard has
been run).


I find it consistent if you run a wizard, close the current database
leaving Access open then open another database.

--
[OO=00=OO]
Nov 13 '05 #6

P: n/a
Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:
David W. Fenton wrote:
pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

umm...
currentdb
or
dbengine(0)(0)


The latter can fail in certain cases (immediately after a wizard
has been run).


I find it consistent if you run a wizard, close the current
database leaving Access open then open another database.


But you're not going to know if your call to DBEngine(0)(0) is going
to come immediately after re-opening the database.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7

P: n/a
David W. Fenton wrote:
Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:

David W. Fenton wrote:
pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.googl egroups.com:

umm...
currentdb
or
dbengine(0)(0)

The latter can fail in certain cases (immediately after a wizard
has been run).


I find it consistent if you run a wizard, close the current
database leaving Access open then open another database.

But you're not going to know if your call to DBEngine(0)(0) is going
to come immediately after re-opening the database.


It used to, it was one of the first lines of code in the database, that
always seemed to be executed immediately after re-opening it.

--
[OO=00=OO]
Nov 13 '05 #8

P: n/a
Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:
David W. Fenton wrote:
Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:
David W. Fenton wrote:

pi********@hotmail.com wrote in
news:11**********************@g49g2000cwa.goog legroups.com:

>umm...
>currentdb
>or
>dbengine(0)(0)

The latter can fail in certain cases (immediately after a wizard
has been run).

I find it consistent if you run a wizard, close the current
database leaving Access open then open another database.


But you're not going to know if your call to DBEngine(0)(0) is
going to come immediately after re-opening the database.


It used to, it was one of the first lines of code in the database,
that always seemed to be executed immediately after re-opening it.


Well, that means your caching a reference, which is another
suggestion entirely.

I still think the idea that CurrentDB() is a vastly slower than
DBEngine(0)(0) (while absolutely true) is one of the most misleading
and widely misinterpreted facts about Access programming that I know
of.

IT DOESN'T MATTER THAT IT'S SLOWER.

There is no circumstance where you would need to call CurrentDB()
enough times for the difference to make a difference in a real-world
app.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9

P: n/a

"David W. Fenton" wrote
I still think the idea that CurrentDB() is
a vastly slower than DBEngine(0)(0)
(while absolutely true) is one of the most
misleading and widely misinterpreted
facts about Access programming that
I know of.

IT DOESN'T MATTER THAT IT'S SLOWER.

There is no circumstance where you
would need to call CurrentDB() enough
times for the difference to make a differ-
ence in a real-world app.


And, the same is true of many questions about "which is better/faster"
regarding VBA code.

In many cases the difference is not enough that you would notice it unless
you did that particular thing alone in a loop executed hundreds of thousands
of times, compared with the alternative in a similar loop.

Larry Linson
Microsoft Access MVP


Nov 13 '05 #10

P: n/a
David W. Fenton wrote:
IT DOESN'T MATTER THAT IT'S SLOWER.


Keep yer hair on. Nobody apart from you mentioned the speed in this
thread AFAICS.

--
[OO=00=OO]
Nov 13 '05 #11

P: n/a
Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:
David W. Fenton wrote:
IT DOESN'T MATTER THAT IT'S SLOWER.


Keep yer hair on. Nobody apart from you mentioned the speed in
this thread AFAICS.


It's a common subtext in discussions of this nature. Many people are
adamant about avoiding CurrentDB() because of the supposed speed
penalty.

And I think those people are nutcases.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.