Table w/ Autonumber AND Cascade update

[Lee C.]

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

[Bas Cost Budde]

David W. Fenton wrote:

Bas Cost Budde <ba*@heuveltop. org> wrote in

autonumber itself is some sort of trigger.

Um, no, it's not.

AutoNumber is a special kind of default value.

Nothing more, nothing less.

I see; there is no table access to create the new number, right? I'm too
forgiving (or too assuming anyway)

It doesn't matter anyway because any mechanism may be behind the
cascades, and any other that is behind the autonumber, are unavailable
to us developers.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[David W. Fenton]

Tony Toews <tt****@teluspl anet.net> wrote in
news:tp******** *************** *********@4ax.c om:

"Lee Cichanowicz" <ci***********@ hotmail.com> wrote:

Answer: Design.

But what design? Why?

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.

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.

To me, the problem there is not the cascading update, which Jet
handles just fine.

It's that you've built into your application a system that isn't
really needed -- a system of codes.

Back in the days when we didn't have dropdown lists and easy
lookups, having a printed list of company codes was how people
worked, because the UI's were primitive enough that they couldn't
provide the capability to find the data in human-friendly terms.

Nowaways, since at least the advent of Access (i.e, the last 10
years), there's no need for that. Don't force the user to do what a
computer can do better.

All data retrieval and input should be in terms that are natural to
the process and to human beings.

Artifically created codes are a kludge that just isn't necessary any
more.

And that's where surrogate AutoNumber primary keys come in -- human
beings never see them, so it doesn't matter what they are. And since
they can't be updated, there's never any need to have a cascade
update on an AutoNumber relationship.

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

[Lee Cichanowicz]

I'm sure there is beneficial wisdom in your post, Mr. Fenton, but I cannot
extract it, though, I'd like to. To what are you referring by "system of
codes" that the designer built into the application? The autonumbers?
Later you go on to say autonumbers are good. I'm confused.

A little more, perhaps long overdue, background on this situation. The app
was designed with RequisitionIDs that are autonumbers, and the user sees
them as ReqIDs. There were no cascade updates or deletes in the numerous
related tables, so when a Requisition Line Item was moved to a different
Requisition, it was treated as creating a New Line Item, and all new records
in all related tables, then the old records were deleted--which worked well
most of the time and *only* required 1,000+ lines of VBA (due to handling
combining of like items). When it didn't work, it was a monster to debug.

I decided that Access should be earning its keep by doing cascade updates,
which involved some struggle (in cleaning up the orphaned records from the
tables, tweaking the relationships, and re-writing the code) but is now
reliable and requires less than 100 lines of VBA.

Example, move ReqID 5's LineItem 3 to ReqID 10. I see that as updating the
ReqID on LineItem 3 from 5 to 10, and updating the LineItem to the next
sequential number. The old code saw it as creating ReqID 10, line item x,
and deleting ReqID 5's line item 3. Keep in mind, this LineItem has a
one-many with funding, which has a one-to-many with unit acceptance, etc.
That was a whole lot of record creation and deletion!

Since the ReqIDs are autonumber and the user wants to be able to change them
sometimes, I wanted to change them to number type, but then had no
trigger-like mechanism by which to manage them in the back-end. That
situation led to my post.

Thanks,
Lee
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.74...

Tony Toews <tt****@teluspl anet.net> wrote in
news:tp******** *************** *********@4ax.c om:

"Lee Cichanowicz" <ci***********@ hotmail.com> wrote:

Answer: Design.

But what design? Why?

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.

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.

To me, the problem there is not the cascading update, which Jet
handles just fine.

It's that you've built into your application a system that isn't
really needed -- a system of codes.

Back in the days when we didn't have dropdown lists and easy
lookups, having a printed list of company codes was how people
worked, because the UI's were primitive enough that they couldn't
provide the capability to find the data in human-friendly terms.

Nowaways, since at least the advent of Access (i.e, the last 10
years), there's no need for that. Don't force the user to do what a
computer can do better.

All data retrieval and input should be in terms that are natural to
the process and to human beings.

Artifically created codes are a kludge that just isn't necessary any
more.

And that's where surrogate AutoNumber primary keys come in -- human
beings never see them, so it doesn't matter what they are. And since
they can't be updated, there's never any need to have a cascade
update on an AutoNumber relationship.

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

[David W. Fenton]

"Lee Cichanowicz" <ci***********@ hotmail.com> wrote in
news:5u******** ************@co mcast.com:

I'm sure there is beneficial wisdom in your post, Mr. Fenton, but
I cannot extract it, though, I'd like to. To what are you
referring by "system of codes" that the designer built into the
application? . . .
I was referring to Tony's example of artificially constructed
company codes:

Tony Toews <tt****@teluspl anet.net> wrote in
news:tp******** *************** *********@4ax.c om:

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

To me, those are artifacts of the old days, and are a huge mistake,
as they can't ever be algorithmically created reliably, unless you
include a MAX() on the number or some such.
. . . The autonumbers? Later you go on to say autonumbers
are good. I'm confused.
I was replying to Tony's examples.

I think AutoNumbers are good.

And that users should never know they exist.

They should refer to people by their names and as much other
information as necessary to distinguish two people with the same
name. Companies, likewise. Name or Company + Location generally does
the trick.
A little more, perhaps long overdue, background on this situation.
The app was designed with RequisitionIDs that are autonumbers,
and the user sees them as ReqIDs. . . .
This is a major design error.
. . . There were no cascade updates
or deletes in the numerous related tables, so when a Requisition
Line Item was moved to a different Requisition, it was treated as
creating a New Line Item, and all new records in all related
tables, then the old records were deleted--which worked well most
of the time and *only* required 1,000+ lines of VBA (due to
handling combining of like items). When it didn't work, it was a
monster to debug.
I don't get it. If you move a line item to a different requisition,
aren't you just updating the foreign key in the line item? Child
records of the line item should be joined to the line item record by
the line item record's primary key, which is most easily implemented
as an AutoNumber surrogate key, but could also be implemented as a
compound key on RequisitionID (the foreign key of the line item) and
LineItem number. Now, assuming that the line items of any
requisition are specific to that requisition (i.e., the line items
of a requisition with 3 line items will be numbered 1, 2 and 3), the
compound key on RequisitionID + LineItemNumber could cause a PK
collision in the line item table if the target requisition already
uses those line items. That is you move Requisition 1's line item 2
to Requisition 2, and Requisition 2 already has a line item 1, you
have a problem -- you've got to figure out the next available line
item. Then you have to cascade that through to the records in other
tables related to the line item records.

With surrogate keys instead of compound "natural" keys (I'm not sure
I'd count a compound key with a RequisitionID in it that is an
AutoNumber as a pure natural key), you have no such worries. Line
item number is generated only for printouts, and does not exist as
an attribute of the data itself, and the line item records will have
their own AutoNumber behind the scenes for storing as foreign key in
the tables related to the line item table. To move a line item to a
different requisition, all you have to to is change the
RequisitionID, and all the child records follow it.

Now, you may be wedded to the line item numbers as an attribute of
the data as opposed to being for printout/display only, because you
might be using them to control the sort order of the line items. In
that case, you'd have to have some way of breaking "ties" between
the line item numbers. How you handle that would depend on whether
you have a unique index on RequisitionID + LineItemNumber or not. I
wouldn't recommend it, as it makes life much harder. If you *don't*
have it, you can sort the line items for a requisition and then walk
through them, record by record. When you come to a repeated number,
increment it by one, then increment all further numbers by one. If
you again come to repeated numbers, increment again, and so forth,
so that you can sequentially renumber the line item records after
you've already moved them to the other RequisitionID.

This whole situation is exactly the reason why I am all for
AutoNumber surrogate keys instead of "natural" and/or compound keys
-- because those can maintain the relationships between records
without having any meaning other than maintaining those
relationships.
I decided that Access should be earning its keep by doing cascade
updates, which involved some struggle (in cleaning up the orphaned
records from the tables, tweaking the relationships, and
re-writing the code) but is now reliable and requires less than
100 lines of VBA.
I think your schema is badly flawed.
Example, move ReqID 5's LineItem 3 to ReqID 10. I see that as
updating the ReqID on LineItem 3 from 5 to 10, and updating the
LineItem to the next sequential number. The old code saw it as
creating ReqID 10, line item x, and deleting ReqID 5's line item
3. Keep in mind, this LineItem has a one-many with funding, which
has a one-to-many with unit acceptance, etc. That was a whole lot
of record creation and deletion!
I don't see why you'd ever think of deleting records when you could
just change the foreign key of the line item records (i.e., the
RequisitionID). You only need cascade update if the records related
to the line item also store the RequisitionID, and there's never any
need for that, as it involves repitition of data that needs to be
updated in two (or more) places. That's what cascade update is for,
but the structure I outlined above has no need whatsoever for
cascading updates.
Since the ReqIDs are autonumber and the user wants to be able to
change them sometimes, I wanted to change them to number type, but
then had no trigger-like mechanism by which to manage them in the
back-end. That situation led to my post.

If the user needs to change them, which seems like a really bad idea
to me (the user's accountant might think so, too), then you cannot
use an AutoNumber for it. And with Access alone, you can't force the
creation of the next number to match the rules you want.

I don't see this an issue, since nobody will be editing the back
end. If they are, then that's the problem you should be worrying
about.

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