By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,693 Members | 1,737 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,693 IT Pros & Developers. It's quick & easy.

How to delete records from MS Access table

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. DELETE tblHoldingFee.[Loc_ID HF], tblHoldingFee.[RM_No HF], tblHoldingFee.DO_Arrival
  2. FROM tblHoldingFee, tblArchive_HoldingFee
  3. 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
Jul 26 '10 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. DELETE tHF.*
  2. FROM   tblHoldingFee AS tHF INNER JOIN
  3.        tblArchive_HoldingFee AS tAHF
  4.   ON   ([tHF].[Loc_ID HF]=[tAHF].[Loc_ID HF])
  5.  AND   ([tHF].[RM_No HF]=[tAHF].[RM_No HF])
  6.  AND   ([tHF].[DO_Arrival]=[tAHF].[DO_Arrival])
Welcome to Bytes!

Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,703
@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.
Jul 26 '10 #2

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. DELETE tHF.*
  2. FROM   tblHoldingFee AS tHF INNER JOIN
  3.        tblArchive_HoldingFee AS tAHF
  4.   ON   ([tHF].[Loc_ID HF]=[tAHF].[Loc_ID HF])
  5.  AND   ([tHF].[RM_No HF]=[tAHF].[RM_No HF])
  6.  AND   ([tHF].[DO_Arrival]=[tAHF].[DO_Arrival])
Welcome to Bytes!
Jul 26 '10 #3

ADezii
Expert 5K+
P: 8,703
@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.
Jul 27 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
My comments were referring to the SQL posted by the OP, rather than what I was recommending ADezii.
Jul 27 '10 #5

ADezii
Expert 5K+
P: 8,703
@NeoPa
Sorry NeoPa, off on a Tangent again!
Jul 27 '10 #6

P: 2
@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.
Jul 27 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
You're very welcome Martin, and I'm glad that suggestion hit the spot :)
Jul 28 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.