Delete Query not working

[bw]

I'm receiving a message when trying to run a Delete Query: "Could not
delete from specified tables."

I can't determine what is causing the problem. The help message indicates
that it is a security problem, but I don't see how. Can someone give me
some things to look for that may be causing this problem?

Thanks,
Bernie

 

[Allen Browne]

If the query contains only ONE table, it should delete okay.

If it contains multiple tables, there may be an issue with which one you are
deleting from.

If you can't sort it out, switch the query to SQL View (View menu, in query
design), and paste the SQL statement as a reply to this thread.

 

[bw]

Thanks for the reply, Allen!

Here is the SQL code you asked for:

DELETE tblFlightNumberQuery.FltNum, Right([FltNum],4) AS Expr1
FROM tblFlightNumberQuery
WHERE (((Right([FltNum],4))="/xxx"));

This code selects the correct records to be deleted when I run it in design
mode.
Bernie

 

[Allen Browne]

Drop the field list from the DELETE clause, i.e.:
DELETE FROM tblFlightNumberQuery WHERE Right([FltNum],4) = "/xxx";

In the query design interface, you could probably achieve that by dragging
the * from the table into the grid. Access then puts "From" in the delete
row under that field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, Allen!

Here is the SQL code you asked for:

DELETE tblFlightNumberQuery.FltNum, Right([FltNum],4) AS Expr1
FROM tblFlightNumberQuery
WHERE (((Right([FltNum],4))="/xxx"));

This code selects the correct records to be deleted when I run it in
design mode.
Bernie

If the query contains only ONE table, it should delete okay.

If it contains multiple tables, there may be an issue with which one you
are deleting from.

If you can't sort it out, switch the query to SQL View (View menu, in
query design), and paste the SQL statement as a reply to this thread.

 

[bw]

Allen:
I copied your code, and it inserted a new column Expr1, with the value
"/xxx" in every row. (not what I wanted, I think). In addition, I still
got the original error message: "Could not delete from specified tables."

So then I used your next suggestion using the * to get the following SQL

DELETE tblFlightNumberQuery.*, tblFlightNumberQuery.FltNum
FROM tblFlightNumberQuery
WHERE ((Right([FltNum],4)="/xxx"));

In design view, this looks like what I want, but I get the same error on
this also: "Could not delete from specified tables."

Any more suggestions?
Bernie

Drop the field list from the DELETE clause, i.e.:
DELETE FROM tblFlightNumberQuery WHERE Right([FltNum],4) = "/xxx";

In the query design interface, you could probably achieve that by dragging
the * from the table into the grid. Access then puts "From" in the delete
row under that field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, Allen!

Here is the SQL code you asked for:

DELETE tblFlightNumberQuery.FltNum, Right([FltNum],4) AS Expr1
FROM tblFlightNumberQuery
WHERE (((Right([FltNum],4))="/xxx"));

This code selects the correct records to be deleted when I run it in
design mode.
Bernie

If the query contains only ONE table, it should delete okay.

If it contains multiple tables, there may be an issue with which one you
are deleting from.

If you can't sort it out, switch the query to SQL View (View menu, in
query design), and paste the SQL statement as a reply to this thread.

I'm receiving a message when trying to run a Delete Query: "Could not
delete from specified tables."

I can't determine what is causing the problem. The help message
indicates that it is a security problem, but I don't see how. Can
someone give me some things to look for that may be causing this
problem?

 

[bw]

By the way, I forgot to tell you about an anomaly when creating queries (IN
THIS DATABASE ONLY), which may have something to do with this problem.

When I select Queries>New>Simple Query Wizard and then CLICK the drop down
box for "Tables/Queries", I get the following dialog box, labeled "Microsoft
Access":
"The expression you entered On Got Focus you entered as the event property
setting produced the following error: The text you entered is not an item in
the list.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event or macro.

I don't know why I got this. I'm just using the tools provided by Access.

Bernie

Drop the field list from the DELETE clause, i.e.:
DELETE FROM tblFlightNumberQuery WHERE Right([FltNum],4) = "/xxx";

In the query design interface, you could probably achieve that by dragging
the * from the table into the grid. Access then puts "From" in the delete
row under that field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, Allen!

Here is the SQL code you asked for:

DELETE tblFlightNumberQuery.FltNum, Right([FltNum],4) AS Expr1
FROM tblFlightNumberQuery
WHERE (((Right([FltNum],4))="/xxx"));

This code selects the correct records to be deleted when I run it in
design mode.
Bernie

If the query contains only ONE table, it should delete okay.

If it contains multiple tables, there may be an issue with which one you
are deleting from.

If you can't sort it out, switch the query to SQL View (View menu, in
query design), and paste the SQL statement as a reply to this thread.

I'm receiving a message when trying to run a Delete Query: "Could not
delete from specified tables."

I can't determine what is causing the problem. The help message
indicates that it is a security problem, but I don't see how. Can
someone give me some things to look for that may be causing this
problem?

 

[Allen Browne]

You still have 2 fields in the DELETE clause. You need the * only (or
preferably none.)

The SQL statement I suggested was meant to go in SQL View, not in the Field
row as an expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
I copied your code, and it inserted a new column Expr1, with the value
"/xxx" in every row. (not what I wanted, I think). In addition, I still
got the original error message: "Could not delete from specified tables."

So then I used your next suggestion using the * to get the following SQL

DELETE tblFlightNumberQuery.*, tblFlightNumberQuery.FltNum
FROM tblFlightNumberQuery
WHERE ((Right([FltNum],4)="/xxx"));

In design view, this looks like what I want, but I get the same error on
this also: "Could not delete from specified tables."

Any more suggestions?
Bernie

Drop the field list from the DELETE clause, i.e.:
DELETE FROM tblFlightNumberQuery WHERE Right([FltNum],4) = "/xxx";

In the query design interface, you could probably achieve that by
dragging the * from the table into the grid. Access then puts "From" in
the delete row under that field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply, Allen!

Here is the SQL code you asked for:

DELETE tblFlightNumberQuery.FltNum, Right([FltNum],4) AS Expr1
FROM tblFlightNumberQuery
WHERE (((Right([FltNum],4))="/xxx"));

This code selects the correct records to be deleted when I run it in
design mode.
Bernie

If the query contains only ONE table, it should delete okay.

If it contains multiple tables, there may be an issue with which one
you are deleting from.

If you can't sort it out, switch the query to SQL View (View menu, in
query design), and paste the SQL statement as a reply to this thread.

I'm receiving a message when trying to run a Delete Query: "Could not
delete from specified tables."

I can't determine what is causing the problem. The help message
indicates that it is a security problem, but I don't see how. Can
someone give me some things to look for that may be causing this
problem?