Help! cannot delete records in the table "PRODUCTS"

Paul T. Rong

Dear all,

Here is my problem:

There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to delete
those PRODUCTS from the table, but I was not allowed to do that, because
"there are records related with those PRODUCTS in other tables (e.g. in
table "ORDER_DETAIL").

Who knows how to solve this problem?

Any ideas, advise are very much appreciated. Thanks.

Paul T. RONG

Nov 13 '05 #1

Deano

Paul T. Rong wrote:

Dear all,

Here is my problem:

There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to
delete those PRODUCTS from the table, but I was not allowed to do
that, because "there are records related with those PRODUCTS in other
tables (e.g. in table "ORDER_DETAIL").

Who knows how to solve this problem?

Any ideas, advise are very much appreciated. Thanks.

Paul T. RONG

Delete the related records in ORDER_DETAIL then you'll be able to delete the
records from PRODUCTS. Else ensure that cascade deletes are turned on
within referential integrity.

Nov 13 '05 #2

Alfred

Hi
First delete the related records and then the products. Or delete the
relationship but you will have orphaned records, not a good idea
Alfred

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:uJ******************@news.chello.at...

Dear all,

Here is my problem:

There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to delete
those PRODUCTS from the table, but I was not allowed to do that, because
"there are records related with those PRODUCTS in other tables (e.g. in
table "ORDER_DETAIL").

Who knows how to solve this problem?

Any ideas, advise are very much appreciated. Thanks.

Paul T. RONG

Nov 13 '05 #3

Paul T. Rong

But, the related records are more than two thousand, it will be very hard to
delete all of them.

And for some reason, I would like to keep all old records, is it
THEORETICALLY
possible, I mean, to delete the unwanted records in PRODUCTS table but at
the same time to leave the old related records in other tables unchanged?

Last, who can help to explain more simply on "cascade deletes", I don't
think I
understand that term. Many thanks in advance.

Paul


"Deano" <de*********@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:b2*********************@stones.force9.net...

Paul T. Rong wrote:

Dear all,

Here is my problem:

There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to
delete those PRODUCTS from the table, but I was not allowed to do
that, because "there are records related with those PRODUCTS in other
tables (e.g. in table "ORDER_DETAIL").

Who knows how to solve this problem?

Any ideas, advise are very much appreciated. Thanks.

Paul T. RONG
Delete the related records in ORDER_DETAIL then you'll be able to delete

the records from PRODUCTS. Else ensure that cascade deletes are turned on
within referential integrity.

Nov 13 '05 #4

Anne Nolan

If you want to retain the related records, then don't delete the "parent"
Products records. Instead, add a column to the Products table, and call it
"Active". Make it a Yes/no field. Instead of deleting a Product, just set
the value of Active to "no".

Then fix up your forms, reports, and queries to just show records from the
Products table where Active = "yes".

Anne Nolan

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:nq******************@news.chello.at...

But, the related records are more than two thousand, it will be very hard to delete all of them.

And for some reason, I would like to keep all old records, is it
THEORETICALLY
possible, I mean, to delete the unwanted records in PRODUCTS table but at
the same time to leave the old related records in other tables unchanged?

Last, who can help to explain more simply on "cascade deletes", I don't
think I
understand that term. Many thanks in advance.

Paul


"Deano" <de*********@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:b2*********************@stones.force9.net...

Paul T. Rong wrote:

Dear all,

Here is my problem:

There is a table "products" in my access database, since some of the
products are out of date and stopped manufacture, I would like to
delete those PRODUCTS from the table, but I was not allowed to do
that, because "there are records related with those PRODUCTS in other
tables (e.g. in table "ORDER_DETAIL").

Who knows how to solve this problem?

Any ideas, advise are very much appreciated. Thanks.

Paul T. RONG

Delete the related records in ORDER_DETAIL then you'll be able to delete

the

records from PRODUCTS. Else ensure that cascade deletes are turned on
within referential integrity.

Nov 13 '05 #5

Deano

Paul T. Rong wrote:

But, the related records are more than two thousand, it will be very
hard to delete all of them.

And for some reason, I would like to keep all old records, is it
THEORETICALLY
possible, I mean, to delete the unwanted records in PRODUCTS table
but at the same time to leave the old related records in other tables
unchanged?
Thing is, if you have established a relationship between the two then you
are always going to have this problem. Should there be a one-2-many
relationship between them in the first place?
Last, who can help to explain more simply on "cascade deletes", I
don't think I
understand that term. Many thanks in advance.

From Access 2000 help;

"If you select the Cascade Delete Related Records check box when defining a
relationship, any time you delete records in the primary table, Microsoft
Access automatically deletes related records in the related table. For
example, If you delete a customer record from the Customers table, all the
customer's orders are automatically deleted from the Orders table (this
includes records in the Order Details table related to the Orders records).
When you delete records from a form or datasheet with the Cascade Delete
Related Records check box selected, Microsoft Access warns you that related
records may also be deleted. However, when you delete records using a delete
query, Microsoft Access automatically deletes the records in related tables
without displaying a warning."

To change this go into Tools, Relationships and double-click on the line
connecting your tables.
I reckon Enforce Referential Integrity is checked.
If you uncheck that then you can do what you want but it's going to leave
your tables in a messy state - there will be references in one table to
records that no longer exist.

I think you can sidestep this by following Anne's advice.

Nov 13 '05 #6

Paul T. Rong

Anne, great ideas! thanks you!!!

"Anne Nolan" <an***************@AOL.COM> дÈëÏûÏ¢ÐÂÎÅ
:2i************@uni-berlin.de...

If you want to retain the related records, then don't delete the "parent"
Products records. Instead, add a column to the Products table, and call it "Active". Make it a Yes/no field. Instead of deleting a Product, just set the value of Active to "no".

Then fix up your forms, reports, and queries to just show records from the
Products table where Active = "yes".

Anne Nolan

"Paul T. Rong" <et***@hotmail.com> wrote in message
news:nq******************@news.chello.at...

But, the related records are more than two thousand, it will be very hard

to

delete all of them.

And for some reason, I would like to keep all old records, is it
THEORETICALLY
possible, I mean, to delete the unwanted records in PRODUCTS table but

at the same time to leave the old related records in other tables unchanged?
Last, who can help to explain more simply on "cascade deletes", I don't
think I
understand that term. Many thanks in advance.

Paul


"Deano" <de*********@hotmail.com> дÈëÏûÏ¢ÐÂÎÅ
:b2*********************@stones.force9.net...

Paul T. Rong wrote:
> Dear all,
>
> Here is my problem:
>
> There is a table "products" in my access database, since some of the
> products are out of date and stopped manufacture, I would like to
> delete those PRODUCTS from the table, but I was not allowed to do
> that, because "there are records related with those PRODUCTS in other > tables (e.g. in table "ORDER_DETAIL").
>
> Who knows how to solve this problem?
>
> Any ideas, advise are very much appreciated. Thanks.
>
> Paul T. RONG

Delete the related records in ORDER_DETAIL then you'll be able to

delete the

records from PRODUCTS. Else ensure that cascade deletes are turned on
within referential integrity.

Nov 13 '05 #7