How to delete records from MS Access table

[Martin Lee]

Hi. I am having difficulty deleting records from my ‘Holding Fee’ Table. The names used in the access code below are the table names and field names used in the database. When I try to run the delete query I am presented with the following ‘Microsoft Office Access’ Information Popup message:
‘Specify the table containing the records you want to delete’.
I have searched the internet for an answer and played with the code but I have not been able to effect the deletion of the records. The archive table contains the ‘three field values’ which together supply a ‘unique reference’ to those records which need to be deleted from the ‘Holding Fee’ table; ‘Location ID’, ‘Room Number’ and ‘Date Of Arrival’. (The departing occupant records have already been appended to the ‘Holding Fee Archive’ so, for now, I am using the ‘Holding Fee Archive’ table as a source for the field criterion, ‘values’ which need to be met. I thought a table may be a more reliable source than a query). I need syntax which will ‘FIND’ and ‘DELETE’ records on the basis of WHERE ‘field 1 in table A = value in field 1 of table B’ and ‘field 2 of table A = value in field 2 of table B’ and ‘field 3 in table A = value of field 3 in Table B’ DELETE THESE RECORDS. I tried the following but it did not work.

DELETE tblHoldingFee.[Loc_ID HF], tblHoldingFee.[RM_No HF], tblHoldingFee.DO_Arrival
FROM tblHoldingFee, tblArchive_HoldingFee
WHERE (([tblHoldingFee].[Loc_ID HF] Like [tblArchive_HoldingFee.Loc_ID HF]) AND ([tblHoldingFee].[RM_No HF] Like [tblArchive_HoldingFee].[RM_No HF]) AND ([tblHoldingFee].[DO_Arrival] Like [tblArchive_HoldingFee.DO_Arrival]));

I am using Office 2003 on Windows XP. I am the database administrator, no user level security has been set up and the database is not read only. Sorry, I am NOT a whiz with SQL. Anyone out there who can help me; it would be much appreciated. Thank you

[ADezii]

@Martin Lee
This can be done relatively easily with VBA Code utilizing Nested Recordset Loops, but I'll wait and see if someone comes up with a SQL-based solution.

[NeoPa]

You can only delete from an updatable recordset (See Reasons for a Query to be Non-Updatable). Your recordset consists of a Cartesian Product of two discrete tables. Certainly not updatable.

From your description I suspect you want something like :

DELETE tHF.*
FROM   tblHoldingFee AS tHF INNER JOIN
       tblArchive_HoldingFee AS tAHF
  ON   ([tHF].[Loc_ID HF]=[tAHF].[Loc_ID HF])
 AND   ([tHF].[RM_No HF]=[tAHF].[RM_No HF])
 AND   ([tHF].[DO_Arrival]=[tAHF].[DO_Arrival])

Welcome to Bytes!

[ADezii]

@NeoPa
Hello NeoPa. We're not talking about a Cartesian Product but two, Nested, Independent, Recordsets. The Nested loops will test for equality on the three Fields fora each Record in Table A against all Records in Table B. Should a match exist at an iteration, use the DELETE Method of the Recordset Object to remove the Record in Table A.

[NeoPa]

My comments were referring to the SQL posted by the OP, rather than what I was recommending ADezii.

[ADezii]

@NeoPa
Sorry NeoPa, off on a Tangent again!

[Martin Lee]

@NeoPa
Thank you NeoPa and Adezii for reading my long enquiry and for kindly responding. The solution offered by NeoPa worked Splendidly. Having searched for an answer for some hours prior to posting on bytes; receiving a solution so quickly was wonderful. Many, Many thanks to you both.

[NeoPa]

You're very welcome Martin, and I'm glad that suggestion hit the spot :)