Cannot Delete Records

[Greg]

I made a simple database with two tables. I releated
them as one-to-many through table releationships then
made a query. I added both tables to the query, and it
seems to run fine.

Problem: If I try to delete a record, it seems like it
works. But when close and re-open the query the same
record is back. I never had this before. I am using
Access 2000 on Win XP. Any thoughts?

Thank You,
Greg

 

[Randy Wayne]

Tell me about the table relationships and what you tried
to delete.

There are two primary causes for not being able to
successfully delete a record through a query.

1. The query is not "updateable". This occurs when the
JOIN does not allow the query to allow updates. The
easiest test of this is to open the query and scroll to
the bottom. If there is a "blan" record and you can
enter some data it IS updateable. Otherwise it is not.
If this is the case you will need to redesign your query
and relook at the relationships.

2. You can not delete a record on the ONE side of the
relationship because it would leave "orphaned" records on
the MANY side. You can only delete on the MANY side.

To fix: Start with your relationships. Is your primary
key on the ONE side properly related to a foreign key on
the MANY side.

Next, if any of the fields you are viewing in the query
are from the ONE table you will not (usuaally) be able to
delete. Modify the qquery to show only records from the
MANY table, using the relationship to the ONE table to
define the records you are viewing.

Let me know if this helps.

 

[Greg]

Thanks for the response and help. Here is the info:

The tables are one-many. The query is set up to show records from both
tables. Updateable? Well, I can start a record through the query, but I
can only add data to the one side, not the many, until I close and reopen
the query. Then I can add to the many side.. Also, the records do delete
from the many side, but not the one.

My one table is set for Autonumber, Long Interger, Increment values, no
duplicates, primary key. My many side is set for the excact same name,
number format, long interger, not required, duplicates OK. They are
releated through releationships, one-many, enfore referential integirty,
casade updates and casade deletes. The join type is the middle (2.).

I really need to see information from both the one and the many side, else
the query really does me no good at all. This seems really bazaar to me. I
have made plenty of databases from scratch, but never ran into this. I
almost always use releated tables, and they delete so well that I actually
have to make them read only to prevent accidental cascade deletes across the
database. Thanks again for the help.

 

[Guest]

Here is the problem as I see it.

Lets say your ONE table is tblVendors. PK is VendorID
(autonumber). The only field is VendorName.

Lets say your MANY table is tblOrders. PK is OrdersID
(autonumber). FK is the VendorID from the tblVendors.
Other fields are Item and Cost.

Now you build a query displaying the VendorID from the
tblVendors and the OrdersID, Item, and Cost from the
tblVendors.

When you try to delete, Access does not know what you
want to delete. If it tries to delete everything in the
record, it would have to delete the VendorID from the tbl
of Vendors, ut since there is a VendorName still there,
it cannot. Same is true for any combination that leaves
parts of records without a PK.

Without seeing your other queries, I cannot tell why they
work and this one does not.
You said you need to use this query for delettion. What
are you trying to delete? Do you really mean to delete
something from the ONE table? If I knew I might better
help.

I will add that you should use the query to limit records
in the table that contains the record(s) you want to
delete - usually records in the MANY table - but you do
not need to display the information from the ONE table in
the query. For example:

You want to delete all Orders from a Vendor . Your query
would contain both tables - their relationship will
display a join. In the grid you include all the fields
from the MANY table and the VendorName from the
tblVendors but uncheck the display box. You can then add
a citeria to it like ="Jones Industries" to limit the
recordset. You would then be able to delete.

I hope this helps. Let me know