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

[Mike Storr]

On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

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

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique
value that can be automatically assigned without fear of it being repeated.
Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and
the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the
desired records. If relationships are defined, then these changes will
cascade.
--
Mike Storr
veraccess.com

[Lee C.]

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympatico.ca> wrote in message news:<1w******************************@40tude.net> ...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique
value that can be automatically assigned without fear of it being repeated.
Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and
the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the
desired records. If relationships are defined, then these changes will
cascade. On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

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

[Douglas J. Steele]

I don't believe any RDMS that has the equivalent of AutoNumbers (say, for
instance, SQL Server's Identity type) allows you to change the value of the
field. There is no legitimate reason I can think of why you'd need to.

As Mike alludes to, AutoNumbers are intended for one purpose: to create an
(almost guaranteed) unique value that can be used as a primary key. Seldom,
if ever, should the value of the AutoNumber field even be shown to the user.
If you care about the value of the AutoNumber field, then it's almost
certain that you should be using a different data type.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Lee C." <ci***********@hotmail.com> wrote in message
news:c4*************************@posting.google.co m...

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympatico.ca> wrote in message

news:<1w******************************@40tude.net> ...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the desired records. If relationships are defined, then these changes will
cascade.

[Lee Cichanowicz]

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying
to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things
should be, but that doesn't help me right now. How do I get around this, is
my question.

I am stuck with a table that has an autonumber PK, because it has no good
candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records
if Access won't let me: (1) use SQL in the default value, (2) use a UDF in
the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

Sincere thanks,
Lee
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:XX******************@twister01.bloor.is.net.c able.rogers.com...

I don't believe any RDMS that has the equivalent of AutoNumbers (say, for
instance, SQL Server's Identity type) allows you to change the value of the field. There is no legitimate reason I can think of why you'd need to.

As Mike alludes to, AutoNumbers are intended for one purpose: to create an
(almost guaranteed) unique value that can be used as a primary key. Seldom, if ever, should the value of the AutoNumber field even be shown to the user. If you care about the value of the AutoNumber field, then it's almost
certain that you should be using a different data type.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Lee C." <ci***********@hotmail.com> wrote in message
news:c4*************************@posting.google.co m...

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympatico.ca> wrote in message

news:<1w******************************@40tude.net> ...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence, then you'll need to create update queries or a function that renumbers the desired records. If relationships are defined, then these changes will
cascade.

[Bas Cost Budde]

Lee Cichanowicz wrote:

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying
to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things
should be, but that doesn't help me right now. How do I get around this, is
my question.

I am stuck with a table that has an autonumber PK, because it has no good
candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records
if Access won't let me: (1) use SQL in the default value, (2) use a UDF in
the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

You definately have a no-go there. There are no user defined triggers in
Access (Jet engine). So, nothing inbetween numbers and autonumber.

As long as your users do not touch the tables, is there a real objection
to handling new numbers from the front end?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Lee Cichanowicz]

You have clearly identified problem, Bas: No triggers in Access! What a
tremendous shortcoming...I mean, product feature, that is.

My real objection to managing record IDs in the front end is that I upsize a
lot of Access databases to SQL Server. I also put Web front-ends on them.
I know that the more back-end management that I do in the front-end, the
greater the nightmare upsizing or Web-enabling becomes. So, it's just a
matter of principle, but it seems I'm screwed here.

Thanks,
Lee

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0*********@news2.solcon.nl...

You definately have a no-go there. There are no user defined triggers in
Access (Jet engine). So, nothing inbetween numbers and autonumber.

As long as your users do not touch the tables, is there a real objection
to handling new numbers from the front end?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl
Lee Cichanowicz wrote:

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things should be, but that doesn't help me right now. How do I get around this, is my question.

I am stuck with a table that has an autonumber PK, because it has no good candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records if Access won't let me: (1) use SQL in the default value, (2) use a UDF in the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

[Tony Toews]

ci***********@hotmail.com (Lee C.) wrote:

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.
But why?
This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.
But this sounds like ReqID is a foreign key and not the primary key. Surely the
ReqID is a primary key in the Requesition Header table. But it would be a foreign
key in the Line Item table.
This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Just curious. What kind of related tables do you have from the Line Items table?

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]

> >What I am trying to do is have the capability of changing a record's

primary key value and have it cascade updated throughout related
tables.
But why?

As it says below: Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables. But this sounds like ReqID is a foreign key and not the primary key. Surely the ReqID is a primary key in the Requesition Header table. But it would be a foreign key in the Line Item table.

Yep. Precisely.
This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Just curious. What kind of related tables do you have from the Line Items

table?

tblRequisitions --> tblLineItems --> tblLineItemFunding -->
tblUnitAcceptance ...

[Mike Storr]

On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

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

I think the only work around for this is to change the data type on the
field from AutoNumber to Long. You'll need to create you own function to
generate new numbers, but your update queries should then be capable of
changing your IDs

--
Mike Storr
veraccess.com

[Bas Cost Budde]

Lee Cichanowicz wrote:

You have clearly identified problem, Bas: No triggers in Access! What a
tremendous shortcoming...I mean, product feature, that is.

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.

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

[Tony Toews]

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

But why?
As it says below: Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

>This is an existing database, which I didn't design: Requisitions have
>line items. Line items can be reassigned to other requisitions. All
>I want is to be able to change the ReqID (currently an autonumber) of
>an existing line item and have Access cascade update the related
>tables.

But this sounds like ReqID is a foreign key and not the primary key.

Surely the

ReqID is a primary key in the Requesition Header table. But it would be a

foreign

key in the Line Item table.

Yep. Precisely.

But the LineItem table in which you need to change ReqID has it's own autonumber key
which is not ReqID. Likely LineItemID if I follow the naming system implied. Thus
ReqID in the Line Item table is not an autonumber key in which case you should be
able to change it just fine. And without affecting any child tables such as Line
Item Funding.
tblRequisitions --> tblLineItems --> tblLineItemFunding -->
tblUnitAcceptance ...

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]

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.

Thanks,
Lee
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:83********************************@4ax.com...

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

But why?
As it says below: Line items can be reassigned to other requisitions. AllI want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

>This is an existing database, which I didn't design: Requisitions have
>line items. Line items can be reassigned to other requisitions. All
>I want is to be able to change the ReqID (currently an autonumber) of
>an existing line item and have Access cascade update the related
>tables.

But this sounds like ReqID is a foreign key and not the primary key.

Surely the

ReqID is a primary key in the Requesition Header table. But it would

be aforeign

key in the Line Item table.

Yep. Precisely.

But the LineItem table in which you need to change ReqID has it's own

autonumber key which is not ReqID. Likely LineItemID if I follow the naming system implied. Thus ReqID in the Line Item table is not an autonumber key in which case you should be able to change it just fine. And without affecting any child tables such as Line Item Funding.

tblRequisitions --> tblLineItems --> tblLineItemFunding -->
tblUnitAcceptance ...

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]

It seems impossible to make all of this happen in the back-end mdb, though.
Or is it?

I think the only work around for this is to change the data type on the
field from AutoNumber to Long. You'll need to create you own function to
generate new numbers, but your update queries should then be capable of
changing your IDs

--
Mike Storr
veraccess.com

"Mike Storr" <st******@sympatico.ca> wrote in message
news:9w****************************@40tude.net... On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

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

[Michael \(michka\) Kaplan [MS]]

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

It seems impossible to make all of this happen in the back-end mdb, though.

Correct.
Or is it?

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, and
(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.

[Lee Cichanowicz]

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]" <mi*****@online.microsoft.com> wrote in
message news:40******@news.microsoft.com...

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

It seems impossible to make all of this happen in the back-end mdb, though.

Correct.

Or is it?

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,

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

[Tony Toews]

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

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.
Whereas designing new database has been pretty much all I've been doing for years.
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. :[

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]

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

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.

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]

Bas Cost Budde <ba*@heuveltop.org> wrote in
news:c0**********@news2.solcon.nl:

Lee Cichanowicz wrote:

You have clearly identified problem, Bas: No triggers in Access!
What a tremendous shortcoming...I mean, product feature, that is.

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.

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]

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" <tt****@telusplanet.net> wrote in message
news:ib********************************@4ax.com...

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

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.

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

[Lee Cichanowicz]

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" <tt****@telusplanet.net> wrote in message
news:bd********************************@4ax.com...

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

I saw it as wanting to misuse a feature (tirggers) that would have
legitimate uses, if it existed. ;] I think well-designed Access databasesare too uncommon; I rarely get to work on them, anyway. I also rarely getto design.
Whereas designing new database has been pretty much all I've been doing

for years.

Personally, I don't like surrogate keys at all. I wish Access didn't haveautonumber, but that likely wouldn't force people to design tables with goodnatural keys--they'd just simulate autonumber in VBA. :[
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]

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

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]

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

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.

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]

"Lee Cichanowicz" <ci***********@hotmail.com> wrote in news:D-
ad******************@comcast.com:

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.

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]

Lyle Fairfield <Mi************@Invalid.Com> wrote:

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.

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]

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" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.17...

"Lee Cichanowicz" <ci***********@hotmail.com> wrote in news:D-
ad******************@comcast.com:

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

[Lee Cichanowicz]

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" <tt****@telusplanet.net> wrote in message
news:tp********************************@4ax.com...

"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 keyvalue 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 bigdeal? As long as the value is still unique, and the change gets propagatedthroughout 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.

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]

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." <ci***********@hotmail.com> wrote in message
news:c4**************************@posting.google.c om...

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

[Tony Toews]

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

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.

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]

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

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.

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

[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****@telusplanet.net> wrote in
news:tp********************************@4ax.com:

"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********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.74...

Tony Toews <tt****@telusplanet.net> wrote in
news:tp********************************@4ax.com:

"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********************@comcast.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****@telusplanet.net> wrote in
news:tp********************************@4ax.com:

> 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