The simple workaround is to call CurrentDb prior to calling Workspaces for the first time:
Debug.Print CurrentDb.Name
Debug.Print Workspaces.Count
Debug.Print CurrentDb.Name
I'll take a shot at demystifying what's going on, but this is just my theory.
The relevant bits of the Access help file are as follows (for CurrentDb):
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.
And for the Workspaces collection:
When you first refer to or use a
Workspace object, you automatically
create the default workspace,
DBEngine.Workspaces(0).
It would seem that by creating the default workspace prior to the first call to CurrentDb, you are somehow causing CurrentDb to forget how it should work. It seems that instead of creating a new instance of the current database it just uses the one that's already lying around in the default Workspace.
Of course, this is all conjecture and I'm as curious as you to know the "real answer".
