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]

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.
tblRequisition s --> tblLineItems --> tblLineItemFund ing -->
tblUnitAccepta nce ...

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****@teluspl anet.net> wrote in message
news:83******** *************** *********@4ax.c om...

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

tblRequisition s --> tblLineItems --> tblLineItemFund ing -->
tblUnitAccepta nce ...

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******@sympa tico.ca> wrote in message
news:9w******** *************** *****@40tude.ne t... 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******@n ews.microsoft.c om...

"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****@teluspl anet.net> wrote in message
news:ib******** *************** *********@4ax.c om...

"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