delete query

[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]));

 

[Gerald Stanley]

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

 

[Dennis]

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

 

[John Spencer (MVP)]

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.

 

[vbdotnetmania]

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 )

(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?

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]));

 

[vbdotnetmania]

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.

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 )

(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?

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]));

 

[John Spencer (MVP)]

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.

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 )

(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?

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]));