Delete Query: Could not delete from specified tables.

  • Thread starter Thread starter James Franklin
  • Start date Start date
J

James Franklin

Hi,

Using A2K, I have a delete query which contains a table joined on two fields
(inner join) to a select query. The query deleted all fields from the table,
no fields from the query are selected.

When I run the query, I get the error message "Could not delete from
specified tables."

I have looked at the article

http://support.microsoft.com/default.aspx?scid=kb;en-us;240098&Product=acc20
00

which refers to this and have set the UniqueRecords property to YES as
suggested. However the problem still persists.

Please can anyone help?

Thanks,
Jim F.
 
Hi,


If you are using Jet, try adding DISTINCTROW, as in


DELETE DISTINCTROW a.* FROM ...


Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Thanks for the help. Unfortunately, I have tried that, as Access puts this
in when you set the UniqueRecords property.

If it helps, my SQL reads:

DELETE DISTINCTROW tbl_LettersNext.*
FROM tbl_LettersNext INNER JOIN [Orders Despatch - Letters1] ON
(tbl_LettersNext.Customer_ID = [Orders Despatch - Letters1].[Customer ID])
AND (tbl_LettersNext.Product_Group = [Orders Despatch - Letters1].Group)
WHERE (((tbl_LettersNext.NextLetterDate) Between DateAdd("d",1,[Order Date])
And DateAdd("d",56,[Order Date])) AND ((tbl_LettersNext.Type)>1));

[Order Date] is a field in my select query, but I don't think having this in
the criteria makes any difference. (I tried removing it.)

Any other ideas, anyone, please...?

Thanks again,
Jim F.
 
Hi Michel,

Thanks for the help. Unfortunately, I have tried that, as Access puts this
in when you set the UniqueRecords property.

If it helps, my SQL reads:

DELETE DISTINCTROW tbl_LettersNext.*
FROM tbl_LettersNext INNER JOIN [Orders Despatch - Letters1] ON
(tbl_LettersNext.Customer_ID = [Orders Despatch - Letters1].[Customer ID])
AND (tbl_LettersNext.Product_Group = [Orders Despatch - Letters1].Group)
WHERE (((tbl_LettersNext.NextLetterDate) Between DateAdd("d",1,[Order Date])
And DateAdd("d",56,[Order Date])) AND ((tbl_LettersNext.Type)>1));

[Order Date] is a field in my select query, but I don't think having this in
the criteria makes any difference. (I tried removing it.)

Any other ideas, anyone, please...?

Thanks again,
Jim F.
 
Hi Michel,

Thanks for the help. Unfortunately, I have tried that, as Access puts this
in when you set the UniqueRecords property.

If it helps, my SQL reads:

DELETE DISTINCTROW tbl_LettersNext.*
FROM tbl_LettersNext INNER JOIN [Orders Despatch - Letters1] ON
(tbl_LettersNext.Customer_ID = [Orders Despatch - Letters1].[Customer ID])
AND (tbl_LettersNext.Product_Group = [Orders Despatch - Letters1].Group)
WHERE (((tbl_LettersNext.NextLetterDate) Between DateAdd("d",1,[Order Date])
And DateAdd("d",56,[Order Date])) AND ((tbl_LettersNext.Type)>1));

[Order Date] is a field in my select query, but I don't think having this in
the criteria makes any difference. (I tried removing it.)

Any other ideas, anyone, please...?

Thanks again,
Jim F.
 
Hi,


Check if [Orders Despatch - Letters1] is updateable. If not, the query
involving it is not either, generally, since all the "tables" must be
updateable, even if you delete from just one.


If so, one solution is to push the relevant data into a temp table, and
use that temp table (which is updateable) rather than using the query (which
is not updateable).


Hoping it may help,
Vanderghast, Access MVP


James Franklin said:
Hi Michel,

Thanks for the help. Unfortunately, I have tried that, as Access puts this
in when you set the UniqueRecords property.

If it helps, my SQL reads:

DELETE DISTINCTROW tbl_LettersNext.*
FROM tbl_LettersNext INNER JOIN [Orders Despatch - Letters1] ON
(tbl_LettersNext.Customer_ID = [Orders Despatch - Letters1].[Customer ID])
AND (tbl_LettersNext.Product_Group = [Orders Despatch - Letters1].Group)
WHERE (((tbl_LettersNext.NextLetterDate) Between DateAdd("d",1,[Order Date])
And DateAdd("d",56,[Order Date])) AND ((tbl_LettersNext.Type)>1));

[Order Date] is a field in my select query, but I don't think having this in
the criteria makes any difference. (I tried removing it.)

Any other ideas, anyone, please...?

Thanks again,
Jim F.

Michel Walsh said:
Hi,


If you are using Jet, try adding DISTINCTROW, as in


DELETE DISTINCTROW a.* FROM ...


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
http://support.microsoft.com/default.aspx?scid=kb;en-us;240098&Product=acc20
Click to expand...
 
Hi,

Thanks again Michel. I checked the [Orders Despatch - Letters1] query, which
is updateable. So I guess I have no choice but to drop the data into a temp
table. Was hoping not to have to do this, but...

If anyone else has any ideas....thanks as always,

Jim F.

Michel Walsh said:
Hi,


Check if [Orders Despatch - Letters1] is updateable. If not, the query
involving it is not either, generally, since all the "tables" must be
updateable, even if you delete from just one.


If so, one solution is to push the relevant data into a temp table, and
use that temp table (which is updateable) rather than using the query (which
is not updateable).


Hoping it may help,
Vanderghast, Access MVP


James Franklin said:
Hi Michel,

Thanks for the help. Unfortunately, I have tried that, as Access puts this
in when you set the UniqueRecords property.

If it helps, my SQL reads:

DELETE DISTINCTROW tbl_LettersNext.*
FROM tbl_LettersNext INNER JOIN [Orders Despatch - Letters1] ON
(tbl_LettersNext.Customer_ID = [Orders Despatch - Letters1].[Customer ID])
AND (tbl_LettersNext.Product_Group = [Orders Despatch - Letters1].Group)
WHERE (((tbl_LettersNext.NextLetterDate) Between DateAdd("d",1,[Order Date])
And DateAdd("d",56,[Order Date])) AND ((tbl_LettersNext.Type)>1));

[Order Date] is a field in my select query, but I don't think having
Click to expand...
this
in
the criteria makes any difference. (I tried removing it.)

Any other ideas, anyone, please...?

Thanks again,
Jim F.
Click to expand...
http://support.microsoft.com/default.aspx?scid=kb;en-us;240098&Product=acc20
Click to expand...
 
Back
Top