Delete query versus primary key

[Vladimir]

Hi friends,

I've got a problem.
I have 2 tables, both tables have an identical data structure and both have
primary key made of combination of two fields.
When I run a Delete query that should remove those records from table 1 that
are present in table 2, I get a following message:
"Could not delete from specified tables".

Might it be caused by a primary key? When I run similar query in the same
database based on tables with simple Primary key, evrything works normaly.
All permissions and Read only modes are set as needed.

I would appreciate any help.

Thanks Vladimir

 

[John Vinson]

Hi friends,

I've got a problem.
I have 2 tables, both tables have an identical data structure and both have
primary key made of combination of two fields.
When I run a Delete query that should remove those records from table 1 that
are present in table 2, I get a following message:
"Could not delete from specified tables".

Might it be caused by a primary key?

I strongly suspect that it's not the key itself; it's the relationship
between the two tables. Relational integrity will prevent the deletion
of a record in Table1 if that deletion would leave an orphan record in
a related table.

You can set the relationship to enable Cascade Deletes; this will
automatically delete all linked records in Table2 when you delete a
record in Table1 (note: this can be dangerous, since you could end up
deleting a lot of data with no way to get it back!). With a multifield
key you must set the join properties on ALL of the join lines in the
relationships window.

 

[Vladimir]

John,

The problem is the tables have no related tables so that the orphan records
are ruled out. I did not manage to set any relationship between tables but
got a message instead "No unique index found for the referenced field of the
primary table."
The joke is that both tables have the primary key and indexes properly set.

When I add a column to both tables and create a record by joining Field1 of
the primary key and Field2 of the primary key like this:
StringOfField1_StringOfField2 - I get a unique field that can be a primary
key. If I create delete query with tables amended like this everything
works - and without setting any relationship. I think this is the evidence
that records are unique and there is no obstruction to accomplish the delete
query with tables based on a multifield primary key. The data in both cases
are the same.

When I want to see records selected by the query and prepared for deleting I
can, but when I run the query I get the message mentioned before.

What can I do?

Thanks Vladimir

 

[John Vinson]

When I want to see records selected by the query and prepared for deleting I
can, but when I run the query I get the message mentioned before.

What can I do?

Please post the SQL of the query. You *DO* have a relationship (not an
enforced one but a relationship created for the query) - it may be
something wrong with the Join clause.

 

[Vladimir]

I've already found out the problem.
In one of the tables was an index that was somehow corrupted. So I removed a
primary key from a table - it should remove the index consequently - and
saved the table. Next step was creating a primary key again - it should
create the index again.
From this point everything became to work properly.
Thanks for a hint.

Vladimir