472,986 Members | 2,995 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 software developers and data experts.

Help! cannot delete records in the table "PRODUCTS"

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
6 3019
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
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
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
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
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

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jucius | last post by:
Using Delphi for developing, I get and delete records from an Access Database 2000. Sometimes, It happened 3 times (but not reproductible), the application did not succeed in connecting the...
2
by: MLH | last post by:
I have a table with the following fields: AreaID AreaCode Exchange City The table is in an Access 97 database. The table actually resides on a Linux box and is attached via a MySQL ODBC...
7
by: Paul T. Rong | last post by:
Hi everybody, I can not add into the "order_detail" subform a product more than once. The subform just refuses accepting any repeated items. The subform's source is a "expanded_order_detail"...
2
by: Robin | last post by:
I have a main table that I need to delete records that arn't referenced in another. Query says I cannot delete. If I remove the reference query all deletes ok. Hope there is a way around this ?...
18
by: shinyo21 | last post by:
Anyone know how to create a SQL or way in Access for deleting few tables' record. I means is delete all the records in specify few tables. I tried to use query to delete the records' table but...
6
by: satish mullapudi | last post by:
Hi All, I am getting strange situation. These r the steps I have followed: 1. Created an EMPLOYEE table with around 14 fields & 688038 records. (so a large table indeed). 2. Tried to delete all...
4
by: felicia | last post by:
Hi All, Below is my code to delete records: adodcAllEntries.Recordset.MoveFirst Do While (adodcAllEntries.Recordset.EOF = False) If adodcAllEntries.Recordset.Fields(0) = selected_id Then...
6
by: scott.tang | last post by:
I'm experiencing a very strange problem. My application is MS Access front-end and MS SQL server back-end database. I have a SQL statement that deletes records from a table after an export...
6
by: kstevens | last post by:
I have tables setup with a main table for information and a subtable that records the "multiple" records for the main record. I have written a query to go in and find Null or "" values to delete...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.