CurrentDB() vs DbEngine(0)(0): Redeeming Qualities?

I've just been through the "Error3048: Cannot open any more databases"
thing.

Seems like DbEngine(0)(0).OpenRecordset allows one to open more tables
- either linked or internal - than CurrentDB().OpenRecordset.

That being the case, is there any reason to use CurrentDB
().OpenRecordset .... ever...?

e.g.
Public Function Error3048Test() As String
On Error GoTo Error3048Test_err

' PURPOSE: To explore relationship of "Error3048: Cannot open any more
databases" to
' CurrentDB() vs DBEngine(0)(0) when opening recordsets
'
' NOTES: 1) With a non-linked table, the number of open tables seems
tb
' 243 with CurrentDB and 403 with DbEngine
' 2) With a linked table, the number of open tables allowed
' appears to drop to 81 with CurrentDB and drop to 122
' with DbEngine(0)(0)

Dim thisDB As DAO.Database

Dim i As Long
Dim myMsg As String
Dim myRS(1000) As DAO.Recordset

Set thisDB = DBEngine(0)(0)

For i = 1 To 1000

' myMsg = " (CurrentDB, Internal)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (DbEngine Internal)"
' Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (CurrentDB, Linked)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

myMsg = " (DbEngine, Linked)"
Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

Next i

Error3048Test_xit:
Error3048Test = i & myMsg

On Error Resume Next
For i = 1 To 1000
myRS(i).Close
Set myRS(i) = Nothing
Next i
Set thisDB = Nothing
Exit Function

Error3048Test_err:
Resume Error3048Test_xit
End Function

When should you use CurrentDB vs. DBEngine(0)(0)?

(shortened version of DBEngine.Workspaces(0).Databases(0))



There is a lot of contradictory documentation about how these are used. As
far as I have been able to determine, the following apply:



CurrentDB always reflects changes made with the Graphical Interface,
DBEngine(0)(0) does not.



Creating two database variables and setting them to CurrentDB creates TWO
instances of the currently opened database. DBEngine(0)(0) refers to the
open copy of the database, so setting two database variables will point to
the same instance of the open database.



Even though two database variables created with CurrentDB are in separate
instances, changes to one affect the other, but it has to be refreshed in
order to show changes made in the other.



So it boils down to this:



1) A database variable set with CurrentDB will always be in sync with the
database currently opened in the Graphical interface. But if two or more
variables are created this way, they will need refreshing to stay in sync.



2) A database variable set with DBEngine(0)(0) will need to be refreshed to
stay in sync with the Graphical Interface. But if two or more variables are
created this way, they will always be in sync.



As far as I know, there is no speed difference between the two. (I
emphasize AFAIK.) Interestingly, Access 1.0 used CurrentDB() as the
preferred method. Access 2.0 switched to DBEngine(0)(0) as the preferred
method. All subsequent versions of Access have listed CurrentDB as the
preferred method.




--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com




"PeteCresswell" wrote in message
...
I've just been through the "Error3048: Cannot open any more databases"
thing.

Seems like DbEngine(0)(0).OpenRecordset allows one to open more tables
- either linked or internal - than CurrentDB().OpenRecordset.

That being the case, is there any reason to use CurrentDB
().OpenRecordset .... ever...?

e.g.
Public Function Error3048Test() As String
On Error GoTo Error3048Test_err

' PURPOSE: To explore relationship of "Error3048: Cannot open any more
databases" to
' CurrentDB() vs DBEngine(0)(0) when opening recordsets
'
' NOTES: 1) With a non-linked table, the number of open tables seems
tb
' 243 with CurrentDB and 403 with DbEngine
' 2) With a linked table, the number of open tables allowed
' appears to drop to 81 with CurrentDB and drop to 122
' with DbEngine(0)(0)

Dim thisDB As DAO.Database

Dim i As Long
Dim myMsg As String
Dim myRS(1000) As DAO.Recordset

Set thisDB = DBEngine(0)(0)

For i = 1 To 1000

' myMsg = " (CurrentDB, Internal)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (DbEngine Internal)"
' Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (CurrentDB, Linked)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

myMsg = " (DbEngine, Linked)"
Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

Next i

Error3048Test_xit:
Error3048Test = i & myMsg

On Error Resume Next
For i = 1 To 1000
myRS(i).Close
Set myRS(i) = Nothing
Next i
Set thisDB = Nothing
Exit Function

Error3048Test_err:
Resume Error3048Test_xit
End Function

PeteCresswell wrote in

m:

I've just been through the "Error3048: Cannot open any more
databases" thing.

Seems like DbEngine(0)(0).OpenRecordset allows one to open more
tables - either linked or internal - than
CurrentDB().OpenRecordset.

That being the case, is there any reason to use CurrentDB
().OpenRecordset .... ever...?

If you're using up your table handles, your app is badly designed.

And the reason that CurrentDB uses up more table handles is because
it refreshes the collections each time you call it. I don't call
CurrentDB more than once in each session of my apps because I use a
function that initializes a static variable the first time it's run,
so that CurrentDB gets called only once.

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

"Roger Carlson" wrote in
:

CurrentDB always reflects changes made with the Graphical
Interface, DBEngine(0)(0) does not.

That is, the collections of DBEngine(0)(0) are initialized when the
database is opened and not refreshed when they are changed. Each
time you call CurrentDB, the collections are refreshed (this is why
it is many times slower than DBEngine(0)(0)).

Creating two database variables and setting them to CurrentDB
creates TWO instances of the currently opened database.

No, that's not true. CurrentDB creates nothing -- all it does is
return a pointer the the currently opened database.

DBEngine(0)(0) refers to the
open copy of the database, so setting two database variables will
point to the same instance of the open database.

This is simply not true.

Even though two database variables created with CurrentDB are in
separate instances, changes to one affect the other, but it has to
be refreshed in order to show changes made in the other.

Again, not really true. And the requirement to refresh collections
after you initialize a database variable is true regardless of
whether or not you use CurrentDB or DBEngine(0)(0). A refresh is
*not* required if you call CurrentDB again (as opposed to using a
reference cached in a database variable).

So it boils down to this:

1) A database variable set with CurrentDB will always be in sync
with the database currently opened in the Graphical interface.

NOT TRUE.

But if two or more
variables are created this way, they will need refreshing to stay
in sync.

....as do any cached references, regardless of how you initialize
them.

2) A database variable set with DBEngine(0)(0) will need to be
refreshed to stay in sync with the Graphical Interface. But if
two or more variables are created this way, they will always be in
sync.

The collections of DBEngine(0)(0) are updated when you open the
database and are never refreshed automatically during the session.
If you refresh the collections of DBEngine(0)(0), subsequent calls
to it will reflect the refreshed collections.

As far as I know, there is no speed difference between the two.
(I emphasize AFAIK.)

There are huge speed differences because a call to CurrentDB
refreshes all the collections, while DBEngine(0)(0) does not. It
doesn't matter, though, since it's not something you're going to
call in a loop where the speed difference would become significant.

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

Per David W. Fenton:
I don't call
CurrentDB more than once in each session of my apps because I use a
function that initializes a static variable the first time it's run,
so that CurrentDB gets called only once.

I used to do that, but somehow drifted away from it.

Sounds like it's time to return to the Good-Right-And-Holy-Path.

But it seems to me like, well-written or not, when screens have -
say - 10-12 subforms each, and the user wants to have three of
such screens open at one time, that one can easily be flirting
with an 80-table limit.

Am I missing something?
--
PeteCresswell

"(PeteCresswell)" wrote in
:

Per David W. Fenton:
I don't call
CurrentDB more than once in each session of my apps because I use
a function that initializes a static variable the first time it's
run, so that CurrentDB gets called only once.

I used to do that, but somehow drifted away from it.

Sounds like it's time to return to the Good-Right-And-Holy-Path.

But it seems to me like, well-written or not, when screens have -
say - 10-12 subforms each, and the user wants to have three of
such screens open at one time, that one can easily be flirting
with an 80-table limit.

Am I missing something?

A screen with 10-12 subforms visible at once is a nightmare that
should never be created. Nobody can process that amount of
information at once and there's no justification for the design
whatsoever.

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

Per David W. Fenton:
A screen with 10-12 subforms visible at once is a nightmare that
should never be created. Nobody can process that amount of
information at once and there's no justification for the design
whatsoever.

A bond trader, sufficiently caffienated, can. Trust me.

I've written three bond trading systems - at two different
clients and these people want as much information as can possibly
be fit on a screen. Give them less and they'll ask for more.

Actually "demand" is more accurate.

Same with reports. 8 or 9-point fonts... single spaced.

They live and die by hundredths of a percent and their world
turns a lot faster than mine.

I live by The Golden Rule: "He who has the gold makes the
rules.". They have the gold, they want more on a screen, they
get more on a screen.

I don't sell programming. I sell happiness.
--
PeteCresswell

According to MSDN.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

quote
Note In previous versions of Microsoft Access, you may have used the syntax
DBEngine.Workspaces(0).Databases(0) or DBEngine(0)(0) to return a pointer to
the current database. In Microsoft Access 2000, you should use the CurrentDb
method instead. The CurrentDb method creates another instance of the
current database, while the DBEngine(0)(0) syntax refers to the open copy of
the current database. The CurrentDb method enables you to create more than
one variable of type Database that refers to the current database. Microsoft
Access still supports the DBEngine(0)(0) syntax, but you should consider
making this modification to your code in order to avoid possible conflicts
in a multiuser database.
/quote

http://support.microsoft.com/kb/131881
quote
CurrentDB()and DBEngine(0)(0) have slightly different behaviors.
DBEngine(0)(0) returns a pointer to an object whereas CurrentDB() creates a
new object. This means that when you have two functions that set database
variables to DBEngine(0)(0), if the second function closes DBEngine(0)(0),
both functions are affected. When you return to the first function, you may
receive the following error message:

Object is no longer valid.
/quote

Each variable created with CurrentDb creates a new instance (object).
DBEngine(0)(0) returns a pointer to open copy of the current database. Two
variables create with DBEngine(0)(0) point to the same object, thus allowing
the error above.

As for the concurrency, I refer to Access 2000 Developer's Handbook by Getz,
et al. (pg 1482-4).

quote
There's one big difference between the two that may make your decision for
you: the database referred to by CurrentDb is always up to date with the
user interface. However, you must call the Refresh method before you use
any collection retrieved using a reference through DBEngine(0)(0). The
Refresh method is quite expensive (slow, that is) and will immediately
obviate any speed gains you made by choosing DBEngine(0)(0) over CurrentDb.
/quote

As I said, that's my best understanding based on my reading.

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com




"David W. Fenton" wrote in message
36.94...
"Roger Carlson" wrote in
:

CurrentDB always reflects changes made with the Graphical
Interface, DBEngine(0)(0) does not.

That is, the collections of DBEngine(0)(0) are initialized when the
database is opened and not refreshed when they are changed. Each
time you call CurrentDB, the collections are refreshed (this is why
it is many times slower than DBEngine(0)(0)).

Creating two database variables and setting them to CurrentDB
creates TWO instances of the currently opened database.

No, that's not true. CurrentDB creates nothing -- all it does is
return a pointer the the currently opened database.

DBEngine(0)(0) refers to the
open copy of the database, so setting two database variables will
point to the same instance of the open database.

This is simply not true.

Even though two database variables created with CurrentDB are in
separate instances, changes to one affect the other, but it has to
be refreshed in order to show changes made in the other.

Again, not really true. And the requirement to refresh collections
after you initialize a database variable is true regardless of
whether or not you use CurrentDB or DBEngine(0)(0). A refresh is
*not* required if you call CurrentDB again (as opposed to using a
reference cached in a database variable).

So it boils down to this:

1) A database variable set with CurrentDB will always be in sync
with the database currently opened in the Graphical interface.

NOT TRUE.

But if two or more
variables are created this way, they will need refreshing to stay
in sync.

...as do any cached references, regardless of how you initialize
them.

2) A database variable set with DBEngine(0)(0) will need to be
refreshed to stay in sync with the Graphical Interface. But if
two or more variables are created this way, they will always be in
sync.

The collections of DBEngine(0)(0) are updated when you open the
database and are never refreshed automatically during the session.
If you refresh the collections of DBEngine(0)(0), subsequent calls
to it will reflect the refreshed collections.

As far as I know, there is no speed difference between the two.
(I emphasize AFAIK.)

There are huge speed differences because a call to CurrentDB
refreshes all the collections, while DBEngine(0)(0) does not. It
doesn't matter, though, since it's not something you're going to
call in a loop where the speed difference would become significant.

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

Pete,

It suddenly occurred to me that you are not testing CurrentDb and
DBEngine(0)(0) equally. You are creating a database variable with
DBEngine(0)(0), but explicitly calling CurrentDb each time. Try creating a
database variable using CurrentDb and use that database variable to open
recordsets.

Dim thisDB As DAO.Database
Dim i As Long
Dim myMsg As String
Dim myRS(1000) As DAO.Recordset

Set thisDB = CurrentDb
For i = 1 To 1000
myMsg = " (CurrentDB, Internal)"
Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app
Next i

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com



"PeteCresswell" wrote in message
...
I've just been through the "Error3048: Cannot open any more databases"
thing.

Seems like DbEngine(0)(0).OpenRecordset allows one to open more tables
- either linked or internal - than CurrentDB().OpenRecordset.

That being the case, is there any reason to use CurrentDB
().OpenRecordset .... ever...?

e.g.
Public Function Error3048Test() As String
On Error GoTo Error3048Test_err

' PURPOSE: To explore relationship of "Error3048: Cannot open any more
databases" to
' CurrentDB() vs DBEngine(0)(0) when opening recordsets
'
' NOTES: 1) With a non-linked table, the number of open tables seems
tb
' 243 with CurrentDB and 403 with DbEngine
' 2) With a linked table, the number of open tables allowed
' appears to drop to 81 with CurrentDB and drop to 122
' with DbEngine(0)(0)

Dim thisDB As DAO.Database

Dim i As Long
Dim myMsg As String
Dim myRS(1000) As DAO.Recordset

Set thisDB = DBEngine(0)(0)

For i = 1 To 1000

' myMsg = " (CurrentDB, Internal)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (DbEngine Internal)"
' Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblDummy_SingleRecord", dbOpenDynaset) 'Internal to the app

' myMsg = " (CurrentDB, Linked)"
' Set myRS(i) = CurrentDb.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

myMsg = " (DbEngine, Linked)"
Set myRS(i) = thisDB.OpenRecordset("Select * from
zstblRecordNumbers", dbOpenDynaset) 'Linked

Next i

Error3048Test_xit:
Error3048Test = i & myMsg

On Error Resume Next
For i = 1 To 1000
myRS(i).Close
Set myRS(i) = Nothing
Next i
Set thisDB = Nothing
Exit Function

Error3048Test_err:
Resume Error3048Test_xit
End Function

"Roger Carlson" wrote in
:

Each variable created with CurrentDb creates a new instance
(object). DBEngine(0)(0) returns a pointer to open copy of the
current database. Two variables create with DBEngine(0)(0) point
to the same object, thus allowing the error above.

You're mixing up initializing a variable with using the two methods
directly. Once you've initialized a variable, in regard to
collections, they behave identically, though the variable
initialized with DBEngine(0)(0) won't reflect additions to the
collections that have been made since the database was opened.

Why anyone would even *have* two database variables initialized that
point to the database open in the current instance of Access, I
can't say. Why you would do that and then want to have some
interaction between the two baffles me even further.

The fact that all variables initalized with DBEngine(0)(0) points to
a single data structure maintained by the Access Application object
(or, if you choose, you can use the DAO instance of DBEngine) and
that variables initialized with CurrrentDB point to a different data
structure in memory (not a different instance of the database, but a
difference instance of the memory structure that represents the
database) is of no consequence for any real-world programming
situation that I know of.

As for the concurrency, I refer to Access 2000 Developer's
Handbook by Getz, et al. (pg 1482-4).

quote
There's one big difference between the two that may make your
decision for you: the database referred to by CurrentDb is always
up to date with the user interface.

Only if you use it directly. If you've initialized a variable with
it, its collections will not be updated unless you manually refresh
them.

However, you must call the Refresh method before you use
any collection retrieved using a reference through DBEngine(0)(0).


That is, using DBEngine(0)(0) directly you must refresh collections
each time you call it if you've added/deleted any objects to the
collections.

The
Refresh method is quite expensive (slow, that is) and will
immediately obviate any speed gains you made by choosing
DBEngine(0)(0) over CurrentDb.
/quote

As I said, that's my best understanding based on my reading.

Again, I think what you have written (as opposed to what you quote)
completely confuses direct use of the objects versus initializing a
database variable. A variable initialized with CurrentDB will need
to have its collections refreshed (if you've altered their contents)
in the same way that a direct use of DBEngine(0)(0) or of a variable
initialized with it will.

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