Access 2002 cannot delete record in query

[Dan]

Access 2002 SP-2 - when we delete a record in a query the
record seems to delete, but then when you refresh the
table it is back. We thought going to SP-2 would fix the
problem, but that is not the case. Any ideas???

Thanks for the help....

 

[John Vinson]

Access 2002 SP-2 - when we delete a record in a query the
record seems to delete, but then when you refresh the
table it is back. We thought going to SP-2 would fix the
problem, but that is not the case. Any ideas???

Thanks for the help....

I really doubt that this is a version problem; I suspect it's a query
problem! Could you post the SQL view of the query, and describe how
you're doing the deletion?

 

[Dan]

Here is that SQL View you requested.

Thanks for the help..

SELECT Patients.ID, Patients.Patient, Patients.FName,
Patients.LName, Tests.EEGFileName, Tests.CDVolume,
TestData.DocFileName
FROM (Patients INNER JOIN Tests ON Patients.ID =
Tests.PatientsID) LEFT JOIN TestData ON Tests.ID =
TestData.TestsID
WHERE (((Tests.EEGFileName) Is Null) AND
((TestData.DocFileName) Is Null))
ORDER BY Patients.Patient;

 

[John Vinson]

Here is that SQL View you requested.

Thanks for the help..

SELECT Patients.ID, Patients.Patient, Patients.FName,
Patients.LName, Tests.EEGFileName, Tests.CDVolume,
TestData.DocFileName
FROM (Patients INNER JOIN Tests ON Patients.ID =
Tests.PatientsID) LEFT JOIN TestData ON Tests.ID =
TestData.TestsID
WHERE (((Tests.EEGFileName) Is Null) AND
((TestData.DocFileName) Is Null))
ORDER BY Patients.Patient;

By default deleting a record from this query will delete from the
"manyest" table - TestData. What are you trying to delete? a record
from TestData, from Tests, or from Patients? Have you demonstrated
that selecting a record (in datasheet view, by clicking the record
selector at the left edge) and deleting it in fact does NOT delete any
record from any table?

 

[Dan]

Hello John,

Yes, I am trying to delete records from all tables, using
the link table manager. When i delete a record from the
query the record will delete, then when you close the
query and open it back up the record you just deleted is
back. It seems as though the three tables are not getting
updated with the deleted record, but just do not know
why...


Thanks

 

[John Vinson]

Hello John,

Yes, I am trying to delete records from all tables, using
the link table manager.

The linked table manager links tables - it doesn't have anything to do
with deleting records.

When i delete a record from the
query the record will delete, then when you close the
query and open it back up the record you just deleted is
back. It seems as though the three tables are not getting
updated with the deleted record, but just do not know
why...

When you delete a record from this query it will NOT delete the record
from the main table. It will delete one record from the "manyest" side
of the three tables.

I would suggest opening the backend database and selecting "Cascade
Deletes" on all the relationships between these tables; then delete
the record, not from a three-table query, but directly from the main
table. The deletions will cascade to all the related tables. Or, if
you prefer not to use the cascade, use a Form with subforms; delete
the child records in the subforms, and then the mainform record.