Discussion: DAO.DBEngine.Workspaces() versus DAO.Workspaces()

[NeoPa]

My question, or point of discussion if you like, is why is the default Workspace object always described as DBEngine.Worksp aces(0) and not the simpler Workspaces(0) which, according to all I've discovered, is the same thing. Am I missing something important? I couldn't even find a reference that confirmed that the references are the same, although they are both from the DAO library.

I'm looking for a way to determine the actual Workspace object involved when dealing with a Database object. I'm surprised the DAO.Database object doesn't have a simple property pointing to the workspace it's within, but it doesn't. The best I have so far is :

'WorkspaceFromDB() returns the Workspace object that contains dbSought.
Public Function WorkspaceFromDB(ByVal dbSought As DAO.Database) As DAO.Workspace
    Dim wsVar As DAO.Workspace
    Dim dbVar As DAO.Database

    For Each wsVar In Workspaces
        For Each dbVar In wsVar.Databases
            If dbVar Is dbSought Then
                Set WorkspaceFromDB = wsVar
                Exit Function
            End If
        Next dbVar
    Next wsVar
End Function

[ADezii]

To the best of my knowledge, there is no easy way to determine the actual Workspace Object referenced by a specific Database. Your workaround looks like a good solution. Just out of curiosity, why do you need to determine the Workspace Object for a specified DB?

[NeoPa]

ADezii:
Just out of curiosity, why do you need to determine the Workspace Object for a specified DB?

I'm writing routines that execute batches of SQL commands that should be run as a single transaction. Transactions, and the methods that go with them, are things that work at the Workspace level, so, while the SQL must be executed on the Database object, the Transaction is defined by the related Workspace object. As such, the Database and the related Workspace objects are both required.

[zmbd]

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
First paragraph under the list:

When you first refer to or use a Workspace object, you automatically create the default workspace, DBEngine.Worksp aces(0).

It maybe the matter of being "more correct" in the description of the workspace.

I for one, whenever using this method, have always used the full construct much like I do with the DAO.* objects to ensure that there is no misunderstandin g the code parser - I simply do not trust the developers not to muck things up with some new or changed library reference. :)

[NeoPa]

That was my best guess Z. The linked page seems also to work on that basis in that it seems to use them interchangably, yet it doesn't ever explain that explicitly (which is pretty poor for an official page - apparently switching between them randomly with no explanation). If anyone knows where that is confirmed I'd be interested to see.

[ADezii]

I, like zmbd, have always fully qualified the Workspace Object with DBEngine to avoid any possible conflicts. Aside from brevity, I do not see any reason to not use the DBEngine.Worksp aces(?) Construct.

[NeoPa]

I'm still looking for confirmation that they are equivalent. I've seen articles where the assumption seems apparent, but none that explain the situation or even refer explicitly to their being equivalent references to the same collection.

As far as I can see from my own checking, Workspaces() is a DAO collection just as DBEngine is a DAO object. My experimenting has shown them to be equivalent in the extremely limited test environment (with simply the * Default Workspace * existing) that I've used.

[TheSmileyCoder]

Do any of you ever use anything BUT the default workspace? And if so for what purpose, and why not use the default workspace?

[ADezii]

Personally, I never had any reason to use anything but the Default Workspace.

[NeoPa]

Smiley:
Do any of you ever use anything BUT the default workspace? And if so for what purpose, and why not use the default workspace?

No. Not yet.

If I were to though, it would be to ensure that updates I applied were not counted as part of a transaction.

It should be bourn in mind however, that the Default Workspace and CurrentDb() are not used by the Access interface when dealing with the open database. Running action queries manually (or even via the DoCmd.RunSQL etc commands) will ensure that the changes are outside the scope of any defined transactions in the Default Workspace. This can be a bit of a gotcha if you have procedures, as I do, to do specific tasks that use this approach. I had to recode how my record deletions were invoked for this very reason.

[GKJR]

I had to do some brushing up from one of my books, but I think I have an answer to this if it's still open. The DBEngine is at the root of the hierarchy of the DAO Library. It is always assumed to be there if it's left out. If you use intellisense in VBA you can see that there are other properties and methods that would also assume the reference to DBEngine.

There are instances where you can have more than one workspace opened. My book goes on to say a couple reasons for doing this are "to gain fine control over how transactions behave, or to log on to an Access database as a different user connected to the same security Workgroup." I've never used this, but it sounds spot on with what you just mentioned NeoPa.

You can open a new database file from within VBA with an object variable type Workspace and the DBEngine.Create NewWorkspace and .append methods. It gets a little confusing because workspaces is a temporary collection and it only exists while your code is running. I didn't try doing this yet, but I wonder what would happen if you opened a new workspace and used this in the immediate window before closing out the object...

?DBEngine.Workspaces.Count
?DBEngine(0)(0).Name
?DBEngine(0)(1).Name

It is useful to know that you can open another database from within VBA. You could then use the DAO to access any of its data, as long as you specify which workspace you are working in.

[NeoPa]

You can open multiple databases within the same workspace too. Only one can be the current database though ;-)

[zmbd]

GKJR: That the DBENGINE is the root was basically covered (although maybe not in such depth) in post #4 Of course in that post I was using references to ACC/Office 2003, here's the updated version to ACC/Office 2010, and there is no change to this in the ACC/Office 2013 release:

Workspace Object (DAO)Office 2010

When you first refer to or use a Workspace object, you automatically create the default workspace, DBEngine.Worksp aces(0). The settings of the Name and UserName properties of the default workspace are "#Default Workspace#" and "Admin," respectively. If security is enabled, the UserName property setting is the name of the user who logged on.

It was also covered to some degree in the remaining posts as to the mulitple instances and contexts.

[GKJR]

Hi zmbd,
I did read the rest of the posts, although I only brushed through the link you gave before my first post. There still seemed to be some confusion about whether DBEngine and Workspaces() are the same or not. Just because the default workspace is under DBEngine itdoesn't mean there can't be other workspaces, and besides that doesn't really clarify what the DBEngine actually is. As the root of the hierarchy, everything else is below it so it isn't really necessary to refer to the DBEngine. As an analogy you can say that I am from USA on Earth or UK on Earth, but it is understood that we're all from Earth and it isn't necessary to say it every time. It doesn't mean that we're wrong when we do though.

Now if you were writing VBA for Access from another Office application, I wonder if it makes any difference if you leave out DBEngine.? Ultimately I think most of you guys are right in saying that you may as well put it in there so there is no confusion with libraries or parsers (don't know what these are) down the line.

I think I mis-wrote my tests for the immediate window. Looking at them again, they both refer to the first workspace, DBEngine(0) and the first and second databases within it. I think it should be

?DBEngine(0).Name
?DBEngine(1).Name

I'm going to do a little playing around soon and see what I get.

I guess the question now would be why would you open a database in a new workspace as opposed to opening one in the current default workspace? The MS link sums this up in their last bullet point towards the top: "... and use several Workspace objects to conduct multiple, simultaneous, and overlapping transactions." This really confuses me because why would you want to use simultaneous overlapping transactions? Does this also mean that you can't have multiple, simultaneous, OR overlapping transactions with out another workspace opened?

[zmbd]

Please read the link(s) I gave in some detail.

To use your analogy (and it breaks down quite quickly as such):
DBEngine is more like saying "Solar System"
WorkSpace is More like sayting "Earth", "Mercury"
The active database would be more like saying "USA", "UK"

So in the defaults of Access, it is assumed under the DAO object that you are talking about "Solar System" and not "Epsilon Eridani System"; thus when you establish the workspace "earth" is is assumed that you mean [Solar System]![Earth] and not [Epsilon Eridani System]![Earth] which if you will read the link(s) provided will be made clear (IMHO, MS could have done a better job explaining this, but there you have it)