Composite primary keys (1 Viewer)

"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

would work

And it does

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).

Banana said:

for instance, Recordsets suffer from the same malady that TableDefs does with respects to holding onto its parent reference.

Click to expand...

You mean: Set rs = CurrentDb.Recordsets(0)

Again, not something I've ever needed to do.

But yes, could be useful to know these peculiarities.

Thanks for clearing it up Banana.

That's what happen when I try to answer from top of my head.

Here's the post that I was thinking about WRT Brent's assessment.

>>It is a euphamism for "bug".<<[sic]

That pretty much sums it up. I’ve been programming for 36 years, using Access for 16 years, been on the www for 10 years and you are telling me that "undocumented feature" means a bug?

My question was; “What exactly do you mean by "undocumented feature"?
I wanted to know what you mean by it.
I wanted to know if you mean it’s a bug or if you mean it’s undocumented.

So now we know you mean it’s a bug, that question answered.

But is it a bug?

Someone went to the trouble of calling Banana.
Banana went to the considerable trouble of writing a reply.
Banana posted a link to another thread on another site.
That thread is 4 years old.
There is more than one person replying to that thread.
In that thread there is a link to another thread on another site dated Jan-1999
The sited behaviour of TableDef has been documented for at least 13 years.

------

1. We can not say the behaviour is undocumented. If someone finds it obscure it is usually because they have not read the documentation. However, the documentation is a tautology in the sense of it being a ‘description in the disguise of a definition’. (Please be careful when Googling that; I think that definition of tautology goes back to Plato and I doubt if Plato ever personally contributed anything to a Wiki. One example would be “Survival of the fittest.” Which has been reduced to “Survival of that which has that which enables it to survive.” A circular argument based on a description which has no definition to break the argument out of the description circle. In other words, another 500 laps around the park to avail.)

So what I think is happening here is that many people over the years are supplying descriptions of the fault, if it really is a fault, but they are not supplying a definition of that fault. In other words, they are supplying many different ways to reproduce the ‘fault’ without saying why the ‘fault’ occurs. They are supplying yet another ‘description in the disguise of a definition’.

Who would know why it occurs? It seems to me that the best people to ask would be the people who wrote it. But would you get an answer or would some non disclosure agreement get in the way? Who really knows? It has been documented for at least 13 years and the discussions still flourish. Another 500 laps of the park.

It’s great for post counts but it really doesn’t solve anything.

2. Is it a bug? Well, if we look at the link as posted by Banana we see something by LPurvis in post #9
>>So I suppose the question is - even if it is data schema objects - why is it like this?
I just wish MS would document their reasons for things like this. What about their implementation in DAO makes it so...?

Though I've hastily used the term myself in the past I do think "Bug" is too harsh (Brent - remember our defending Jet's late expression evaluation a while back? I'd rate this more of a bug than that lol ;-).
However I find it a somewhat "poorly documented implementation".<<

------

If we are going down the track of requiring links to other sites then we should be prepared to read everything. Read the words, read the links and read the date/time stamps too.

This behaviour is documented and it has been documented for many years. It might be thought to be a bug but, even if it is, we should not expect Microsoft to fix it. We can expect DAO 3.6 to be free of bugs else those bugs would have been fixed after all these years.

DAO 3.6 behaves the way it does and that has been documented. A failure to be able to use its behaviour is a failure to read the available documentation. A failure to understand why it behaves the way it does require someone who knows why it was written that way. We are not likely to get an answer from such a person, perhaps due to a NDA. Posting links to other ‘gurus’ on another site only includes more ‘hows’ to do it. It does not answer the why of the situation.

Even if I take part in such a thread the same still applies. The why is not resolved.
All it becomes is another 500 laps around the park; see you next Sunday with the same non-result.

------

The ‘description in the disguise of a definition’ pervades the www.
And it has also happened in this thread.
Code has been written which describes the documented condition.
The code is faulty because it propagates a known, faulty, documented condition.
There is little point in "writing faulty code to prove a point" if the point has been documented for many years and no new point is made. If no new point is made then it simply becomes another 500 laps around the park.

------

Without doubt, people are at different levels of development on the www.
Some people are new and some people are old. We do not expect the new to know everything nor should we expect that of the old; Access is a broad brush. But a problem arises when the enthusiasm of the new tempts them into criticizing the old.

I would hope that the new would read more, consider more and write less.

Chris.

re undocumented

this means what it says, doesn't it.

some behaviour that is not officially acknowledged, and therefore is not guaranteed to always work - or which could be changed in the future.


eg - using the msys tables directly. and assuming it will always be managed in the same way. what if MS decided to implement storage within the msys tables differently, but rewrote functions that interface with them.

DAO would still be reliable, but your apps that use msys tables directly would no longer be so.


eg. testing if a string is null by
if somestring = "", as oppsed to if somestring=vbnullstring.

if MS decided to store strings differently in access maybe anything that "assumed" a certain storage mechanism might not work correctly.


eg - going back to the days when the visible screen (80x25 bytes) was mapped to a physical area of memory, and you could do wonderful things by just moving 2000/4000 bytes of memory on to and off the stack. But once the screen gets managed in a different way, the code no longer works.

I would think that if it is Undocumented then it doesn't exist.

Doesn't Microsoft have a good reputation for posting Bugs. They may choose not to do anything because a new version is on the way, but if it is a Bug they usually acknowledge its existance.

So IMHO this current subject is not a Bug and is in fact expected under certain conditions. Just because you or I expect something else does not mean it is wrong.

To quote the late great Sir Karl Popper:

"When I speak of reason or rationalism, all I mean is the conviction that we can learn through criticism of our mistakes and errors, especially through criticism by others, and eventually also through self-criticism. A rationalist is simply someone for whom it is more important to learn than to be proved right; someone who is willing to learn from others — not by simply taking over another's opinions, but by gladly allowing others to criticize his ideas and by gladly criticizing the ideas of others. The emphasis here is on the idea of criticism or, to be more precise, critical discussion. The genuine rationalist does not think that he or anyone else is in possession of the truth; nor does he think that mere criticism as such helps us achieve new ideas. But he does think that, in the sphere of ideas, only critical discussion can help us sort the wheat from the chaff. He is well aware that acceptance or rejection of an idea is never a purely rational matter; but he thinks that only critical discussion can give us the maturity to see an idea from more and more sides and to make a correct judgement of it."

I'm not sure that fits very comfortably with the assertion that:

"But a problem arises when the enthusiasm of the new tempts them into criticizing the old.

I would hope that the new would read more, consider more and write less."

I would hope everyone reads more and considers more. Old and new alike (myself included).

I don't agree with "write less" at all. I trust nobody is printing this thread or so short of bandwidth for a few extra words to be a problem. And if brevity is important (which I think it is for us all, old and new alike, within reason - we should all try to be concise):

I've just gone to the trouble of reading the nearly thousand words you've just written Chris. It is one of the least concise things I've ever read. And the only point I can gather from it that you're trying to make is: this thread is a waste of time ("If no new point is made then it simply becomes another 500 laps around the park")

And I disagree. Do you demand every thread is a ground-breaking piece of new information? Do you regard anything that isn't as a waste of time? What precisely do you think the purpose of this website is?

I started this current discussion by moaning about over-use of Set db = CurrentDb.

Galaxiom kindly pointed out a situation where it was necessary. Through discussion and experimentation, more facts about those 'obscure' cases were shown. (And it was I who used the word obscure because I do consider it obscure. I rarely if ever need to bother with TableDefs. The word obscure is not strictly defined and I'm free to use it as I see fit. To me it is obscure. It's a matter of opinion and therefore no point debating that.)

Definitions are always descriptions. In language and science, nothing is ever truly defined. Every definition is circular. For example:

The definition of space: The infinite extension of the three-dimensional region in which all matter exists

The definition of region: A large, usually continuous segment of a surface or space; area

The definition of dimension: A measure of spatial extent, especially width, height, or length

Time, matter/energy, mind/sentience, existence. All impossible to define and the basis of every other definition in the universe. Ergo, nothing is truly defined and never will be. (There is no such thing as épisteme.)

So, I for one don't get hung up about distinctions between definitions and descriptions because there really isn't any: a definition is just a description that appears not to describe anything else (and often its just totally arbitrary) until we find or even imagine something else that fits that description too. Occam's Razor plays a part too when two descriptions are vying for the mantle of 'definition' but it is subservient to empirical observation and it guarantees nothing.

The important thing is that people learn and enjoy. That is what philosophy means (the love of knowledge) and what this website is fundamentally about.

So, I'm sorry you regard it as a waste of time. But I don't, therefore it isn't: It just takes one person to regard it as valuable for it to have value. Nobody's making you read it Chris.

VilaRestal

>>I would hope that the new would read more, consider more and write less.<<

What that means is that there are such things as the F1 key, site search and Google.

However, it does increase post counts and site traffic so I guess it can’t be all bad.

Chris.

What about the case where each of two fields included as part of the composite key is not unique, but the combination of the two fields is unique? I cannot get such a composite key to work, but I recall from many years ago we could do it in DB2. Help.

Thanks for getting us back on topic Earl.

To create a composite key in Access 2007 (I forget about earlier versions and I think 2010 is the same):

Open the table design. In the Design ribbon there is the Indexes button, click that.

Enter a new Index name in the left column, select the first field to be used in the middle column.
Set the Unique property (in the footer of the Indexes window) to Yes
In the row underneath that leave the name blank (that means it's the same index as the one above) and choose the other field in the middle column.

So you have something like:

PrimaryKey------ID
NewIndex-------Field1
--------------------Field2

Thank you to VilaRestal for the initial suggestion. I tried it and it works so long as the key will actually be unique.

Earl

It is usually better to set this before entering Data.

Another way to set the Key is to hold the Ctrl Key while selecting the fields then click on the lightning bolt.