Separate PK in Jxn Tbl?

[Neil]

Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

[Phil Stanton]

I always use just the 2 primary keys but....
If I were a library lending 2 undiffentiatable copies of 1 book to the same
person, I suppose I would need an Autonumber PK plus A BookID and
BorrowerID.

Phil

"Neil" <no****@nospam.netwrote in message
news:6k***************@newssvr14.news.prodigy.net. ..

Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a
junction table with a separate PK consisting of an autonumber field, and
then the two fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

[David Cressey]

"Neil" <no****@nospam.netwrote in message
news:6k***************@newssvr14.news.prodigy.net. ..

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

With a PK consisting of the FKs that reference the tables being joined.

Is that what you meant?

[John W. Vinson]

On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" <no****@nospam.netwrote:

>So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make multifield
foreign keys all that easy to use (e.g. you can't use them in a combobox
without some messy code).

If I do so, I will always specify a unique (non-primary) Index on the two
parent foreign key fields.

John W. Vinson [MVP]

[Roy Hann]

"Bob Badour" <bb*****@pei.sympatico.cawrote in message
news:47**********************@news.aliant.net...

From that last anecdote, one can conclude one of two things: 1) gin was
only a small part of the overal liquor purchase or 2) you were too drunk
to notice.

Actually I got the kids to buy it on the way home from school. :-)

Roy

[Neil]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.comwrote in message
news:78********************************@4ax.com...

On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" <no****@nospam.netwrote:

>>So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make
multifield
foreign keys all that easy to use (e.g. you can't use them in a combobox
without some messy code).

If I do so, I will always specify a unique (non-primary) Index on the two
parent foreign key fields.

John W. Vinson [MVP]

Good to know. That makes sense, about needing the PK to refer separately to
the junction table, if that situation exists.

[Larry Linson]

"Roy Hann" <sp*******@processed.almost.meatwrote

Actually I got the kids to buy it on the way home from school. :-)

That could have happened where I grew up -- it was a dry county, but liquor
was available from bootleggers and a few people who ran their own stills.

[Jamie Collins]

On Jan 23, 1:41 am, "Tony Toews [MVP]" <[e-mail]:

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key.

The theorists will argue. I don't care.

Would you care for a pragmatist to offer a counter argument?

I never seek to add columns where an attribute does not exist in the
reality being modelled; sometimes I do end up adding something
'artificial' but only when there is a "good data modelling" reason for
doing so.

In the scenario described, there is a two-column "all key" table which
means it is in 5NF. From a "data modelling" point of view I can't see
a case for adding an 'artifical key'.

Jamie.

--

[Dustin W. Jones]

On Jan 22, 3:26 pm, "Neil" <[e-mail]:

Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

I've done both. I try to use natural keys where applicable, usually
both PK's as the primary for the junction table.

[Tony Toews [MVP]]

Jamie Collins <ja**********@xsmail.comwrote:

>The theorists will argue. I don't care.

Would you care for a pragmatist to offer a counter argument?

You're no pragmatist.

tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[lyle fairfield]

"Brian Selzer" <br***@selzer-software.comwrote in
news:l1*****************@nlpi069.nbdc.sbc.com:

Only an idiot would have a rule for no particularly good reason. Only
an imbecile would follow such a rule. A strong argument can be made
for using autonumber primary keys--especially if the target DBMS
doesn't support FOR EACH ROW triggers--but to just blythely add them
for no particularly good reason is a recipe for disaster. A clear
understanding of how and when they can be used and why is critical or
you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

[Larry Linson]

"David Cressey" <cr*******@verizon.netwrote

Sometimes, theory IS practical. (Some would say always).
A pragmatic person would at least listen to the arguments
of theorists before dismissing them.

In this case, of course, it is immaterial, because Tony is correct that
Jamie is not a pragmatist, at least not demonstrably so in his posts that I
have observed. Is there a category of "argumentist"?

Larry

[JOG]

On Jan 24, 2:01 am, lyle fairfield <[e-mail]:

"Brian Selzer" <[e-mail] innews:l1*****************@nlpi069.nbdc.sbc.com:

Only an idiot would have a rule for no particularly good reason. Only
an imbecile would follow such a rule. A strong argument can be made
for using autonumber primary keys--especially if the target DBMS
doesn't support FOR EACH ROW triggers--but to just blythely add them
for no particularly good reason is a recipe for disaster. A clear
understanding of how and when they can be used and why is critical or
you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

Ah, the joys of cross-posting. Different expectations as to precision,
definitions, decorum, etc... well they're all just going to lead to
immensely fruitful conversations!

>
I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

And there you have it. There is no way there is going to be a sensible
discussion between RM and MS Access experts. Let the tirade of
misunderstanding and ego begin.

>
Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

Let me start things off, and see how everyone politely rectifies any
ensuing confusion! Let me see...how about a database with Marriages:
{id, husband, wife, church} - the PK being the autonumbered id - where
a church clerk accidentally enters a marriage twice, because the
schema isn't using the natural {husband, wife} key. Hey, for all I
know duplicates are allowed in Access anyhow...

[Tony Toews [MVP]]

"Brian Selzer" <br***@selzer-software.comwrote:

>Only an idiot would have a rule for no particularly good reason. Only an
imbecile would follow such a rule. A strong argument can be made for using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster.

My reasons are, in my opinion, good reasons. Not great but good. You don't like
them? Tough.

>A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

Umm, not that you care I'm sure but my web pages on Microsoft Access corruptions
http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive resource on the
web. And there have never been any Access corruptions during to autonumber primary
keys that I can recall. And I've likely read just about every posting on that topic
in the last eight or ten years in the comp.databases.ms-access and the
microsoft.public.access.* newsgroups.

However my knowledge is practical not theoretical.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

Bob Badour <bb*****@pei.sympatico.cawrote:

>>>The theorists will argue. I don't care.

Sometimes, theory IS practical. (Some would say always). A pragmatic person
would at least listen to the arguments of theorists before dismissing them.

The theorists won't argue. The theorists will simply point out the
inherent stupidity of abdicating thought for simplistic recipes. The
invincibly ignorant won't care. They never do.

Hey, I thought you had plonked me.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Brian Selzer]

"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn************************@216.221.81.119...

"Brian Selzer" <br***@selzer-software.comwrote in
news:l1*****************@nlpi069.nbdc.sbc.com:

>Only an idiot would have a rule for no particularly good reason. Only
an imbecile would follow such a rule. A strong argument can be made
for using autonumber primary keys--especially if the target DBMS
doesn't support FOR EACH ROW triggers--but to just blythely add them
for no particularly good reason is a recipe for disaster. A clear
understanding of how and when they can be used and why is critical or
you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

It is not the auto-number primary key that causes corruption: it is its
misuse. Many will fail to add the unique constraints on the other candidate
keys. This is how you can end up with more than one employee with the same
social security number.

[Tony Toews [MVP]]

JOG <jo*@cs.nott.ac.ukwrote:

>Let me see...how about a database with Marriages:
{id, husband, wife, church} - the PK being the autonumbered id - where
a church clerk accidentally enters a marriage twice, because the
schema isn't using the natural {husband, wife} key.

And of course two different males with the same name, John Smith have never, ever
married two females with the same name, Jane Doe.

My brother has actually met his "twin" Same first, middle and last name. And same
birth date. Causes no end of trouble as the other guy has been a guest of Her
Majesty involuntarily a number of times. (That is the other guy has been in
prison.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

"Stuart McCall" <sm*****@myunrealbox.comwrote:

>2. The name 'id' is instantly recognisable for what it is (a rare thing for
a 2-char name).

Although I don't use exactly that as my primary key name. I use the initials of the
table in front if the ID.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

>4. When 'id' is used for all PK names, building criteria strings in code can
take up a lot less room.

However duplicate field names can be more troublesome in queries and code as you must
disambiguate them.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Brian Selzer]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:6l********************************@4ax.com...

"Brian Selzer" <br***@selzer-software.comwrote:

>>Only an idiot would have a rule for no particularly good reason. Only an
imbecile would follow such a rule. A strong argument can be made for
using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster.

My reasons are, in my opinion, good reasons. Not great but good. You
don't like
them? Tough.

So now they're good reasons? In your earlier post, you said they weren't
good reasons. Can't you make up your mind? You also haven't stated your
reasons. How can I like them or not like them? I don't know them!

>>A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

Umm, not that you care I'm sure but my web pages on Microsoft Access
corruptions
http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive
resource on the
web. And there have never been any Access corruptions during to
autonumber primary
keys that I can recall. And I've likely read just about every posting on
that topic
in the last eight or ten years in the comp.databases.ms-access and the
microsoft.public.access.* newsgroups.

I was not speaking of corruption due to disk failures; I was instead
referring to permitting garbage into the database due to the misuse of
auto-number primary keys.

However my knowledge is practical not theoretical.

I gained most of my knowledge the hard way as well, but that doesn't mean
that one shouldn't seek to understand and apply the theory.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[JOG]

On Jan 24, 7:03*am, "Brian Selzer" <[e-mail]:

"Tony Toews [MVP]" <[e-mail] in messagenews:6l********************************@4ax .com...

"Brian Selzer" <[e-mail]:

>Only an idiot would have a rule for no particularly good reason. *Onlyan
imbecile would follow such a rule. *A strong argument can be made for
using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster.

My reasons are, in my opinion, good reasons. *Not great but good. *You
don't like
them? *Tough.

So now they're good reasons? *In your earlier post, you said they weren't
good reasons. *Can't you make up your mind? *You also haven't stated your
reasons. *How can I like them or not like them? *I don't know them!

No, it looks like Tony's reasons are secret, and may only be gleaned
from a romantic evening of fine wine and barry white.

>

>A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

Umm, not that you care I'm sure but my web pages on Microsoft Access
corruptions
http://www.granite.ab.ca/access/corruptmdbs.htmare the definitive
resource on the
web. * And there have never been any Access corruptions during to
autonumber primary
keys that I can recall. *And I've likely read just about every postingon
that topic
in the last eight or ten years in the comp.databases.ms-access and the
microsoft.public.access.* newsgroups.

I was not speaking of corruption due to disk failures; I was instead
referring to permitting garbage into the database due to the misuse of
auto-number primary keys.

An actual example I experience springs to mind - I have witnessed a
database where student projects were recorded via a schema of Project
Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id).
None of the partnerships were aware of any "id" in the real world, and
simply submitted their partnership choices on paper to admin. A
clerical error resulted in 2/3 of the data being entered twice, which
left a lot of people flapping about the number of markers required
until the error was found. If the schema had used the natural {RoleA,
RoleB} key there would have been no issue.

But then for all I know, MS Access might allow duplicates anyhow....

>

However my knowledge is practical not theoretical.

I gained most of my knowledge the hard way as well, but that doesn't mean
that one shouldn't seek to understand and apply the theory.

Tony
--
Tony Toews, Microsoft Access MVP
* Please respond only in the newsgroups so that others can
read the entire thread of messages.
* Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
* Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/- Hide quoted text -

- Show quoted text -

[lyle]

On Jan 23, 10:29 pm, JOG <[e-mail]:

On Jan 24, 2:01 am, lyle fairfield <[e-mail]:

"Brian Selzer" <[e-mail] innews:l1*****************@nlpi069.nbdc.sbc.com:

Only an idiot would have a rule for no particularly good reason. Only
an imbecile would follow such a rule. A strong argument can be made
for using autonumber primary keys--especially if the target DBMS
doesn't support FOR EACH ROW triggers--but to just blythely add them
for no particularly good reason is a recipe for disaster. A clear
understanding of how and when they can be used and why is critical or
you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

Ah, the joys of cross-posting. Different expectations as to precision,
definitions, decorum, etc... well they're all just going to lead to
immensely fruitful conversations!

I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

And there you have it. There is no way there is going to be a sensible
discussion between RM and MS Access experts. Let the tirade of
misunderstanding and ego begin.

I thought we were being peaceful here in CDMA, despite the unnecessary
references to idiot and imbecile.

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

Let me start things off, and see how everyone politely rectifies any
ensuing confusion! Let me see...how about a database with Marriages:
{id, husband, wife, church} - the PK being the autonumbered id - where
a church clerk accidentally enters a marriage twice, because the
schema isn't using the natural {husband, wife} key. Hey, for all I
know duplicates are allowed in Access anyhow...

Many indexes are allowed in JET/ACE and many other db engines to which
Access might connect.
In addition to the PK index on the ID field, I would create a unique
non-null index on husband and wife, if I wanted to restrict couples
from marrying each other more than once.

[Jamie Collins]

On Jan 24, 2:01 am, lyle fairfield <[e-mail]:

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

There was a notorious bug in Access2000 where an auto-number values
would get duplicated:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database
http://support.microsoft.com/kb/257408

There was anecdotal evidence that this would happen even when a PK has
been used on the column Presumably the PK would get dropped in the
process; I can't quite believe that a Jet PK could ever contain
duplicates. That said, I was surprised to discover that NULL values
can exists in a Jet NOT NULL column e.g.

Sub NullInANotNullColumn()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Test (" & vbCr & _
"ID INTEGER);"
..Execute Sql
Sql = _
"INSERT INTO Test (ID) VALUES (NULL);"
..Execute Sql
Sql = _
"SELECT ID FROM Test;"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString(, , , , "<<NULL>>")
rs.Close
Sql = _
"ALTER TABLE Test ALTER" & _
" ID INTEGER NOT NULL;"
..Execute Sql
Set rs = .OpenSchema(4, _
Array(Empty, Empty, "Test", "ID"))
MsgBox rs.Fields("IS_NULLABLE").Value
Sql = _
"SELECT ID FROM Test;"
Set rs = .Execute(Sql)

' If Test.ID IS_NULLABLE = false, why
' does it still contain a NULL ?!
MsgBox rs.GetString(, , , , "<<NULL>>")
Sql = _
"INSERT INTO Test (ID) VALUES (NULL);"
..Execute Sql
End With
Set .ActiveConnection = Nothing
End With
End Sub

In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

A primary key auto-number is not a prerequisite of update and delete
actions via ADO. In fact, updating a Jet table with no unique
constraint/index and no PK using an ADO recordset still works with a
cursor side location provided the _data being updated_ can be uniquely
identified in the table using the compound of its attributes; with a
server-side cursor (which is more usual for ADO in Access) the data
does not need to unique _at all_.

Here's some VBA code to demo this behaviour (to test with a server
side cursor, comment out the line .CursorLocation = 3):

Sub ADONaturalKey()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String

' Note this table has no constraints
' (no UNIQUE, no PK) and no indexes
Sql = _
"CREATE TABLE Test (ID INTEGER," & _
" data_col INTEGER NOT NULL)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (1, 1)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (2, 2)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (3, 3)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (NULL, 4)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (NULL, 5)"
..Execute Sql
Sql = _
"SELECT ID, data_col " & vbCr & "FROM Test"

Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString(, , , , "<<NULL>>")
rs.Close
End With
End With
With rs
..CursorLocation = 3
..CursorType = 2
..LockType = 3
..Open

' Update some data
..MoveLast
..Update Array("ID", "data_col"), Array(1, 1)
..MovePrevious
..Update "data_col", 55
..MoveFirst

' Note that update was successful and
' that the rows are no longer unique
..MoveFirst
MsgBox rs.GetString(, , , , "<<NULL>>")

' Update an attribute on a
' non-duplicated row
..MoveFirst
..MoveNext
..Update "data_col", 99

' Note that update was successful and
' even though the rows are no longer unique
..MoveFirst
MsgBox rs.GetString(, , , , "<<NULL>>")

' Update an attribute on a
' duplicated row
..MoveLast
On Error Resume Next
..Update "data_col", 666
MsgBox Err.Description
End With
End Sub

Jamie.

--

[lyle]

On Jan 24, 1:27 am, "Brian Selzer" <[e-mail]:

"lyle fairfield" <[e-mail] in message

news:Xn************************@216.221.81.119...

"Brian Selzer" <[e-mail] in
news:l1*****************@nlpi069.nbdc.sbc.com:

Only an idiot would have a rule for no particularly good reason. Only
an imbecile would follow such a rule. A strong argument can be made
for using autonumber primary keys--especially if the target DBMS
doesn't support FOR EACH ROW triggers--but to just blythely add them
for no particularly good reason is a recipe for disaster. A clear
understanding of how and when they can be used and why is critical or
you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

It is not the auto-number primary key that causes corruption: it is its
misuse. Many will fail to add the unique constraints on the other candidate
keys. This is how you can end up with more than one employee with the same
social security number.

The defense rests.

[Jamie Collins]

On Jan 24, 11:09 am, Jamie Collins <[e-mail]:

Tony's reasons are secret, and may only be gleaned
from a romantic evening of fine wine and barry white.

Hmm, I once tried this with Tony [TIC]

I also tried it with Bob Badour [TIC] but he merely dismissed my
simplistic recipes and wanted more plonk.

Jamie.

--

[Stuart McCall]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:2c********************************@4ax.com...

"Stuart McCall" <sm*****@myunrealbox.comwrote:

>>2. The name 'id' is instantly recognisable for what it is (a rare thing
for
a 2-char name).

Although I don't use exactly that as my primary key name. I use the
initials of the
table in front if the ID.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tony...onventions.htm

>>4. When 'id' is used for all PK names, building criteria strings in code
can
take up a lot less room.

However duplicate field names can be more troublesome in queries and code
as you must
disambiguate them.

Disambiguating is exactly what you're doing by prefixing id with your
table's initials. If you use short (read abbreviated) table names as I tend
to, then it's a non-issue. And you only need to disambiguate when there's a
potential clash. Apples and oranges AFAIC.

[Rick Brandt]

"Brian Selzer" <br***@selzer-software.comwrote in message
news:sd*****************@nlpi068.nbdc.sbc.com...

It is not the auto-number primary key that causes corruption: it is its
misuse. Many will fail to add the unique constraints on the other candidate
keys. This is how you can end up with more than one employee with the same
social security number.

This statement can be made generic...

[SomeNoun] is a [Bad/Good] idea because stupid people [Will/WillNot] do
[SomeVerb].

Perhaps the *actual* problem is stupid people or more generously "people who do
stupid things".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[lyle]

On Jan 24, 6:09 am, Jamie Collins <[e-mail]:

He picks up the salt and, just as he was about to
apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't
you going to test it for seasoning first? I think you'll find it is
just fine." He replied, "I always add salt to my food. Why? No
particular good reason. One of my rules is that all food needs extra
salt."

Thank you. I've learned that Tony and I share two rules.

[David Cressey]

"JOG" <jo*@cs.nott.ac.ukwrote in message
news:2b**********************************@d4g2000p rg.googlegroups.com...
On Jan 24, 7:03 am, "Brian Selzer" <[e-mail]:
(quote)
An actual example I experience springs to mind - I have witnessed a
database where student projects were recorded via a schema of Project
Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id).
None of the partnerships were aware of any "id" in the real world, and
simply submitted their partnership choices on paper to admin. A
clerical error resulted in 2/3 of the data being entered twice, which
left a lot of people flapping about the number of markers required
until the error was found. If the schema had used the natural {RoleA,
RoleB} key there would have been no issue.

But then for all I know, MS Access might allow duplicates anyhow....
(/quote)

I have begun to mess around with MS Access, as a retirement hobby, after
working professionally with some SQL DBMS products.

AFAIK, MS Access enforces the no duplicates rule, and the no missing data
rule, for every PK that's declared. There are other ways to express these
constraints, but the easiest way to get them is to declare a PK.

For that reason, I prefer not to create a new ID with an autonumber for any
junction table. Tony have have his reasons for going the other way. Until
I know what they are, I remain unpersuaded.

The fact tables in a star schema have the same property as a junction table,
except that the number of FKs that are components of the PK may be larger
than 2, and generally is larger.
I've just started messing with star schemas in MS Access. It's too soon for
me to offer even a guess as to whether this is a smart idea or a stupid
idea. All I know is that it will provide some cheap amusement for my
retirement.

[Jamie Collins]

On Jan 24, 6:43 pm, "David Cressey" <[e-mail]:

I've just started messing with star schemas in MS Access. It's too soon for
me to offer even a guess as to whether this is a smart idea or a stupid
idea. All I know is that it will provide some cheap amusement for my
retirement.

Please post you findings here ;)

Jamie.

--

[Tony Toews [MVP]]

JOG <jo*@cs.nott.ac.ukwrote:

>I just think you're all damn brave for using Access in the first
place.

Why? It works and works well. If you have too many users or remote users bolt on a
SQL Server backend. Now you can have thousands of users.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[David W. Fenton]

JOG <jo*@cs.nott.ac.ukwrote in
news:ff**********************************@t1g2000p ra.googlegroups.com
:

I just think you're all damn brave for using Access in the first
place.

That just goes to show you haven't got a clue about what MS Access
actually is.

Typical.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tony Toews [MVP]]

"Larry Daugherty" <La********************@verizon.netwrote:

>This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?) years.
In the meantime OP is probably trying to hide the matches with which
he started the fires....

<chuckle Yup, it's amazing how this all happens. To me the particularly amusing
part is that we're generally quite civil in our discussions that stay in the Access
groups.

I'm saddened, although not surprised, at the comments from a few regulars from the
c.d.theory newsgroup using words such as idiot, imbecile and invincibly ignorant.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

Jamie Collins <ja**********@xsmail.comwrote:

>Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

There was a notorious bug in Access2000 where an auto-number values
would get duplicated:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database
http://support.microsoft.com/kb/257408

A bug in the first versions of Jet 4.0. Long since fixed. <shrug>

And that didn't actually corrupt the database. It was unusable for inserting records
but not corrupted. Although that's splitting hairs.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[JOG]

On Jan 24, 8:53 pm, "Tony Toews [MVP]" <[e-mail]:

JOG <[e-mail]:

I just think you're all damn brave for using Access in the first
place.

Why?

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

....but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

It works and works well. If you have too many users or remote users bolt on a
SQL Server backend. Now you can have thousands of users.

Or I could just use a decent database architecture in the first place
(and I am lucky enough to be able to), that can cope with more that 10
users (I mean 255 *cough*). Thats why I salute your dedication in the
face of all that superior db technology. I mean, we all know that the
client really wants to use oracle, and yet its you that stoutly has to
deal with his corner cutting as best you can.

I certainly don't mean to upset you....Well okay, maybe there's just a
bit of friendly ribbing in there ;)

>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

[Larry Daugherty]

Every dog gets one bite. Then they go into the bs/Kill file.

Did you note the net contribution to the subject at hand? Their
commentary serves only to identify and characterize *themselves*

My hope is to make meaningful contributions to those who are actually
developing applications or learning to do so.

Keep up the good work!
--
-Larry-
--

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:fm********************************@4ax.com...

"Larry Daugherty" <La********************@verizon.netwrote:

This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?)

years.

In the meantime OP is probably trying to hide the matches with

which

he started the fires....

<chuckle Yup, it's amazing how this all happens. To me the

particularly amusing

part is that we're generally quite civil in our discussions that

stay in the Access

groups.

I'm saddened, although not surprised, at the comments from a few

regulars from the

c.d.theory newsgroup using words such as idiot, imbecile and

invincibly ignorant.

>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Bob Badour]

Larry Daugherty wrote:

Every dog gets one bite. Then they go into the bs/Kill file.

Did you note the net contribution to the subject at hand? Their
commentary serves only to identify and characterize *themselves*

My hope is to make meaningful contributions to those who are actually
developing applications or learning to do so.

Your hope is futile in the face of your ignorance and laziness.

Keep up the good work!

I'll try.
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:fm********************************@4ax.com...

>"Larry Daugherty" <La********************@verizon.netwrote:

>>This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?) years.
In the meantime OP is probably trying to hide the matches with which
he started the fires....

>>
<chuckle Yup, it's amazing how this all happens. To me the

particularly amusing

>part is that we're generally quite civil in our discussions that

stay in the Access

>groups.

Ignorance is bliss.

>I'm saddened, although not surprised, at the comments from a few

regulars from the

>c.d.theory newsgroup using words such as idiot, imbecile and

invincibly ignorant.

Precious few pleasant ways exist to express unpleasant truths. However,
intellectual honesty requires the expression of truths regardless of
appeal to oneself or to others.

[Tony Toews [MVP]]

JOG <jo*@cs.nott.ac.ukwrote:

>Why?

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

...but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

<chuckle>

>It works and works well. If you have too many users or remote users bolt on a
SQL Server backend. Now you can have thousands of users.

Or I could just use a decent database architecture in the first place
(and I am lucky enough to be able to), that can cope with more that 10
users (I mean 255 *cough*). Thats why I salute your dedication in the
face of all that superior db technology. I mean, we all know that the
client really wants to use oracle, and yet its you that stoutly has to
deal with his corner cutting as best you can.

But in many situations why go to the extra effort of Oracle or <coughSQL Server
required? I seldom create systems that will be used by more than ten people. Using
Access in such an environment is a simpler install and simple updating. No DBA
required and very little IT admin.

>I certainly don't mean to upset you....Well okay, maybe there's just a
bit of friendly ribbing in there ;)

<smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

JOG <jo*@cs.nott.ac.ukwrote:

>I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

...but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

BTW I didn't realize that there were people in the theory newsgroup who actually
wanted to discuss the issues in a reasonable fashion.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Tony Toews [MVP]]

Bob Badour <bb*****@pei.sympatico.cawrote:

<chuckle Yup, it's amazing how this all happens. To me the

particularly amusing

part is that we're generally quite civil in our discussions that

stay in the Access

groups.

Ignorance is bliss.

Politeness goes a long way.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[JOG]

On Jan 25, 2:23 am, "Tony Toews [MVP]" <[e-mail]:

JOG <[e-mail]:

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

...but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

BTW I didn't realize that there were people in the theory newsgroup who actually
wanted to discuss the issues in a reasonable fashion.

Then consider yourself corrected ;) One person's posting style does
not a news group make (and FWIW if you can get past that posting style
there is a lot of value to be had imo). I had a look at your own
website and saw the effort you have put in to help other people, so
kudos for that. But this is partly why I was so suprised at your
"That's my rule, no good reason and if anyone disagrees sod 'em" post.

I have given an example of where using an artifical key broke a
database, and I'd stoutly argue that _hidden_ attributes are
dangerous, period. I'm dubious as to how they simplify queries (they
may shorten them, but not reduce their complexity as far as I can
see), but I can also think of instances where they opposite would be
the case. However, I am of course happy to be illuminated by some
examples, if any access people want to convince me that using
artificial keys on every table I create is a good thing...

J.

>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

[Jamie Collins]

On Jan 23, 12:45 am, "Neil" <[e-mail]:

I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make
multifield
foreign keys all that easy to use (e.g. you can't use them in a combobox
without some messy code).

Good to know. That makes sense, about needing the PK to refer separately to
the junction table, if that situation exists.

Why do you say "needing the PK"? JohnV no doubt makes a good case when
considering bound controls in Access but I would point out that he
also said the PK is "never necessary, but it can be handy".

Jamie.

--

[Jamie Collins]

On Jan 25, 1:13 am, "Tony Toews [MVP]" <[e-mail]:

I'm saddened, although not surprised, at the comments from a few regulars from the
c.d.theory newsgroup using words such as idiot

"Clearly the person is an idiot and is better off employed at a 7-11."
http://groups.google.com/group/micro...3c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/micro...5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/micro...c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/micro...edf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/micro...2d3ed992e9a2ec

Jamie.

--

[Jamie Collins]

On Jan 25, 12:59 pm, JOG <[e-mail]:

I had a look at your own
website and saw the effort you have put in to help other people, so
kudos for that. But this is partly why I was so suprised at your
"That's my rule, no good reason and if anyone disagrees sod 'em" post.

I too would like to pay tribute here to Tony Toews Access MVP, he does
help a lot of people and does not deserve a hard time (same for Larry
Linson, great guy, kind of the grandfather of this group, I like to
think, though I still wonder what I did to offend him). I too read his
website a while back and there's some good stuff in there and some
links to some great chuckle-some comedy. Which leads me nicely...

Sorry to spoil anyone's fund but am I the only one to spot the
duplicitous (pun intended, natch) nature of this post and other
similar ones recently i.e. where he *seems* to treat people he should
care about (community regulars, newbies, his own clients, etc) with
contempt? I think he knew he was lighting the blue touch paper of this
thread by saying, "I don't care" and I might be partly responsible for
this because I did kind of give him a good response to the same trick
last week. Come on people, lighten up! Tony is being light-hearted,
tickling your ribs. And I congratulate him for it, things can get dull
around here :)

Jamie.

--

[rkc]

Jamie Collins wrote:

On Jan 25, 1:13 am, "Tony Toews [MVP]" <[e-mail]:

>I'm saddened, although not surprised, at the comments from a few regulars from the
c.d.theory newsgroup using words such as idiot

"Clearly the person is an idiot and is better off employed at a 7-11."
http://groups.google.com/group/micro...3c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/micro...5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/micro...c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/micro...edf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/micro...2d3ed992e9a2ec

Jamie.

Oops.

[Neil]

Bob,

Here's a programming tip for you: prozac.
"Bob Badour" <bb*****@pei.sympatico.cawrote in message
news:47**********************@news.aliant.net...

Larry Daugherty wrote:

>Every dog gets one bite. Then they go into the bs/Kill file.

Did you note the net contribution to the subject at hand? Their
commentary serves only to identify and characterize *themselves*

My hope is to make meaningful contributions to those who are actually
developing applications or learning to do so.

Your hope is futile in the face of your ignorance and laziness.

>Keep up the good work!

I'll try.
"Tony Toews [MVP]" <tt****@telusplanet.netwrote in message
news:fm********************************@4ax.com...

"Larry Daugherty" <La********************@verizon.netwrote:

>This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?)
years.
In the meantime OP is probably trying to hide the matches with which
he started the fires....

>
<chuckle Yup, it's amazing how this all happens. To me the

particularly amusing

part is that we're generally quite civil in our discussions that

stay in the Access

groups.

Ignorance is bliss.

I'm saddened, although not surprised, at the comments from a few

regulars from the

c.d.theory newsgroup using words such as idiot, imbecile and

invincibly ignorant.

Precious few pleasant ways exist to express unpleasant truths. However,
intellectual honesty requires the expression of truths regardless of
appeal to oneself or to others.

[Neil]

"Jamie Collins" <ja**********@xsmail.comwrote in message
news:08**********************************@c23g2000 hsa.googlegroups.com...

On Jan 23, 12:45 am, "Neil" <[e-mail]:

I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make
multifield
foreign keys all that easy to use (e.g. you can't use them in a
combobox
without some messy code).

Good to know. That makes sense, about needing the PK to refer separately
to
the junction table, if that situation exists.

Why do you say "needing the PK"? JohnV no doubt makes a good case when
considering bound controls in Access but I would point out that he
also said the PK is "never necessary, but it can be handy".

OK, I'll rephrase:

"That makes sense, about the PK coming in handy to refer spearately to the
junction table, if that situation exists."

Better? :-)

[Neil]

"Larry Daugherty" <La********************@verizon.netwrote in message
news:ur**************@TK2MSFTNGP04.phx.gbl...

In the meantime OP is probably trying to hide the matches with which
he started the fires....

Indeed. :-|

[Bob Badour]

David Cressey wrote:

<CD********@fortunejames.comwrote in message
news:a1**********************************@n20g2000 hsh.googlegroups.com...
On Jan 25, 9:12 am, Jamie Collins <[e-mail]:
(quote)
What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

(end quote)

Simplicity is in the eye of the beholder.

I tend to disagree. I suspect one can quantify simplicity and complexity.

I think it's simpler to rely on constraints enforced by the DBMS to prevent
duplicate entries
than it is to write code to accomplish the same thing.

Using the dbms uses fewer tools, fewer concepts, fewer computational
models, fewer structures, fewer machines. I suggest the observed
simplicity is more than a matter of perspective or opinion.

[further demonstrations of simplicity snipped]

[Marshall]

On Jan 26, 4:26 am, "David Cressey" <[e-mail]:

>
When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.

Yes, exactly.

One of the greatest benefits, and one of the fundamental
differences between how SQL treats data and how
(most) conventional programming languages treat data
is that in SQL we specify data by its value, instead of by
location. I often observe that superfluous keys in the field
are an attempt to make SQL data have an address, to
make it behave the way the programmer's mental model
(perhaps influenced by years of using pointers) does.
Marshall

[Bob Badour]

Roy Hann wrote:

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%2****************@TK2MSFTNGP05.phx.gbl...

[snip]

>>And finally, a for your request of asking me to convince you that I'm
properly understand the problem here of to etablish that I'm credible: I
can tell you that I have absolutely no intention of doing it

And yet you have greatly increased your credibility with this post.

I disagree. You give him too much credit.
I still

disagree with what you've said, but I can see you know more about what
you're talking about than it seemed before. Before, I thought you were
ignorant and uncurious. Now I see you are merely wrong. :-)

I suspect that is wishful thinking on your part.