Table w/ Autonumber AND Cascade update

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

I saw it as wanting to misuse a feature (tirggers) that would have
legitimate uses, if it existed. ;] I think well-designed Access databases
are too uncommon; I rarely get to work on them, anyway. I also rarely get
to design. I get called in to fix them after they are so far along that the
client feels that they have too much invested to start over. I'm pretty low
on the food chain, so I just do what I'm told: fix the messed up database in
XX hours, or less.

Personally, I don't like surrogate keys at all. I wish Access didn't have
autonumber, but that likely wouldn't force people to design tables with good
natural keys--they'd just simulate autonumber in VBA. :[

Thanks for your insight.

"Michael (michka) Kaplan [MS]" <michkap@online .microsoft.com> wrote in
message news:4026a37a@n ews.microsoft.c om...[color=blue]
> "Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote...
>[color=green]
> > It seems impossible to make all of this happen in the back-end mdb,[/color]
> though.
>
> Correct.
>[color=green]
> > Or is it?[/color]
>
> It is impossible, given the way that Access/Jet is being [mis]used here.
> Though perhaps one could look at doing a proper schema redesign as soon as
> feasible (the facts that (1) a table can exist with *no* candidate keys,[/color]
and[color=blue]
> (2) an autonumber is being used as data points to at least two of those
> flaws. The flaws are what in fact leads up to being unable to do what one
> wants to in a file server database product).
>
>
> --
> MichKa [MS]
> NLS Collation/Locale/Keyboard Development
> Globalization Infrastructure and Font Technologies
>
> This posting is provided "AS IS" with
> no warranties, and confers no rights.
>
>
>[/color]

[Tony Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>I saw it as wanting to misuse a feature (tirggers) that would have
>legitimate uses, if it existed. ;] I think well-designed Access databases
>are too uncommon; I rarely get to work on them, anyway. I also rarely get
>to design.[/color]

Whereas designing new database has been pretty much all I've been doing for years.
[color=blue]
>Personally, I don't like surrogate keys at all. I wish Access didn't have
>autonumber, but that likely wouldn't force people to design tables with good
>natural keys--they'd just simulate autonumber in VBA. :[[/color]

There are definite arguments for having good natural keys. However I tried this once
and found that
1) Access doesn't do a good job with these. In particular subforms and other places
where wizards are expecting only one field for a key they get really confused with
multiple fields in the key.

That said this was back in A2.0. That said Tom Ellison has done an excellent job of
promoting natural keys elsewhere and I think he said that the wizards still don't do
a good job.

2) sometimes when you got down enough tables you started having many fields in the
primary keys. It got to be quite cumbersome in the relationships diagram.

3) It is extra work when doing record inserts via VBA,ec.

4) You're right about the simulating of autonumber in VBA. <smile>

5) Hey, even SQL Server has identity keys. <smile>

6) Personal preference is that I like the simplicity of autonumber keys.

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 Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>Ah, yes. Right you are. :] I actually need both (update the PK and the
>FK) at times, but I'll take what I can get.[/color]

Ah, good.

But now we're back to why would you need to update the PK?

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

[David W. Fenton]

Re: Table w/ Autonumber AND Cascade update

Bas Cost Budde <bas@heuveltop. org> wrote in
news:c05tp8$140 $1@news2.solcon .nl:
[color=blue]
> Lee Cichanowicz wrote:[color=green]
>> You have clearly identified problem, Bas: No triggers in Access!
>> What a tremendous shortcoming...I mean, product feature, that is.[/color]
>
> There are *some* triggers but that is of no use to the programmer.
> Cascase update and delete must be trigger-powered; autonumber
> itself is some sort of trigger.[/color]

Um, no, it's not.

AutoNumber is a special kind of default value.

Nothing more, nothing less.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

Answer: Design.

Even so, I don't understand the horror of the idea of changing a primary key
value on occassion. Access lets you do it for all field types other than
Autonumber, right? As long as you're using cascade update, what's the big
deal? As long as the value is still unique, and the change gets propagated
throughout related tables, I don't see a dilemma. Granted, if it's being
done a lot, it would seem to indicate a less than ideal PK choice.

, Lee


"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:ibad20l52v qbgbjmkt4qhufsv mm1mpe16g@4ax.c om...[color=blue]
> "Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
>[color=green]
> >Ah, yes. Right you are. :] I actually need both (update the PK and the
> >FK) at times, but I'll take what I can get.[/color]
>
> Ah, good.
>
> But now we're back to why would you need to update the PK?
>
> 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[/color]

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

Excellent insight. Thanks for taking the time.

I have developed a rather complex database, in SQL Server, for a personal
project (that may hit the Web this summer) and I haven't used any surrogate
keys. The db is to the point where some of the compound keys are rather
hefty, but it doesn't bother me enough to start using identity fields. I
appreciate the benefit of working with meaningful key values, versus
meaningless integers. I'd rather not have meaningless data in my database.

"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:bdad209d83 5urgcheogt3r3vm umv7kdvh9@4ax.c om...[color=blue]
> "Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
>[color=green]
> >I saw it as wanting to misuse a feature (tirggers) that would have
> >legitimate uses, if it existed. ;] I think well-designed Access[/color][/color]
databases[color=blue][color=green]
> >are too uncommon; I rarely get to work on them, anyway. I also rarely[/color][/color]
get[color=blue][color=green]
> >to design.[/color]
>
> Whereas designing new database has been pretty much all I've been doing[/color]
for years.[color=blue]
>[color=green]
> >Personally, I don't like surrogate keys at all. I wish Access didn't[/color][/color]
have[color=blue][color=green]
> >autonumber, but that likely wouldn't force people to design tables with[/color][/color]
good[color=blue][color=green]
> >natural keys--they'd just simulate autonumber in VBA. :[[/color]
>
> There are definite arguments for having good natural keys. However I[/color]
tried this once[color=blue]
> and found that
> 1) Access doesn't do a good job with these. In particular subforms and[/color]
other places[color=blue]
> where wizards are expecting only one field for a key they get really[/color]
confused with[color=blue]
> multiple fields in the key.
>
> That said this was back in A2.0. That said Tom Ellison has done an[/color]
excellent job of[color=blue]
> promoting natural keys elsewhere and I think he said that the wizards[/color]
still don't do[color=blue]
> a good job.
>
> 2) sometimes when you got down enough tables you started having many[/color]
fields in the[color=blue]
> primary keys. It got to be quite cumbersome in the relationships diagram.
>
> 3) It is extra work when doing record inserts via VBA,ec.
>
> 4) You're right about the simulating of autonumber in VBA. <smile>
>
> 5) Hey, even SQL Server has identity keys. <smile>
>
> 6) Personal preference is that I like the simplicity of autonumber keys.
>
> 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[/color]

[Tony Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>Answer: Design.[/color]

But what design? Why?
[color=blue]
>Even so, I don't understand the horror of the idea of changing a primary key
>value on occassion. Access lets you do it for all field types other than
>Autonumber, right? As long as you're using cascade update, what's the big
>deal? As long as the value is still unique, and the change gets propagated
>throughout related tables, I don't see a dilemma. Granted, if it's being
>done a lot, it would seem to indicate a less than ideal PK choice.[/color]

It's more along the lines of there should be no need to change an autonumber primary
key. A natural key sure, when, for example, a company changes their name and thus
the company code changes from ACM001 to XYZ013.. But an autonumber field should
never be visible by the user and they should never care what it's value is.

Of course when a company code changes from ACM001 to XYZ013 there can be many tens of
thousands of records in many tables which can change. And that's one of my problems
with natural keys in this fashion. You have to ensure all the updates get committed
before anyone else can use the database. You almost have to do this kind of think
after hours.

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 Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>Excellent insight. Thanks for taking the time.
>
>I have developed a rather complex database, in SQL Server, for a personal
>project (that may hit the Web this summer) and I haven't used any surrogate
>keys. The db is to the point where some of the compound keys are rather
>hefty, but it doesn't bother me enough to start using identity fields. I
>appreciate the benefit of working with meaningful key values, versus
>meaningless integers. I'd rather not have meaningless data in my database.[/color]

Fair enough. This can be a matter of personal preference.

Whereas I'm very comfortable with meaningless keys. They don't bother me a bit.

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

[Lyle Fairfield]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote in news:D-
adnQqlFIXBdLvdR Vn-jg@comcast.com:
[color=blue]
> I have developed a rather complex database, in SQL Server, for a personal
> project (that may hit the Web this summer) and I haven't used any surrogate
> keys. The db is to the point where some of the compound keys are rather
> hefty, but it doesn't bother me enough to start using identity fields. I
> appreciate the benefit of working with meaningful key values, versus
> meaningless integers. I'd rather not have meaningless data in my database.[/color]

Autonumber and identity fields are not meaningless. They identify each row of
a table. Database engines require this identification in order to work. The
notion that you can duplicate the utility of unique identity keys with
"meaningful " data in anything but the most trivial of databases is both naive
and absurd.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Tony Toews]

Re: Table w/ Autonumber AND Cascade update

Lyle Fairfield <MissingAddress @Invalid.Com> wrote:
[color=blue]
>The
>notion that you can duplicate the utility of unique identity keys with
>"meaningful " data in anything but the most trivial of databases is both naive
>and absurd.[/color]

Here I'd like to disagree with you. Natural keys can be used and can be meaningful.
More work and trouble than I like but they are doable.

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

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

You are confusing usefulness with meaningfulness. Autonumbers, when used,
are useful, but meaningless--to me as a developer, and to the user who
shouldn't see them. If I use a company's stock ticker as the PK, that's
meaninful--especially compared to an autonumber.

The notion that I *cannot* duplicate the utility of unique identity keys
with "meaningful " data in anything but the most trivial of databases--just
because you can't--is both naive and absurd.


"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns9489DC0 5170C0FFDBA@130 .133.1.17...[color=blue]
> "Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote in news:D-
> adnQqlFIXBdLvdR Vn-jg@comcast.com:
>[color=green]
> > I have developed a rather complex database, in SQL Server, for a[/color][/color]
personal[color=blue][color=green]
> > project (that may hit the Web this summer) and I haven't used any[/color][/color]
surrogate[color=blue][color=green]
> > keys. The db is to the point where some of the compound keys are rather
> > hefty, but it doesn't bother me enough to start using identity fields.[/color][/color]
I[color=blue][color=green]
> > appreciate the benefit of working with meaningful key values, versus
> > meaningless integers. I'd rather not have meaningless data in my[/color][/color]
database.[color=blue]
>
> Autonumber and identity fields are not meaningless. They identify each row[/color]
of[color=blue]
> a table. Database engines require this identification in order to work.[/color]
The[color=blue]
> notion that you can duplicate the utility of unique identity keys with
> "meaningful " data in anything but the most trivial of databases is both[/color]
naive[color=blue]
> and absurd.
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

The design I'm stuck with where most of the tables use autonumbers that the
user sees as meaningful data, but wants the option to change at whim.
Further, the user already has mountains of printouts with these autonumbers
on them, which means I must preserve existing values.

I see your point about time-intensive updates. My boss gave that same
reason for loving surrogate keys. Plus, he says that, in his experience,
most application developers don't want to deal with compound keys.

"Tony Toews" <ttoews@teluspl anet.net> wrote in message
news:tpqd20pohm t4gplisa6jp552d pke16vvt7@4ax.c om...[color=blue]
> "Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
>[color=green]
> >Answer: Design.[/color]
>
> But what design? Why?
>[color=green]
> >Even so, I don't understand the horror of the idea of changing a primary[/color][/color]
key[color=blue][color=green]
> >value on occassion. Access lets you do it for all field types other than
> >Autonumber, right? As long as you're using cascade update, what's the[/color][/color]
big[color=blue][color=green]
> >deal? As long as the value is still unique, and the change gets[/color][/color]
propagated[color=blue][color=green]
> >throughout related tables, I don't see a dilemma. Granted, if it's being
> >done a lot, it would seem to indicate a less than ideal PK choice.[/color]
>
> It's more along the lines of there should be no need to change an[/color]
autonumber primary[color=blue]
> key. A natural key sure, when, for example, a company changes their name[/color]
and thus[color=blue]
> the company code changes from ACM001 to XYZ013.. But an autonumber field[/color]
should[color=blue]
> never be visible by the user and they should never care what it's value[/color]
is.[color=blue]
>
> Of course when a company code changes from ACM001 to XYZ013 there can be[/color]
many tens of[color=blue]
> thousands of records in many tables which can change. And that's one of[/color]
my problems[color=blue]
> with natural keys in this fashion. You have to ensure all the updates get[/color]
committed[color=blue]
> before anyone else can use the database. You almost have to do this kind[/color]
of think[color=blue]
> after hours.
>
> 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[/color]

[Lee Cichanowicz]

Re: Table w/ Autonumber AND Cascade update

I would just like to thank all who participated in this thread. I have been
periodically searching the Access NG threads and learning from them for well
over a year now, and it isn't often that I post questions or answers. My
love-hate relationship with MS Access is heavily dependent upon Google
Groups. :] I truly appreciate the fast responses, insight, and opinions.


"Lee C." <cichanowicz_l@ hotmail.com> wrote in message
news:c4bb3244.0 402071455.2fd9d dcf@posting.goo gle.com...[color=blue]
> I'm finding this to be extremely difficult to set up. I understand
> that Access won't manage the primary key and the cascade updates for a
> table. Fine. I tried changing the PK type to number and setting
> default value to a UDF that manages the auto-numbering. Access won't
> take a UDF as a default value. Okay, I'll use SQL WITHOUT any
> aggregate functions, for the default value. Access won't do that
> either. Okay, I create a second column, make it autonumber and PK,
> then set the default value of my old PK column to the field value of
> the new autonumber PK field. Wow! Access won't do that either. Am I
> crazy or should this be easier? Any insight will be greatly
> appreciated.
>
> MS Access 2002
> Windows XP Pro
>
> Thanks,
> Lee[/color]

[Tony Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>The design I'm stuck with where most of the tables use autonumbers that the
>user sees as meaningful data, but wants the option to change at whim.
>Further, the user already has mountains of printouts with these autonumbers
>on them, which means I must preserve existing values.[/color]

Ah, gotcha. Makes much more sense now.

I have one similar situation but it's only one table, the main volunteer table. I
assign the number myself in VBA code. The volunteer ID is printed on all reports for
ease of data entry. Much easier for someone to key 1234 than Tony Toews.

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 Toews]

Re: Table w/ Autonumber AND Cascade update

"Lee Cichanowicz" <cichanowicz_l@ hotmail.com> wrote:
[color=blue]
>I would just like to thank all who participated in this thread. I have been
>periodically searching the Access NG threads and learning from them for well
>over a year now, and it isn't often that I post questions or answers. My
>love-hate relationship with MS Access is heavily dependent upon Google
>Groups. :] I truly appreciate the fast responses, insight, and opinions.[/color]

It's been an interesting discussion.

Thanks, 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