Unbderstanding DBEngine(0)(0).TableDefs

They are not exactly the same.  Take a look here for an informed and complete explanation by guru Michael Kaplan...

http://groups.google.com/group/comp.databases.ms-access/msg/9fe98bb5d7cba5ea?hl=en&

BTW, opening other workspaces makes no difference.  CurrentDb is always a reference to the one database opened from File>Open or OpenCurrentDatabase, or from the command line.  This can't change until you close that database and open another one.

--
Graham Mandeno [Access MVP]

First: DBEngine(0)(0) is shorthand for this:

DBEngine.Workspaces(0)

So when you do this:

DBEngine(0)(0).TableDefs

You're asking for the Tabledefs Collection from the defalt Database of the default Workspace. Access is built using different collections, of course, and the top "level" in this case is the DBEngine. The DBEngine contains one or more Workspaces (generally only one, unless you add more), and the Workspaces collection contains several other collections (like Databases), and the Database collection cointains several collections (like TableDefs).

Currentdb is, basically, shorthand for DBEngine(0)(0).

In regards to the difference, this is from here: http://msdn.microsoft.com/en-us/library/bb237861.aspx

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 Visual Basic for Applications 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 Visual Basic for Applications DBEngine(0)(0)syntax, but you should consider making this modification to your code in order to avoid possible conflicts in a multiuser database.

<end paste>

DBEngine(0)(0) is much faster than Currentdb (about 5000 time faster). I've never timed it myself, but I've seen the results of many such tests on the internet, and I'd believe it.

According to Micheal Kaplan, one benefit CurrentDB has over DBEngine(0)(0) is that all collections will be always up to date - why this is I don't know, and Microsoft ain't telling, but if Kaplan says it about Access, you can go to the grave with it. If that's so then you've got your answer - when you're dealing with collections, and you want them to be up to date, use CurrentDB.

BTW, some of the above came from the link that Graham gave above. I've summarized, as that entire thread is a beast to read through - but very, very informative!

"when you're dealing with collections, and you want them to be up to date, use CurrentDB"

Or ...  Use the Refresh Method on

 DBEngine(0)(0).TableDefs,

>>  DBEngine(0)(0).TableDefs.Refresh.

What it really comes down to is ... The TableDefs Collection on the DBEngine object is *not* automatically refreshed when you "If I make changes to a table", whereas the TableDefs Collection off of CurrentDB is.

More importantly (and as denoted above) ... right from the A2003 Help File on the CurrentDB Method:

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

"DBEngine(0)(0) is much faster than Currentdb (about 5000 time faster)."

This would mainly apply if these we being used in a repetitive loop.  And the only reason DBEngine would be faster ... from the Microsoft Jet Database Engine Programmers Guide 2nd Edition is ...

"The CurrentDB function differs from the DBEnigne(0)(0) syntax in that it creates another instance of the current database and returns a reference to that instance, and it refreshes all the collections in the current database. On the other hand, the DBEngine(0)(0) syntax doesn't refresh any collections, so it may ... perform faster"

Another way to state what CurrentDB is: It's the instance of Access that is opened in the Access User Interface.

I'm noting that most of what I've said has been stated one way or another above, but I tried to put a slightly different perspective on it.

mx

mx

And here is a good related read on the subject ... especially noting the comments of Leigh Purvis and Harfang (Marcus Fisher):

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23355139.html

mx