Access 2007: "cannot delete from specified tables" error

[T@abitha]

I have created a database in Access 2007 which I am running on Windows XP computer. The database is all about maintaining the records of the employees database who resides on the company's apartment. So as you may the street, area etc would be exactly same except the flat number since each one of them have been allotted different flats. So I want to delete the records of the employees who have left the company in order to make the note of how many flats are left with me unassigned. But the problem occurs when I here itself. Whenever I fire the delete query I have the message which says "cannot delete from specified tables". I don't know what it is reporting too and so I need your help. Can you help me in deleting those records or at least explain me what this error message indicates or refers to?

[Steyn]

There are number of possibilities which can result into this error message. May be you have cascaded queries, to delete an entry, which is not possible. Moreover if you are trying to use a single delete query to remove record from two different tables than that too isn't possible. Also make sure you don't use DISTINCT word in your delete query, this will not work. That is the limitation of the DELETE query that you can't use DISTINCT. Why don't you run the query first using just SELECT, instead of DELETE directly, to know what the outcome of the query is? This will clear out your question where the problem is occurring and why you are not able to carry out the delete operation.

[Jacques25]

You may receive the "cannot delete from specified tables" error message if the delete query contains multiple tables and the UniqueRecords property is set to No. To resolve this problem, set the UniqueRecords property of the query to Yes. To do this open the delete query in Design view, go to the View menu, and click Properties. Set the UniqueRecords property to Yes. Save the changes done in the query and then close it. With this changes, Access will include the DISTINCTROW predicate with the query. This retrieves unique records in a multi-table query where fields have only been selected from the one-sided table in the query.

[void]

The first thing that comes to my mind is that if the user has rights to delete the records. Make sure you have the sufficient privileges to delete the records. To check it out, go to the Design View by double clicking on the table. Now click on Properties button and check out the privileges. Moreover, you can not delete the data from a linked SQL table in Access 2007 without the primary key. If you are using JET database then you can even add DISTINCTROW in your query and see if that helps.

[$tatic]

If I were you then I would have gone for checking the ConnectionString of that database. Also make sure if you can see the file in the correct location where you are addressing your query to. The user right is of prime importance in this kind of operation. You must have read, write and delete permissions on the directory which contains the database. You must also see a lock file (ldb) on the same location. It should be created on the first user in and deleted on the last user out. If, by chance, anything goes improper over here then this may result you with a read-only database irrespective of the other permissions which it was set.