Can't delete from tables

[Scott]

I'm running the following query:

DELETE tblAllDataMerged.*, tblExcludedRecords.SSN
FROM tblAllDataMerged INNER JOIN tblExcludedRecords ON
tblAllDataMerged.SSN = tblExcludedRecords.SSN;

Access 2003 is responding that it, "Could not delete from specified
tables." I can't figure out why... can somebody tell me what I'm doing
wrong?

Thanks!

Scott

 

[Jeff L]

Try this instead:
DELETE FROM tblAllDataMerged
Where SSN IN(Select SSN from tblExcludedRecords)

If you need the records deleted from tblExcludedRecords too, set up a
relationship in your Database. Click Relationships under the Tools
menu. Select your two tables from the list, link them on SSN, and
select Cascade Deleted Fields. Now when a SSN is deleted from
tblAllDataMerged, it will be deleted from tblExcludedRecords at the
same time.

 

[Scott]

A follow-up... the tables are not read-only...
Deleting goes fine when I run a delete query on either table
individually. I can also manually delete from the tables.

When I join the two tables, however, it gives me the "could not delete"
error.

 

[Michel Walsh]

Hi,


to delete from one table over a join, you add DISTINCTROW:

DELETE DISTINCTROW tblAllDataMerged.*
FROM tblAllDataMerged INNER JOIN tblExcludedRecords
ON tblAllDataMerged.SSN = tblExcludedRecords.SSN;



Hoping it may help,
Vanderghast, Access MVP