Composite primary keys (1 Viewer)
- Thread starter DaveJHPT
- Start date
ChrisO
Registered User.
- Local time
- Today, 23:00
- Joined
- Apr 30, 2003
- Messages
- 3,202
Why create a variable at all?
Print CurrentDb.TableDefs(0).Name
More on the subject.
Apart from testing code, I can not remember the last time I had to create DAO database or DAO recordset object pointers. The vast majority of people still do it but all they are really doing is early binding to a reference that can be broken.
What happens on the www is; when you ask people why they late bind something they give you all the good reasons. When you ask them why they don’t late bind DAO they say, umm, well, ah, well you can’t umm, ah. I once reduced an MVP to him saying “I would never dream of creating a database without a reference to DAO”. My reply to that was that I don’t really care what he dreams about. I did not get a further reply.
On my SkyDrive site I currently have 23 demos and not one of them uses any references at all, including DAO.(Apart from the two I can’t remove, and I would if I could.) The Chess game has more code than you can poke a stick at and the passing of the Current Record through OpenArgs still does not need a reference set.
-----------
Another question:-
Why test code in an artificial environment (the debug window) where the code will not be actually running? Is it faster? Do people really write code that fast? Is faster really better? How much time do they spend going back to test their fast code in a real environment?
Set db = Currentdb: Set tdf = db.TableDefs(0): ? tdf.Name
That works in the debug window (Even without a DAO reference)
But it doesn’t work in the VBA editor (With or without a DAO reference)
Db is not declared so it won’t work unless we turn off Option Explicit.
But who wants to turn off Option Explicit in order to test their code?
-----------
I think we all get set in our ways. But I don’t think we get that old that we can’t think.
Chris.
Print CurrentDb.TableDefs(0).Name
More on the subject.
Apart from testing code, I can not remember the last time I had to create DAO database or DAO recordset object pointers. The vast majority of people still do it but all they are really doing is early binding to a reference that can be broken.
What happens on the www is; when you ask people why they late bind something they give you all the good reasons. When you ask them why they don’t late bind DAO they say, umm, well, ah, well you can’t umm, ah. I once reduced an MVP to him saying “I would never dream of creating a database without a reference to DAO”. My reply to that was that I don’t really care what he dreams about. I did not get a further reply.
On my SkyDrive site I currently have 23 demos and not one of them uses any references at all, including DAO.(Apart from the two I can’t remove, and I would if I could.) The Chess game has more code than you can poke a stick at and the passing of the Current Record through OpenArgs still does not need a reference set.
-----------
Another question:-
Why test code in an artificial environment (the debug window) where the code will not be actually running? Is it faster? Do people really write code that fast? Is faster really better? How much time do they spend going back to test their fast code in a real environment?
Set db = Currentdb: Set tdf = db.TableDefs(0): ? tdf.Name
That works in the debug window (Even without a DAO reference)
But it doesn’t work in the VBA editor (With or without a DAO reference)
Db is not declared so it won’t work unless we turn off Option Explicit.
But who wants to turn off Option Explicit in order to test their code?
-----------
I think we all get set in our ways. But I don’t think we get that old that we can’t think.
Chris.
You missed the point. It wasn't meant to be a practical piece of code but a demonstration of something that works and something that doesn't when referencing CurrentDb.
It provides a fast rudimentary test. That is one of the reasons that window is provided.
Moreover if one is in the middle of a real job it is better done in the Immediate Window than to add something to the project and accidentally save it.
Anyone experienced in the use of the Immediate Window will know what it is about and how its use differs from the editor. Anyone who doesn't and cares about the subject will ponder it for a bit, try some things, learn something and might even start using it. Win-win either way.
Exactly why I used the Immediate Window. And who wants to type out a whole procedure complete with variable declarations?
Chris, while I have utmost respect for your knowledge of this subject, sometimes I think you just like to be nit-pickingly critical just for the sake of it. Perhaps you could try looking at the context of a post instead of being so focussed on what you can knock down.
Some people don't need to be old to get set in their ways and some have never thought about anything much at all. I have always endeavoured to avoid getting set in my ways and you will generally find that I am quite conversant with multiple perspectives on any subject because I do think about the whys. My family brought me up to look for information and to think for myself.
ChrisO
Registered User.
- Local time
- Today, 23:00
- Joined
- Apr 30, 2003
- Messages
- 3,202
>>You missed the point.<<
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 believe you can write quite well and so make few allowances for what you write.
I am of the opinion that you write what you mean. I am also of the opinion that people who can craft a well worded answer should not flip that well crafted answer like a coin. Then if the well crafted answer comes down in their favour they accept it. But if it comes down in a way they did not intend they deny it, or say nothing.
That was also part of the answer given by ‘modest’ in the link I posted. Well crafted arrogance which could not be defended, so they ran away.
But that was 2006 and so here is a more recent one on a different subject. http://answers.microsoft.com/en-us/...abase-to/2bbd354a-8dee-49d4-aab4-e2e812d50d40
Not an inconsiderable talent but misused the <chuckle> in my opinion.
But look at the work it creates in order to try and explain the situation which had already been stated. And after going to the trouble of explaining it again look at the response…none.
Over the last 10 years, while I have been on the www, there have been many such occasions. So many it just isn’t funny anymore. There are some people who can dish it out but just can’t take it. They don’t know how to simply say ‘I stuffed up’. It simply does not appear to be in their vocabulary. It seems their perception of themselves outweighs all else. 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.
To be fair, there are also times that one finds a decent person (actually two in this link).
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/259447ac-2f0b-4aa7-b77c-f3594cea278f
It’s a long read but you may get my point.
Partial quote:-
>>“sometimes I think you just like to be nit-pickingly critical just for the sake of it.”<<
No! I would say:-
“I try to be critical if I think it lends some insight”
Such things as “Global variables are not reset on Error they are reset on Reset.”
That sort of critical statement lends insight and discriminates Error from Reset.
It may at the time be thought of as pedantic or overly critical. But over time the penny starts to drop and people may start to see the difference. If people continue to merge Error and Reset into some sort of collective blurry mirage then little is gained. But if people can clearly see the difference they can then use that difference in their favour.
That is just one example of being critical, there are many. But the point is the same; without the critique the facts remain blurred.
I have no expectation whatsoever of changing the world, but I will continue to chip away at it.
Chris.
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 believe you can write quite well and so make few allowances for what you write.
I am of the opinion that you write what you mean. I am also of the opinion that people who can craft a well worded answer should not flip that well crafted answer like a coin. Then if the well crafted answer comes down in their favour they accept it. But if it comes down in a way they did not intend they deny it, or say nothing.
That was also part of the answer given by ‘modest’ in the link I posted. Well crafted arrogance which could not be defended, so they ran away.
But that was 2006 and so here is a more recent one on a different subject. http://answers.microsoft.com/en-us/...abase-to/2bbd354a-8dee-49d4-aab4-e2e812d50d40
Not an inconsiderable talent but misused the <chuckle> in my opinion.
But look at the work it creates in order to try and explain the situation which had already been stated. And after going to the trouble of explaining it again look at the response…none.
Over the last 10 years, while I have been on the www, there have been many such occasions. So many it just isn’t funny anymore. There are some people who can dish it out but just can’t take it. They don’t know how to simply say ‘I stuffed up’. It simply does not appear to be in their vocabulary. It seems their perception of themselves outweighs all else. 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.
To be fair, there are also times that one finds a decent person (actually two in this link).
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/259447ac-2f0b-4aa7-b77c-f3594cea278f
It’s a long read but you may get my point.
Partial quote:-
>>“sometimes I think you just like to be nit-pickingly critical just for the sake of it.”<<
No! I would say:-
“I try to be critical if I think it lends some insight”
Such things as “Global variables are not reset on Error they are reset on Reset.”
That sort of critical statement lends insight and discriminates Error from Reset.
It may at the time be thought of as pedantic or overly critical. But over time the penny starts to drop and people may start to see the difference. If people continue to merge Error and Reset into some sort of collective blurry mirage then little is gained. But if people can clearly see the difference they can then use that difference in their favour.
That is just one example of being critical, there are many. But the point is the same; without the critique the facts remain blurred.
I have no expectation whatsoever of changing the world, but I will continue to chip away at it.
Chris.
VilaRestal
';drop database master;--
- Local time
- Today, 14:00
- Joined
- Jun 8, 2011
- Messages
- 1,046
Let's try and keep this friendly and not take people's assertions personally.
I would say your original points Galaxiom were a little bit nitpicky themselves (the examples were quite obscure) so a bit hypocritical to accuse Chris of that. What's more, they appear to be wrong:
"Try passing CurrentDb as an object to a function. It won't work.
But you can pass an object set to the value returned by CurrentDb."
OK, I did. This code in a module:
and this in a form:
Lo and behold: it works fine.
And as Chris says, CurrentDb.TableDefs(0).Name, works fine too. (And in fact the code sample you posted as a MS WTF, the deleting tables loop, is an example of Microsoft using CurrentDb in just this way and not consistently referencing it with db.)
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.
I would say your original points Galaxiom were a little bit nitpicky themselves (the examples were quite obscure) so a bit hypocritical to accuse Chris of that. What's more, they appear to be wrong:
"Try passing CurrentDb as an object to a function. It won't work.
But you can pass an object set to the value returned by CurrentDb."
OK, I did. This code in a module:
Code:
Option Compare Database
Option Explicit
Public Function DBtest(ByRef db As DAO.Database) As String
DBtest = db.TableDefs(0).Name
End Functionand this in a form:
Code:
Option Compare Database
Option Explicit
Private Sub Command0_Click()
MsgBox DBtest(CurrentDb)
End SubLo and behold: it works fine.
And as Chris says, CurrentDb.TableDefs(0).Name, works fine too. (And in fact the code sample you posted as a MS WTF, the deleting tables loop, is an example of Microsoft using CurrentDb in just this way and not consistently referencing it with db.)
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 SubAlthough 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.
http://msdn.microsoft.com/en-us/library/bb237861(v=office.12).aspx
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.
http://www.access-programmers.co.uk/forums/showthread.php?t=220101
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.
VilaRestal
';drop database master;--
- Local time
- Today, 14:00
- Joined
- Jun 8, 2011
- Messages
- 1,046
So I see. Thanks for the example.
Interestingly it works fine with QueryDefs:
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.
Interestingly it works fine with QueryDefs:
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.
- Local time
- Today, 09:00
- Joined
- Feb 19, 2002
- Messages
- 43,223
There is a difference between DBEngine(0)(0) and CurrentDB(). I found this article which I copied part of for convenience. Here's a link so you can read the whole thing where David offers an alternative. http://stackoverflow.com/questions/...verhead-when-using-currentdb-as-opposed-to-db
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?
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.
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.
RainLover
VIP From a land downunder
- Local time
- Today, 23:00
- Joined
- Jan 5, 2009
- Messages
- 5,041
Galaxiom
Do you realise that this was almost a year ago.
If this is the motivation behind this and other discussions, may I suggest that you build a bridge and get over it.
To me this looks bad.
Fair Dinkim, we are talking August 2011.
Do you realise that this was almost a year ago.
If this is the motivation behind this and other discussions, may I suggest that you build a bridge and get over it.
To me this looks bad.
Fair Dinkim, we are talking August 2011.
ChrisO
Registered User.
- Local time
- Today, 23:00
- Joined
- Apr 30, 2003
- Messages
- 3,202
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:-
And then try to prove that sort of stuff with this sort of stuff:-
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:-
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:-
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:-
and the proposed VBA answer:-
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.
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.
Chris.
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 SubAnd 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 SubWell, 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 SubThat 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 SubThere 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 Suband 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 Subwe 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 SubWe 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.
Chris.
VilaRestal
';drop database master;--
- Local time
- Today, 14:00
- Joined
- Jun 8, 2011
- Messages
- 1,046
It is fascinating to me though that
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
MsgBox tdf.Name
Next
works but
Dim tdf As TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
doesn't.
Some difference in how For Each and Set assigns tdf to the TableDef (and perhaps only when it comes to TableDefs).
Although I kind of agree with Chris in that I think it's an obscure error, it is one I'm glad to know about and avoid.
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
MsgBox tdf.Name
Next
works but
Dim tdf As TableDef
Set tdf = CurrentDb.TableDefs(0)
MsgBox tdf.Name
doesn't.
Some difference in how For Each and Set assigns tdf to the TableDef (and perhaps only when it comes to TableDefs).
Although I kind of agree with Chris in that I think it's an obscure error, it is one I'm glad to know about and avoid.
And setting an object as the TableDefs Collection works too.
This works:
But this doesn't:
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:
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.
Code:
Dim tdfs As TableDefs
Dim tdf As TableDef
Set tdfs = CurrentDb.TableDefs
Set tdf = tdfs(0)
Debug.Print tdf.NameThis works:
Code:
Debug.Print CurrentDb.TableDefs(0).Fields(0).Name
Code:
Dim fld As DAO.Field
Set fld = CurrentDb.TableDefs(0).Fields(0)
Debug.Print fld.NameSimilarly 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:
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.
ChrisO
Registered User.
- Local time
- Today, 23:00
- Joined
- Apr 30, 2003
- Messages
- 3,202
>>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.
Chris.
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.
Chris.
RainLover
VIP From a land downunder
- Local time
- Today, 23:00
- Joined
- Jan 5, 2009
- Messages
- 5,041
Chris
Luckilly you are not another 600 posts ahead otherwise Hell really would break loose.
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.
Luckilly you are not another 600 posts ahead otherwise Hell really would break loose.
Here is another example where someone was caught out by this "obscure" issue.
http://www.access-programmers.co.uk/forums/showthread.php?p=1159828&posted=1#post1159828
http://www.access-programmers.co.uk/forums/showthread.php?p=1159828&posted=1#post1159828
Some code to play with and ponder upon.
EDIT: modified the last part to make the point.
Code:
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 SubEDIT: modified the last part to make the point.
VilaRestal
';drop database master;--
- Local time
- Today, 14:00
- Joined
- Jun 8, 2011
- Messages
- 1,046
Shouldn't you Err.Clear before each test?
Or the first time there's an Err then every If Err after will return true?
Edit: Testing I get the impression On Error GoTo 0 does that. I prefer Err.Clear
Or the first time there's an Err then every If Err after will return true?
Edit: Testing I get the impression On Error GoTo 0 does that. I prefer Err.Clear
Last edited:
