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 : - '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
23 7463
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 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.
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. :)
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.
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.
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.
Do any of you ever use anything BUT the default workspace? And if so for what purpose, and why not use the default workspace?
Personally, I never had any reason to use anything but the Default Workspace.
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.
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... -
?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 32,556
Expert Mod 16PB
You can open multiple databases within the same workspace too. Only one can be the current database though ;-)
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 2010When 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.
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 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)
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.
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.
@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).
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.
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.
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.
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?
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 (^_^).
NeoPa 32,556
Expert Mod 16PB
I overlooked that possibility. Well done for clarifying that Z :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |