I'm willing to believe that there may be cases where the method returning an object pointer won't do. But I've not seen an example yet. Just something in the immediate window that, as Chris says, is hardly relevant to the code I would ever write.
Try running these renditions of my Immediate Window demonstration in the VBE. You will see it is nothing to do with where it is run.
Code:
Option Compare Database
Option Explicit
Sub UseCurrentDb()
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
End Sub
Sub UsePointer()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.TableDefs(0)
MsgBox tdf.Name
End Sub
Although this is a simplistic example it does have relevance in real world coding.
My impression is that Properties of the object pointed to by CurrentDb and members of its Collections can be returned in an expression that includes it but it cannot be used entirely as one would refer to an object. Quite reasonably in my opinon, I sumise that this is because it isn't an object per se. Only by convertion to an object can it become fully functional.
This is the page where I discovered that CurrentDb is not an object but a Method of the Application.
And here is where I advised a solution to someone who was having trouble passing CurrentDb to a custom function. They say it worked when they changed to setting a pointer as I had advised.
It is certainly one of the most confusing subjects in Access and I by no means claim that my understanding is complete. It just works for me so far.
I called you on your comment, "Lets replace one perfectly good object variable with another." (implying it was unnecessary) not because I want to nitpick but because I know sometimes it is necessary to set the object variable. My goal is to further the understanding of it by all of us. I would be delighted if someone can broaden my knowledge.
I have often found that these "by the way" tangential discussions are the most revelaing. I don't take other's critical comments personally and I mean no malice in my posts but will offer constructive feedback if I think someone is losing sight of the main plot. I am sorry if this is unwelcome.
Sub UseCurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(0)
MsgBox qdf.Name
End Sub
So, yes there is this one situation where CurrentDb needs to be given a variable to hold it: when you want to assign one of its TableDefs to another variable. (Although it seems to me the fault probably lies in the TableDefs class not the CurrentDb method. That doesn't detract from the need.)
Still, it seems a little excessive to always assign CurrentDb to a variable, even for consistency. And anyway, as your code showed:
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
Microsoft don't consistently do it.
Which was my point.
But still, thanks for the example. I shall watch out for that.
So, yes there is this one situation where CurrentDb needs to be given a variable to hold it: when you want to assign one of its TableDefs to another variable. (Although it seems to me the fault probably lies in the TableDefs class not the CurrentDb method. That doesn't detract from the need.)
Interesting perspective particularly since it is not an issue with the QueryDefs and your general observation that the TableDef situation is an exception rather than the rule.
I would have come across the problem with TableDefs very early on as I used the technique to explore the properties of tables. Combined with the propensity of Microsoft to show the setting of the variable to CurrentDb led me further toward the conclusions I have made. The page at the link I provided strongly reinforced that perspective.
This issue seems worthy of an enquiry with Microsoft. What really does cause this anomaly?
Did I really, or do you think I missed the point? I don’t mind if someone thinks I missed the point but I regard it as quite arrogant when someone tries to elevate their own belief to the status of a fact.
I suppose I could begin every post with "In my opinion ... " but then surely this is understood in the context of any forum. It would be rather repetitive if we all did this, don't you think? Moreover I don't see you prefacing your posts with any such disclaimer.
For the vast majority of the ‘names’ on the www, self promotion is rampant. The www is almost an industry devoted to self promotion where demotion is not tolerated. Be particularly careful at UA; the rejection of truth has many guises.
I think it depends on how one approaches their web experience. Those of us who see it as a collaboration with the goal of sharing knowledge and observations are rarely troubled by those who adopt a self-promoting competitive mentality.
Moreover I have seen few examples of self-promotion and in particular such a strong reaction to perceived "demotion" that even approaches that displayed in Post #14 in this thread.
Moreover I have seen few examples of self-promotion and in particular such a strong reaction to perceived "demotion" that even approaches that displayed in Post #14 in this thread.
Galaxiom, I feel rather comforted that you have to go back so far in order to try and find something you think proves your point.
There might be people, both present and future, who can learn something from this thread so let me address this reply to them.
If we go back in time to post #60 in this thread we find:-
Set tdf = CurrentDb.TableDefs(0): ? tdf.Name
typed into the immediate window.
Yes it fails but that sort of failure can be manufactured almost at will. How to make it fail is in fact quite common knowledge on the www.
Now, we can all write code which fails; I do it almost on the hour evey hour.
The trick, if it really is a trick, is to try to understand why it fails.
------
To try and understand part of the answer, let’s have a look at something else in the immediate window:-
X=null: ?X
Null
Note that no error is raised, but the question remains; why no error?
The answer is that X was not declared and so it becomes a Variant and a Variant can be assigned a Null without error.
But that’s only part of the answer.
------
------
Let’s now look at another part of the answer in the immediate window.
Set tdf = CurrentDb.TableDefs(0)
tdf is not declared and so becomes a Variant.
But the aim is to assign CurrentDb.TableDefs(0) to a variable declared as a DAO.TableDef.
And, because we are using the immediate window, we have not achieved that aim.
Therefore, we are testing code under a condition we can not control.
------
------
The next part of the answer is to ask; how do we control the test condition?
Can we control the test condition in the immediate window if we use variables?
Can we control the test condition in the immediate window?
Can we control the immediate window?
If not then why use it to test code?
------
------
Part of the answer to those questions is; we can if we are careful.
Print CurrentDb.TableDefs(0).Name
Note that no variables are used.
CurrentDb is a function which returns a DAO.Database pointer.
TableDefs is a collection property accessable via that pointer.
(0) is an index into the TableDefs collection property.
Name is the name of the Table at Index(0) of the TableDefs collection property of the CurrentDb.
------
------
So we have to be very careful when testing code in the immediate window.
Print CurrentDb.TableDefs(0).Name
works but
Set tdf = CurrentDb.TableDefs(0): ? tdf.Name
doesn’t.
And the reason for this is that the code which failed was written incorrectly just to prove a point. Uncontrolled variables were declared and that forced a dissociation between those variables.
------
------
Now that we understand that undeclared variables in the immediate window and the dissociation of variables can cause problems we can move on.
For that we move to the VBA editor.
------
We could, as in post #66, try to prove something by writing this sort of stuff:-
Code:
Sub UseCurrentDb()
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
End Sub
And then try to prove that sort of stuff with this sort of stuff:-
Code:
Sub UsePointer()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.TableDefs(0)
MsgBox tdf.Name
End Sub
Well, the fact is that it does prove something. The problem is that what is being proved is that people can write faulty code to prove a point.
Let’s have another look at the faulty code:-
Code:
Sub UseCurrentDb()
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
End Sub
That code definitely type casts tdf correctly because tdf is now a DAO.TableDef.
But it does nothing to dissociate the variables.
Let’s take another look:-
Set tdf = CurrentDb.TableDefs(0)
then we use:-
tdf.Name
Set tdf = CurrentDb.TableDefs(0)
is a needless transfer of equivalence.
Why transfer CurrentDb.TableDefs(0) to tdf only to use tdf?
Why not use CurrentDb.TableDefs(0) directly?
So, if we are going to use tdf.Name why not use CurrentDb.TableDefs(0).Name directly?
The Sub then becomes:-
Code:
Sub UseCurrentDb()
MsgBox CurrentDb.TableDefs(0).Name
End Sub
There is now no variable declared and therefore there can be no dissociation between variables.
What’s more, no declared DAO reference is required in Tools>References.
CurrentDb.TableDefs(0).Name then become late bound DAO. But how?
Because it’s sitting there in Access Help under CurrentDb...
“Remarks
Note In Microsoft Access the CurrentDb method establishes a hidden reference to the Microsoft DAO 3.6 Object Library in a Microsoft Access database (.mdb).”
And in the same Access Help file it says right at the top:-
“The CurrentDb method returns an object variable of type Database that represents the database currently open in the Microsoft Access window.”
So one does not need to be some Google-spurt to find this stuff because it’s right there under the F1 key.
So, if we go back to the proposed VBA problem:-
Code:
Sub UseCurrentDb()
Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
End Sub
and the proposed VBA answer:-
Code:
Sub UsePointer()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb()
Set tdf = db.TableDefs(0)
MsgBox tdf.Name
End Sub
we can see a false ‘Dear Dorothy Dix’ question posed, followed by an incorrect answer.
The answer is not to compound needless code by adding more needless code.
The real answer is to understand the problem and strip out the needless junk which is actually causing the problem.
Code:
Sub UseCurrentDb()
MsgBox CurrentDb.TableDefs(0).Name
End Sub
And that’s it.
We are now back to the immediate window with the same functionality but with carefully written and hopefully understood code. It’s ‘Print CurrentDb.TableDefs(0).Name’ and not ‘Set tdf = CurrentDb.TableDefs(0): ? tdf.Name’.
If someone, anyone, wants to challenge that then please use provable code not some conveniently crafted flexible words.
And setting an object as the TableDefs Collection works too.
Code:
Dim tdfs As TableDefs
Dim tdf As TableDef
Set tdfs = CurrentDb.TableDefs
Set tdf = tdfs(0)
Debug.Print tdf.Name
This works:
Code:
Debug.Print CurrentDb.TableDefs(0).Fields(0).Name
But this doesn't:
Code:
Dim fld As DAO.Field
Set fld = CurrentDb.TableDefs(0).Fields(0)
Debug.Print fld.Name
Similarly with the Properties Collection of the Field. Yet any of these things will work provided we don't refer to an Item in a Collection without first instantiating a variable to either the database or a Collection higher up the chain.
Before you jump up and down again Chris please understand the point never was about trying to return the Name. The Print of the Name property is just a trival reference to the variable to show it breaks there.
We could easily put a break point after the line and look at the Locals window instead to show that the instantition has failed or perhaps more precisely that the variable has immediately gone out of scope.
If it couldn't be instantiated then why did not that line fail?
I tried declaring the object as a Global variable and it still failed, so scope doesn't really seem to be it either.
It is something to do with needing an object instantiated before refering to an Item in a Collection. What is the fundamental difference between the TableDefs Collection and the QueryDefs Collection?
I tend to agree that there is something "wrong" in the way properties are set up in the Table Class. For some reason they can't handle an Item reference at the end of the Methods' function argument.
Summing up: This is what fails even though it has no complaint about the right hand side of the expression:
Set tdf = CurrentDb.TableDefs(0)
The syntax looks as valid as dozens of other analogous expressions that do work (eg QueryDefs).
The same expression can be used as part of another.
But a Collection Item anywhere in the TableDefs object will not be be set as a variable unless one of the Collections in the chain above it is first set to a variable.
I think we need someone like Banana to try to explain this.
It is good to know the ones that don't work are actually an obscurity. I was just unlucky to hit it early on and thought it was a wider issue. Unfortunately it seems a bigger mystery now. At least before I thought I had an explanation that made sense.
Chris is way off the mark with the what he has posted about the Immediate Window. Particulaly this paragraph which doesn't even seem to make sense:
ChrisO said:
Uncontrolled variables were declared and that forced a dissociation between those variables.
>>The code behaves exactly the same in the Editor with fully typed variables so it obviously has absolutely nothing to do with where it is run.<<
That may or may not be true in this case but we had to move it to the VBE in order to find out.
And I didn’t move it, some bloke in post #66 did. And why did he move it? Well, I think you will have to ask him, but perhaps he needed to check the code in a real environment.
Hopefully the bloke in post #60 (Galaxiom) didn’t see the bloke in post #66 (GalaxiomAtHome) move his code else all hell would have broken loose.
>>I think we need someone like Banana to try to explain this.<<
Please, we don’t need another Banana, or another BananaRepublic or another Ben or Fred or Gertrude. Someone else is likely to Google it and come up with a link to Michael Kaplan who probably did not write the original software and therefore says what he thinks and posts a link to someone else who did not write the software and posts a link and…
GENTLEMEN, START YOUR GURUS.
Later on, after yet another 500 laps of the park going in circles, things aren’t any clearer. The green car didn’t win because the red car had a slightly under inflated left front tyre and would have won other than for that. Oh!; so the win was inconclusive. Sure was. OK, see you next Sunday and we can do it all again.
By all means read everything you can. But in the long run we need to be able to satisfy ourselves that it works properly under our circumstances and we do that by testing it.
And that is what we are doing here; we are testing it.
Option Compare Database
Option Explicit
Public Sub TestIt()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Debug.Print vbNewLine & "How many database references are there when we implicitly reference Currentdb?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
With CurrentDb
Debug.Print "Referencing CurrentDb: " & DBEngine(0).Databases.Count
End With
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we assign CurrentDb to a variable?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set db = CurrentDb
Debug.Print "Referencing db variable: " & DBEngine(0).Databases.Count
Set db = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we reference TableDef via CurrentDb?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set tdf = CurrentDb.TableDefs(0)
Debug.Print "Referencing tdf variable: " & DBEngine(0).Databases.Count
Set tdf = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we reference TableDef via db variable?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set db = CurrentDb
Debug.Print "Referencing db variable: " & DBEngine(0).Databases.Count
Set tdf = db.TableDefs(0)
Debug.Print "Referencing db and tdf variables: " & DBEngine(0).Databases.Count
Set db = Nothing
Debug.Print "db variable released: " & DBEngine(0).Databases.Count
On Error Resume Next
Debug.Print tdf.Name
If Err.Number Then
Debug.Print "Error reading tdf's name - " & Err.Number & ": " & Err.Description
End If
On Error GoTo 0
Set tdf = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we reference QueryDef via CurrentDb?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set qdf = CurrentDb.QueryDefs(0)
Debug.Print "Referencing qdf variable: " & DBEngine(0).Databases.Count
Set qdf = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we reference QueryDef via db variable?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set db = CurrentDb
Debug.Print "Referencing db variable: " & DBEngine(0).Databases.Count
Set qdf = db.QueryDefs(0)
Debug.Print "Referencing db and qdf variables: " & DBEngine(0).Databases.Count
Set db = Nothing
Debug.Print "db variable released: " & DBEngine(0).Databases.Count
On Error Resume Next
Debug.Print qdf.Name
If Err.Number Then
Debug.Print "Error reading qdf's name - " & Err.Number & ": " & Err.Description
End If
On Error GoTo 0
Set qdf = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
Debug.Print vbNewLine & "How many database references are there when we reference a TableDef and QueryDef via CurrentDb?" & vbNewLine
Debug.Print "Initial: " & DBEngine(0).Databases.Count
Set db = CurrentDb
Debug.Print "Referencing db variable: " & DBEngine(0).Databases.Count
Set tdf = db.TableDefs(0)
Set qdf = db.QueryDefs(0)
Debug.Print "Referencing db, tdf and qdf variables:" & DBEngine(0).Databases.Count
Set db = Nothing
Debug.Print "db variable released: " & DBEngine(0).Databases.Count
On Error Resume Next
Debug.Print tdf.Name
If Err.Number Then
Debug.Print "Error reading tdf's name - " & Err.Number & ": " & Err.Description
End If
Debug.Print qdf.Name
If Err.Number Then
Debug.Print "Error reading qdf's name - " & Err.Number & ": " & Err.Description
End If
On Error GoTo 0
Set qdf = Nothing
Debug.Print "qdf variable released: " & DBEngine(0).Databases.Count
On Error Resume Next
Debug.Print tdf.Name
If Err.Number Then
Debug.Print "Error reading tdf's name - " & Err.Number & ": " & Err.Description
End If
On Error GoTo 0
Set tdf = Nothing
Debug.Print "End: " & DBEngine(0).Databases.Count
End Sub
"On Error GoTo ...", "Exit <procedure>" and "Resume ..." statements automatically clears the error so Err.Clear in this case would be redundant since I do a Error GoTo 0 to narrow the scope of On Error Resume Next.
But anyway, indeed that shows under the hood what is going on with the tabledef variable. It doesn't hold open the CurrentDb reference in the same way that every other object does.
Presumably:
Dim tdf as TableDef
With CurrentDb
Set tdf = .TableDefs(0)
Debug.Print tdf.Name
End With
FWIW, it's not 'every other objects' --- for instance, Recordsets suffer from the same malady that TableDefs does with respects to holding onto its parent reference.
Brent had posted sometime in past where he suspect the differences could be attributed to what I forgot the specific terms but between "definitions" (e.g. QueryDefs, Indices, Relations) and "data" (e.g. TableDefs, Recordsets, Fields).
