Delete Query: Could not delete from specified tables.

[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.

 

[Michel Walsh]

Hi,


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


DELETE DISTINCTROW a.* FROM ...


Hoping it may help,
Vanderghast, Access MVP

 

[James Franklin]

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.

 

[James Franklin]

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.

 

[James Franklin]

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]

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

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,


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


DELETE DISTINCTROW a.* FROM ...


Hoping it may help,
Vanderghast, Access MVP

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

 

[James Franklin]

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.

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

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.

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