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?

 

[bw]

I'm confused (to say the least).

I copied your code DELETE FROM tblFlightNumberQuery WHERE Right([FltNum],4)
= "/xxx";
into the SQL view window, then SAVED IT.

When viewing in SQL mode, the code is as follows:
DELETE *
FROM tblFlightNumberQuery
WHERE Right([FltNum],4)="/xxx";

When I run this in design mode, I get the correct number of records to
delete.
When I run this to actually delete records, I still get the original
message: "Could not delete from specified tables."

When I view the above code in Design mode, and save it, the SQL code changes
as follows:
Access makes this change, NOT ME.

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

When I run this in design mode, I get the correct number of records to
delete.
When I run it to actually delete the records, I still get that original
message: "Could not delete from specified tables."

I have since exported all the tables, queries, forms and reports to a new
blank database. I no longer get that problem when creating new queries I
mentioned in another post.

I also wanted to thank your for the link to your Tips for Access Users.

Any more suggestions?
Bernie

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: DELETE FROM tblFlightNumberQuery WHERE
Right([FltNum],4) = "/xxx";

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?

 

[Allen Browne]

Access does mess around with the query statements, so that does not
necessarily indicate a problem.

If it shows the correct records to delete, and then fails ONLY IN THIS
DATABASE, then there has to be another problem here. It could be permissions
(if you have secured the database), or it could be a corruption.

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

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

I'm confused (to say the least).

I copied your code DELETE FROM tblFlightNumberQuery WHERE
Right([FltNum],4) = "/xxx";
into the SQL view window, then SAVED IT.

When viewing in SQL mode, the code is as follows:
DELETE *
FROM tblFlightNumberQuery
WHERE Right([FltNum],4)="/xxx";

When I run this in design mode, I get the correct number of records to
delete.
When I run this to actually delete records, I still get the original
message: "Could not delete from specified tables."

When I view the above code in Design mode, and save it, the SQL code
changes as follows:
Access makes this change, NOT ME.

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

When I run this in design mode, I get the correct number of records to
delete.
When I run it to actually delete the records, I still get that original
message: "Could not delete from specified tables."

I have since exported all the tables, queries, forms and reports to a new
blank database. I no longer get that problem when creating new queries I
mentioned in another post.

I also wanted to thank your for the link to your Tips for Access Users.

Any more suggestions?
Bernie

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: DELETE FROM tblFlightNumberQuery
WHERE Right([FltNum],4) = "/xxx";

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?