Deleting Records with a Query

[Thales750]

This is strange; I don’t seem to be able to control deletions within a query.


1. If I run this query, and try to delete the records it populates, it will delete the records from the 1temp table and not the ones from tblContact.

SELECT tblContacts.ContactID, tblContacts.*
FROM tblContacts INNER JOIN 1temp ON tblContacts.ContactID = [1temp].ContactID;


2. This simply will not run


DELETE tblContacts.ContactID, tblContacts.*
FROM tblContacts INNER JOIN 1temp ON tblContacts.ContactID = [1temp].ContactID;



This does not happen with other tables, that I know of.

 

[Trevor G]

Have you looked at the relationship window to see the relationship type and does it show you can cascade update and delete?

 

[Thales750]

I don't believe that it is required to have that type of relationship.

I dont seem to be able to make any delete query work when the where statement references another table or query.

DELETE tblEventCurriculumAttendees.CurriculumAttendeesID
FROM tblEventCurriculumAttendees INNER JOIN qryEventCurriculumAttendeesDeleteFilter ON tblEventCurriculumAttendees.CurriculumAttendeesID = qryEventCurriculumAttendeesDeleteFilter.ToBeDeleted

;

this doesn't work either.

I get the error message "please select table"

 

[vbaInet]

What are you trying to do? This is the structure of a DELETE statement.

DELETE [NameOfField][/SIZE][/FONT][FONT=Calibri][SIZE=3] FROM tblContacts WHERE [ContactID] = 1;[/SIZE][/FONT]

For example, no Inner Joins. The syntax of a SELECT statement is different from action queries (like a DELETE statement).

Here's a link:
http://www.techonthenet.com/sql/delete.php

 

[SOS]

I think this should work:

[FONT=Calibri][SIZE=3]DELETE * FROM tblContacts INNER JOIN 1temp ON tblContacts.ContactID = [1temp].ContactID;[/SIZE][/FONT]

 

[vbaInet]

I think this should work:

[FONT=Calibri][SIZE=3]DELETE * FROM tblContacts INNER JOIN 1temp ON tblContacts.ContactID = [1temp].ContactID;[/SIZE][/FONT]

Wasn't sure about the Inner Join SOS. Have you tried it this way?

 

[SOS]

I've done something like it joining to a table for the criteria but at the moment I'm trying to get the exact wording.

 

[vbaInet]

I've done something like it joining to a table for the criteria but at the moment I'm trying to get the exact wording.

Ah alright.

OP: Here's a link for you -
http://www.access-programmers.co.uk/forums/showthread.php?t=149986

 

[SOS]

Ah alright.

OP: Here's a link for you -
http://www.access-programmers.co.uk/forums/showthread.php?t=149986

Yes, I think George's post (#7) in that thread is the key. I'm not sure about what I stated originally as I can't find where I used it. But the code George gives is the preferred method anyway.