Trying to delete a record from a query

[mp3909]

I am trying to remove a record from a query using the code below but keep getting an error on the "CurrentDb.Execute sSQL" line saying "Could not delete from specified tables".

Private Sub cmdLeft_Click()

dim sSQL as string

sSQL = "DELETE [EntityName]"
sSQL = sSQL & vbNewLine & "FROM qdf_EntityForCustomEmail"
sSQL = sSQL & vbNewLine & "WHERE EntityName IN ('ABC')

Debug.Print sSQL

CurrentDb.Execute sSQL

End Sub

Can someone please advise?

[isladogs]

Try using DELETE DISTINCTROW to specify unique records

[mp3909]

tried that but no luck....still getting same error

[JoeM]

Are the data tables actually housed in Access, or are they linked tables?
If so, where are they linked from, and are you able to actually edit those records at all from Access (if they are in Excel, you cannot; at least not that way).

Also to clarify one point, you do not really delete data from queries. The data would be deleted from the underlying tables.
If you do not want to delete the data from the underlying table, but rather you just want to not show it in the query, you wouldn't delete the data. You would just filter it out of the query using criteria.

[isladogs]

That's surprising.
Agree with all Joe's comments. If that doesn't explain it, please post the SQL for that query you are referencing.

Also have a look at the solution in this thread https://www.access-programmers.co.uk...16#post1614816
Explanation in post 9 but use the attachment from post 12

[mp3909]

Are the data tables actually housed in Access, or are they linked tables?
If so, where are they linked from, and are you able to actually edit those records at all from Access (if they are in Excel, you cannot; at least not that way).

Also to clarify one point, you do not really delete data from queries. The data would be deleted from the underlying tables.
If you do not want to delete the data from the underlying table, but rather you just want to not show it in the query, you wouldn't delete the data. You would just filter it out of the query using criteria.

The query I want to delete from is made from a table > that table gets populated via importing a csv file using the method DoCmd.Transfer text.

I don't want to remove any data from the table, I just want it to not appear in my query.
By the way, I am using this query to set my rowsource for a list box.

[isladogs]

Try this

SELECT * FROM qdf_EntityForCustomerEmail WHERE EntityName <>'ABC';

Note that you cannot run SELECT statements in VBA as there's nothing to execute.
However that is fine for your row source

[JoeM]

You cannot delete records from just a query without deleting them from the table.
You simply filter them out, like I said, and isladogs just posted the code for.

[mp3909]

cool, I just created a table and based my rowsource on that rather than a query.
It's working now, thank you

[isladogs]

Glad its working but I see no reason to have created a new table.
What I was suggesting was a query filtering what you already had.

Your query qdf_EntityForCustomEmail must be based on one or more tables (or is it just a query definition?)