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 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.
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
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.
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.
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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"...
|
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 ?...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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=()=>{
|
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...
|
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...
|
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...
|
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 :...
|
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...
|
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...
|
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...
|
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...
| |