delete query

  • Thread starter Thread starter Dick
  • Start date Start date
D

Dick

Why won't this query work? Error message is: "Cannot
delete from specified tables."


DELETE APhist_PS.*, APhist_PS.[Voucher ID]
FROM APhist_PS, ap_LastVoucher
WHERE (((APhist_PS.[Voucher ID])<[MaxOfVoucher ID]));
 
This is probably because the file ap_LastVoucher is
included in the FROM clause with no reference anywhere else
in the statement.

Hope This Helps
Gerald STanley MCSD
 
Dick,

I am a hack at best but I think I recently had a similar
problem. I do not know how to write code but I can explain
my experience and you may be able to figure out a soultion
or at least be working on a solution until some who knows
what they are doing can help you.

In my delete query I had the same message and I found that
I was trying to delete a field basicly twice once to
delete the whole record and then the field itself and
access could not do that. I found that in design view I
found the offending field and unstead of having the field
deleted I typed, where the field name went, Remove From
Report. I inclosed the SQL code so you may be able to
compare and spot the problem as this Delete query works
and sounds similar to yours.

DELETE DISTINCTROW [INMATE RECORDS].*, BUS.[CDC #],
[INMATE RECORDS].[Remove From Report]
FROM [INMATE RECORDS] LEFT JOIN BUS ON [INMATE RECORDS].
[CDC #] = BUS.[CDC #]
WHERE (((BUS.[CDC #]) Is Null) AND (([INMATE RECORDS].
[Remove From Report]) Is Null)) OR ((([INMATE RECORDS].
[Remove From Report])=No));

Hope this helps?

Dennis
 
Try using a subquery to get your criteria for the comparison.

DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] <
(SELECT Max(Voucher ID)
FROM Ap_LastVoucher)

I had to guess on the Names of your fields and table, but hope the above will
give you the idea of what you need.

TEST it on a COPY of your data. Once it runs (if it runs), those records are gone.
 
going one stage further what if the criteria involves more than just voucherid say samething like orderid was also a field in Ap_LastVoucher that you wanted the criteria to be based on, the only way I can get this to work is something like
DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] & APHist_PS.[Order Id] in
(SELECT (Voucher ID) & [Order Id]
FROM Ap_LastVoucher where [voucher id] > 2 and [order id] < 20 )
Click to expand...

(note this is not always going to be totally accurate even - won't go into that)

basically this works except for one little proviso which I won't go into to avoid confusing the isue - but the main problem is it's not only cumbersome but really really slow is there a way around this?

John Spencer (MVP) said:
Try using a subquery to get your criteria for the comparison.

DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] <
(SELECT Max(Voucher ID)
FROM Ap_LastVoucher)

I had to guess on the Names of your fields and table, but hope the above will
give you the idea of what you need.

TEST it on a COPY of your data. Once it runs (if it runs), those records are gone.
Why won't this query work? Error message is: "Cannot
delete from specified tables."

DELETE APhist_PS.*, APhist_PS.[Voucher ID]
FROM APhist_PS, ap_LastVoucher
WHERE (((APhist_PS.[Voucher ID])<[MaxOfVoucher ID]));
Click to expand...
Click to expand...
 
for those interested there is an easy way of doing this in transact -sql-extension

delete tbl1 from tbl1 INNER JOIN tbl2 on
tbl1.tblid = tbl2.tblid and
tbl1.2ndid = tbl2.2ndid

this doesn't work in access unfortunately - it does in SQL Server 2000 -
hmm.. anybody got any ideas of an easy way to do this in access, maybe I just have to make the mve to SQL Server.

vbdotnetmania said:
going one stage further what if the criteria involves more than just voucherid say samething like orderid was also a field in Ap_LastVoucher that you wanted the criteria to be based on, the only way I can get this to work is something like
DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] & APHist_PS.[Order Id] in
(SELECT (Voucher ID) & [Order Id]
FROM Ap_LastVoucher where [voucher id] > 2 and [order id] < 20 )
Click to expand...

(note this is not always going to be totally accurate even - won't go into that)

basically this works except for one little proviso which I won't go into to avoid confusing the isue - but the main problem is it's not only cumbersome but really really slow is there a way around this?

John Spencer (MVP) said:
Try using a subquery to get your criteria for the comparison.

DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] <
(SELECT Max(Voucher ID)
FROM Ap_LastVoucher)

I had to guess on the Names of your fields and table, but hope the above will
give you the idea of what you need.

TEST it on a COPY of your data. Once it runs (if it runs), those records are gone.
Why won't this query work? Error message is: "Cannot
delete from specified tables."

DELETE APhist_PS.*, APhist_PS.[Voucher ID]
FROM APhist_PS, ap_LastVoucher
WHERE (((APhist_PS.[Voucher ID])<[MaxOfVoucher ID]));
Click to expand...
Click to expand...
Click to expand...
 
The SQL you displayed should work in Access - at least with a small change.
Strange as it seems specify a field in the table that you want to delete From.
Something like:

DELETE Tbl1.tblID
FROM tbl1 INNER JOIN tbl2 On
tbl1.tblid = tbl2.tblid and
tbl1.2ndid = tbl2.2ndid

That query doesn't involve the complexity of identifying some records that match
for those interested there is an easy way of doing this in transact -sql-extension

delete tbl1 from tbl1 INNER JOIN tbl2 on
tbl1.tblid = tbl2.tblid and
tbl1.2ndid = tbl2.2ndid

this doesn't work in access unfortunately - it does in SQL Server 2000 -
hmm.. anybody got any ideas of an easy way to do this in access, maybe I just have to make the mve to SQL Server.

vbdotnetmania said:
going one stage further what if the criteria involves more than just voucherid say samething like orderid was also a field in Ap_LastVoucher that you wanted the criteria to be based on, the only way I can get this to work is something like
DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] & APHist_PS.[Order Id] in
(SELECT (Voucher ID) & [Order Id]
FROM Ap_LastVoucher where [voucher id] > 2 and [order id] < 20 )
Click to expand...

(note this is not always going to be totally accurate even - won't go into that)

basically this works except for one little proviso which I won't go into to avoid confusing the isue - but the main problem is it's not only cumbersome but really really slow is there a way around this?

John Spencer (MVP) said:
Try using a subquery to get your criteria for the comparison.

DELETE APHist_PS.*
FROM APHIST_PS
WHERE APHist_PS.[Voucher Id] <
(SELECT Max(Voucher ID)
FROM Ap_LastVoucher)

I had to guess on the Names of your fields and table, but hope the above will
give you the idea of what you need.

TEST it on a COPY of your data. Once it runs (if it runs), those records are gone.

Dick wrote:

Why won't this query work? Error message is: "Cannot
delete from specified tables."

DELETE APhist_PS.*, APhist_PS.[Voucher ID]
FROM APhist_PS, ap_LastVoucher
WHERE (((APhist_PS.[Voucher ID])<[MaxOfVoucher ID]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top