表w /自动编号和级联更新 [英] Table w/ Autonumber AND Cascade update

问题描述

我发现这很难设置。我理解

Access无法管理

表的主键和级联更新。精细。我尝试将PK类型更改为数字并将

默认值设置为管理自动编号的UDF。访问不会

将UDF作为默认值。好的，我将使用没有任何

聚合函数的SQL作为默认值。访问不会那样做

。好的，我创建了第二列，让它自动编号和PK，然后
然后将我的旧PK列的默认值设置为字段值

新的自动编号PK字段。哇！访问也不会这样做。我是疯了吗？或者这会更容易吗？任何洞察力都会非常好。

赞赏。


MS Access 2002

Windows XP专业版


谢谢，

Lee

解决方案

2004年2月7日14:55:45 -0800，Lee C.写道：

我发现这很难设置。我知道Access无法管理主键和级联更新表。精细。我尝试将PK类型更改为数字并将
默认值设置为管理自动编号的UDF。 Access不会将UDF作为默认值。好的，我将使用没有任何
聚合函数的SQL作为默认值。访问也不会那样做
。好的，我创建了第二列，使其成为自动编号和PK，然后将旧旧PK列的默认值设置为新自动编号PK字段的字段值。哇！访问也不会这样做。我是疯了还是应该更容易？任何见解都将受到极大的赞赏。

MS Access 2002
Windows XP专业版

谢谢，
Lee

您要做的是什么？

自动编号并不意味着被管理。它们是一次性使用，独特的价值可以自动分配，而不用担心它会被重复。

Access管理级联更新就好了。如果记录被更改，只要设置了关系，就会调整相关记录，并且新值不违反有关规则的记录。

已定义。


如果您尝试使用永不中断序列的编号系统，那么您需要创建更新查询或函数重新编号

所需记录。如果定义了关系，那么这些更改将会是b

$

Mike Storr

veraccess.com

我要做的是能够更改记录'

主键值并让它在整个相关过程中更新级联

表。


自动编号肯定是管理的。 Access分配它们并确保

它们是唯一的。那是管理层，我的朋友。 < g>


我知道Access管理级联更新很好 - 但如果PK是自动编号，它拒绝接受管理任务的
！是的，是的，

你不能改变自动编号。 < g取代;这就是我想要的工作

左右。 （如果有人付钱给我，我可以写一个RDBMS，你可以在这里改变一个自动编号，我仍然会确保唯一性。访问已经

让我改变任何其他PK - 那不是自动编号 - 而Access仍然是

确保唯一性，或阻止更改。为什么autonumber如此

不同？因为微软选择了它那样;不是因为

它是不可能或不好的设计。）


这是一个现有的数据库，我没有设计：申请单有

订单项。订单项可以重新分配给其他请购单。所有

我想要的是能够更改现有订单项的ReqID（当前为自动编号）并且让Access级联更新相关的

表格。


从版本10 RDBMS开始，这似乎没什么好看的。

目前，订单项已复制到一个临时表，然后VBA是用于从tblLineItems中删除记录的
，然后VBA将临时表中的rec

复制回到具有新ReqID的tblLineItems，并且

从临时表中删除rec。有这么多相关的表格

，这个噩梦在VBA中管理起来很麻烦。因此，我的想法是

让关系数据库管理系统做一些管理。


再次感谢，

Lee


Mike Storr< st ****** @ sympatico.ca>在消息新闻中写道：< 1w ****************************** @ 40tude.net> ...

你要做的是什么？
自动编号并不意味着被管理。它们是一次性使用，唯一的值可以自动分配而不用担心它会被重复。
Access管理级联更新就好了。如果记录被更改，只要设置了关系，就会调整它的相关记录，并且新值不会违反已定义的规则。
<如果您正在尝试使用永不中断序列的编号系统，那么您需要创建更新查询或重新编号所需记录的函数。如果定义了关系，那么这些更改将会级联。
2004年2月7日14:55:45 -0800，Lee C.写道：

我发现这很难设置。我知道Access无法管理主键和级联更新表。精细。我尝试将PK类型更改为数字并将
默认值设置为管理自动编号的UDF。 Access不会将UDF作为默认值。好的，我将使用没有任何
聚合函数的SQL作为默认值。访问也不会那样做
。好的，我创建了第二列，使其成为自动编号和PK，然后将旧旧PK列的默认值设置为新自动编号PK字段的字段值。哇！访问也不会这样做。我是疯了还是应该更容易？任何见解都将受到极大的赞赏。

MS Access 2002
Windows XP专业版

谢谢，
Lee

我不相信任何具有相当于AutoNumbers的RDMS（例如，对于

实例，SQL Server'的标识类型）允许您更改

字段的值。没有合理的理由我可以想到你为什么需要这样做。


正如迈克所说，AutoNumbers只用于一个目的：创建一个

（几乎可以保证）唯一值，可用作主键。很少，

，如果有的话，自动编号字段的值甚至会显示给用户。

如果你关心自动编号字段的值，那么它''几乎

肯定你应该使用不同的数据类型。


-

Doug Steele，Microsoft Access MVP
http://I.Am/DougSteele

（请不要发送私人电子邮件）


" Lee C." < CI *********** @ hotmail.com>在消息中写道

新闻：c4 ************************* @ posting.google.co m ... < blockquote class =post_quotes>我要做的是能够更改记录的主键值并在相关的表格中更新级联。

自动编号肯定是管理的。 Access分配它们并确保它们是唯一的。那是管理层，我的朋友。 < g>

我知道Access管理级联更新很好 - 但如果PK是自动编号，它拒绝接受管理任务！是的，是的，
你不能改变自动编号。 < g取代;这就是我想要的工作。 （如果有人付钱给我，我可以写一个RDBMS你可以改变一个自动编号，我仍然会确保唯一性。访问已经
让我改变任何其他PK - 那不是autonumber - 和Access仍然可以确保唯一性或防止变化。为什么autonumber如此不同？因为微软选择这样做;不是因为它不可能或者不好设计。）

这是一个现有的数据库，我没有设计：请购单有
行项目。订单项可以重新分配给其他请购单。我想要的是能够更改现有订单项的ReqID（当前为自动编号）并让Access级联更新相关的
表。

从版本10 RDBMS开始，这似乎无法提出太多要求。
目前，行项目被复制到临时表，然后VBA用于从tblLineItems中删除记录，然后VBA使用新的ReqID将临时表中的rec
复制回tblLineItems，并且
从临时表中删除rec。有这么多相关的表格，这个噩梦在VBA中管理起来很麻烦。因此，我的想法是让关系数据库管理系统做一些管理。

再次感谢，


Mike Storr< st ** ****@sympatico.ca>在消息中写道

新闻：< 1w ****************************** @ 40tude.net> ...

你要做的是什么？
自动编号并不意味着被管理。它们是一次性使用，
唯一值可以自动分配而不用担心它是
重复。 Access可以很好地管理级联更新。如果记录被更改，只要设置了关系，就会调整它的相关记录，
并且新值不会违反已定义的规则。
<如果您正在尝试使用永不中断序列的编号系统，那么您需要创建更新查询或重新编号
所需记录的函数。如果定义了关系，则这些更改将级联。

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

解决方案

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

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

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.

这篇关于表w /自动编号和级联更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！