Avatar of stephenlecomptejr
Flag for United States of America asked on

How do I delete all rows in a table based on another table within Microsoft Access

How do I delete all rows in a table based on another table within Microsoft Access?
I'm trying some of the SQL statements online but they work mostly with SQL Server.

What's the proper syntax to get that working?
I tried this using the Design Query first and then changing it to Delete - but it says could not delete from specified tables.

FROM [DeleteThese] INNER JOIN [Jobs] ON DeleteThese.JobTicket = Jobs.JobTicket;

Open in new window


I tried this directly in the SQL View (due to the online SQL searches) but it says specify the table containing the records you want to delete.

DELETE FROM DeleteThese INNER JOIN Jobs ON DeleteThese.JobTicket = Jobs.JobTicket;

Open in new window

Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Daniel Pineault

8/22/2022 - Mon
Daniel Pineault

Could you explain the setup a little more and what you are trying to delete exactly?

Also, typically, if you setup your relationships properly with cascading deletions, when you delete the parent all the child entries are automatically deleted.

The first MS Access table (DeleteThese) has all the unique identifier's numbers that I want to delete from table: (Jobs) using the values found in JobTicket field.

There are no child entries tied to table:  Jobs
And DeleteThese is not related - just a created table telling the database which rows to delete.

There is a specific syntax that is written in MS Access SQL that will allow for this deletion.  I used to have the code syntax but its off in samples I can't access right now.

I could write a VBA script using DAO.Recordset that would loop through each record in table:  DeleteThese and delete such but I want the SQL syntax specifically (the one I put in Query Design) cause it's quicker to use in the future for other tables versus rewriting the code each time.

I also did a quick search for Microsoft Access SQL to do this particular deletion and nothing comes up except Microsoft SQL Server commands - thus the reason why I posted the question.
Daniel Pineault

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thank you Daniel, I appreciate it.  You the man.
I couldn't find that IN (SELECT syntax online).  Hopefully this helps other people as well searching for the same answer.
Your help has saved me hundreds of hours of internet surfing.
Daniel Pineault

Glad I could help.