473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

NeoPa
32,556 Expert Mod 16PB
My question, or point of discussion if you like, is why is the default Workspace object always described as DBEngine.Workspaces(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 :

Expand|Select|Wrap|Line Numbers
  1. 'WorkspaceFromDB() returns the Workspace object that contains dbSought.
  2. Public Function WorkspaceFromDB(ByVal dbSought As DAO.Database) As DAO.Workspace
  3.     Dim wsVar As DAO.Workspace
  4.     Dim dbVar As DAO.Database
  5.  
  6.     For Each wsVar In Workspaces
  7.         For Each dbVar In wsVar.Databases
  8.             If dbVar Is dbSought Then
  9.                 Set WorkspaceFromDB = wsVar
  10.                 Exit Function
  11.             End If
  12.         Next dbVar
  13.     Next wsVar
  14. End Function
Nov 18 '12 #1
23 7463
ADezii
8,834 Expert 8TB
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?
Nov 18 '12 #2
NeoPa
32,556 Expert Mod 16PB
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.
Nov 19 '12 #3
zmbd
5,501 Expert Mod 4TB
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.Workspaces(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 misunderstanding the code parser - I simply do not trust the developers not to muck things up with some new or changed library reference. :)
Nov 19 '12 #4
NeoPa
32,556 Expert Mod 16PB
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.
Nov 19 '12 #5
ADezii
8,834 Expert 8TB
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.Workspaces(?) Construct.
Nov 19 '12 #6
NeoPa
32,556 Expert Mod 16PB
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.
Nov 19 '12 #7
TheSmileyCoder
2,322 Expert Mod 2GB
Do any of you ever use anything BUT the default workspace? And if so for what purpose, and why not use the default workspace?
Nov 21 '12 #8
ADezii
8,834 Expert 8TB
Personally, I never had any reason to use anything but the Default Workspace.
Nov 21 '12 #9
NeoPa
32,556 Expert Mod 16PB
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.
Nov 22 '12 #10
GKJR
108 64KB
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.CreateNewWorkspace 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...

Expand|Select|Wrap|Line Numbers
  1. ?DBEngine.Workspaces.Count
  2. ?DBEngine(0)(0).Name
  3. ?DBEngine(0)(1).Name
  4.  
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.
Mar 11 '14 #11
NeoPa
32,556 Expert Mod 16PB
You can open multiple databases within the same workspace too. Only one can be the current database though ;-)
Mar 11 '14 #12
zmbd
5,501 Expert Mod 4TB
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.Workspaces(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.
Mar 11 '14 #13
GKJR
108 64KB
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
Expand|Select|Wrap|Line Numbers
  1. ?DBEngine(0).Name
  2. ?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?
Mar 12 '14 #14
zmbd
5,501 Expert Mod 4TB
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)
Mar 12 '14 #15
GKJR
108 64KB
I was browsing through the links you posted and they're pretty good resources- thanks for that. I made a bookmark in my browser for the index page.

I found that http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx goes well with this discussion along with the one you posted.

As long as it is clear why it isn't necessary to explicitly state DBEngine in a DAO reference then we're all on the same page.

"Does this also mean that you can't have multiple, simultaneous, OR overlapping transactions with out another workspace opened?" I'm still wondering about this though.
Mar 12 '14 #16
zmbd
5,501 Expert Mod 4TB
"Does this also mean that you can't have multiple, simultaneous, OR overlapping transactions with out another workspace opened?" I'm still wondering about this though.
That may be a topic for a new thread... you can always include a link back to this thread for context should it be needed.
Mar 12 '14 #17
GKJR
108 64KB
@NeoPa
I know this is an old thread, but it's an interesting topic. I meant to ask you does the procedure you posted give you the answer you need?

As a possible answer to the second part of your question:
"I'm looking for a way to determine the actual Workspace object involved when dealing with a Database object..."- Could you specifically create a workspace object and run your SQL batches from within it? This way you would know for sure which workspace it is in and you wouldn't need to call on the property.

One last thing before I stop pushing this thread. Regarding the last question I proposed that is still open, if you had to run multiple, simultaneous, and overlapping transactions, can you assume that a new workspace was created aside from the default workspace? If so, you would know to refer to the DBEngine(1).Name property (unless there was more than two workspaces that is).
Mar 13 '14 #18
NeoPa
32,556 Expert Mod 16PB
As this is specifically listed as a discussion thread it's fair to be a little more relaxed than usual about allowing other, relevant, points to be raised and discussed.

As the command is actually Workspace.BeginTrans without the possibility of identifying the specific transaction, it can be inferred that only one transaction can be active for any workspace at any one time. Ergo, multiple, simultaneous & overlapping transactions in a single workspace are all precluded.
Mar 13 '14 #19
NeoPa
32,556 Expert Mod 16PB
We cross-posted there.

Yes. The procedure, though clumsier than I'd like, gives the information I was after.
Mar 13 '14 #20
zmbd
5,501 Expert Mod 4TB
As the command is actually Workspace.BeginTrans without the possibility of identifying the specific transaction, it can be inferred that only one transaction
In the previously provided link:
•Use the BeginTrans, CommitTrans, and Rollback methods to manage nested transaction processing within a Workspace and use several Workspace objects to conduct multiple, simultaneous, and overlapping transactions.
Gives me the impression that the transactions become like nested if..then... so that it would be wise to have a means of tracking if you have started a transaction within a transaction...

Ofcourse, one could instance a new workspace, and then start the second transaction therein and so forth.
Mar 14 '14 #21
GKJR
108 64KB
This brings up an interesting point in the context of our discussion. If you can start a transaction within a transaction, are you in the same workspace? If so, is the first transaction still active?
Mar 14 '14 #22
zmbd
5,501 Expert Mod 4TB
GKJR
This brings up an interesting point in the context of our discussion. If you can start a transaction within a transaction, are you in the same workspace? If so, is the first transaction still active?
GKJR: Read the second quote block in my last post... it answers your question (^_^).
Mar 15 '14 #23
NeoPa
32,556 Expert Mod 16PB
I overlooked that possibility. Well done for clarifying that Z :-)
Mar 15 '14 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

15
by: John Salerno | last post by:
After my last post, I thought of another question as a result of the following: ------------------------------ Mike Meyer wrote: > John Salerno <johnjsal@NOSPAMgmail.com> writes: > > >>So...
33
by: Joshua D. Drake | last post by:
Hello, I think the below just about says it all: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Sincerely, Joshua Drake
8
by: RC | last post by:
In my Access 2002 form, I have a combo box and on the AfterUpdate event I use DoCmd.RunSQL ("UPDATE .... to update records in a table. When it starts to run I get a message "You are about to...
20
by: ctyrrell | last post by:
Does anyone have any idea how to recover from a run-time error 3002 which I get after creating a workspace 242 times? Or better yet, avoid getting it in the first place? I am creating a...
9
by: travisperkins03 | last post by:
Hi, I have read somewhere that C code sometimes cannot be compiled to be as efficient as FORTRAN, eg for matrix multiplication, because a C compiler cannot make the assumptions about arrays that...
3
by: Daniel Bass | last post by:
I'm just getting into the .Net experience from a VC++ (6.0) background, and am looking at the .Net languages. Just wondering what the differences are, in your opinion, between C# and VB, dispite...
2
by: david epsom dot com dot au | last post by:
I just wanted to test the behaviour of dao.dbEngine compared to dao.dbEngine.35 and dao.dbengine.36, but it's not there. It's always been there before, but it's been years since I looked. This...
135
by: Xah Lee | last post by:
Tabs versus Spaces in Source Code Xah Lee, 2006-05-13 In coding a computer program, there's often the choices of tabs or spaces for code indentation. There is a large amount of confusion about...
1
by: =?Utf-8?B?TmFyYXNpbWhhbQ==?= | last post by:
I have a VB program that uses the DBEngine.CreateWorkspace command to authenticate users in SQL Server. We are trying to move that from VB to C# and I am trying to understand what the equivalent is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.