Could not delete from specified tables??

[dirkg]

I'm having some difficulties with a delete-query I want to make. When I'm executing the query I get the message "Could not delete from specified tables" I do have all the needed rights though. I do use some relations in the query itself to select the data that I want to delete. When I preview this selection it works fine however. Nevertheless I tried to split up the query in subqueries but I still got the same error.
I've had the same problem before with update-queries too ("Could not update specified tables&quot and also that problem is not solved yet.
Do you know whether there was some particular change in Access 2000 that could cause this because I did not have this problem in previous versions and I used the same principles to make delet and update-queries then.

All help would be very appreciated.


Greetings,


Dirk


dirk.news@yucom.be

 

[THoey]

Could it be that you have set up some PK\FK relationships and you are trying to delete the PK's before removing the FK's? Terry M. Hoey

Please read the following FAQ to learn how to help me help you...

faq183-874

 

[dirkg]

no, the tables are not linked through any relationships with other tables....

Best regards,

Dirk

 

[dirkg]

OK, I'm one step further:

The situation is like this:
I have a table with contracts for several suppliers. Each contract has an end-date. From this table I want to delete the entries (=contracts), based on a cut-off date which is different for each supplier (=> so criterium is end-date contract < cut-off date supplier).
I get this cut-off date for each supplier via a query which looks at different tables. The result is a query where there is one record per supplier with the cut-off date for each supplier.

So in order to delete the entries from the contract-table, I make a query that looks like this:

DELETE [CONTRACTS].*
FROM [qrySUPPLIER_CUTOFF_DATE] INNER JOIN [CONTRACTS] ON [qrySUPPLIER_CUTOFF_DATE].SUPPLIER_ID = [CONTRACTS].SUPPLIER_ID
WHERE ((([CONTRACTS].ENDDATE)< [qrySUPPLIER_CUTOFF_DATE].[CUTOFF_DATE]));

CONTRACTS = table with contracts
ENDDATE = enddate of contract

SUPPLIER_CUTOFF_DATE = query yhat looks for the cut-off date for each supplier
CUTOFF_DATE = found cut-off date for the specific supplier

SUPPLIER_ID = unique ID for each supplier


When I execute this query I get the message &quot;Could not delete from specified tables&quot;
However when I use the following synthax it works:

DELETE [CONTRACTS].*
FROM [CONTRACTS]
WHERE ((([CONTRACTS].ENDDATE)< dlookup(&quot;CUTOFF_DATE&quot;,&quot;qrySUPPLIER_CUTOFF_DATE&quot;, &quot;SUPPLIER_ID = &quot; & [SUPPLIER_ID])));


Of course this is not optimal and it takes a lot longer than if it would be possible to perform the same operation with the first query. Therefore I would like to know whether it is not possible anymore to use relationships in delete-queries to select the data you want to delete. Before I never had problems with this...:-(

Thanks again for your help.


Greetings,


Dirk

dirk.news@yucom.be

 

[dirkg]

OK, I found the solution ):

I had to set the property &quot;Unique records&quot; of the query to true. That gives me then an SQL-expression like:

DELETE DISTINCTROW [CONTRACTS].*
FROM [qrySUPPLIER_CUTOFF_DATE] INNER JOIN [CONTRACTS] ON [qrySUPPLIER_CUTOFF_DATE].SUPPLIER_ID = [CONTRACTS].SUPPLIER_ID
WHERE ((([CONTRACTS].ENDDATE)< [qrySUPPLIER_CUTOFF_DATE].[CUTOFF_DATE]));


were the only difference is the DISTINCTROW-statement at the beginning. This makes my query work fine. The reason this is needed is I guess because the query I am linking to does not have SUPPLIER_ID as a key (since queries don't have keys) although that this query does give one record per supplier. I guess the way to say that to my little computer is to use the DISTINCTROW-statement.

Well I thought to share this with you so that if somebody else was having this problem he/she doesn't have to do all the cursing I have done ;-)

Greetings,

Dirk

dirk.news@yucom.be

 

[THoey]

Thanks for posting your solution... Terry M. Hoey

Please read the following FAQ to learn how to help me help you...

faq183-874