Unanswered: Delete records from select query from linked table ->error
I've made a select query querying two tables; the result is display in the datasheet and then I want to delete those records manually (just hit the delete-button).
Then I got a message in the status-bar: "Records not deleted. Data is read-only" .
Why can't I delete those records? The records I try to delete are from a linked access-table, but should'nt be a problem am I right? What is causing the read-only ? Is that because of the linking? First I thought it had something to do with the fact that the linked table was from an Access-97 database, but after I converted that db to access2000 (which is same version as the database which contains the query) that didn't make any difference...
If the table is being used by say a form while you use your query you may get a message on opening the table that it is being used and asks if you want to read for viewing anyhow. I assume this is not your DB mode. However, you do raise a question about linking. Is your table a link to an external file? If so it will have a right pointing arrow on the left of the table name in the table tab listing, followed by a file type icon (X- for Excel, torquoise notepad for text file, etc.). This latter case is definitely a readonly situation. I'm outa guesses w/ the amount of data given thus far.
If you use joins (more than one table) in your query, there is a chance that you can't modify the records. It might have several reasons.
(a) Access doesn't know which table it should delete records from.
(b) Depends on the how complex your query; it returns read-only results. Because changing a record affects records in another table. Which is not always what you want.
(c) There might be other reasons that I don't know too.
I'll describe the situation. I've got two databases: 'One' is the major database of our system containing lots of data. 'Two' is a database which I'm 'developing' at the moment; it contains a few tables, among them is a linked table from database One. In database Two I do a lot of querying and the result of those queries is a small table with records which should be deleted from the linked table in database One.
So I use this result table as as basis for the delete query: Delete * from Translations (which is the name of the table in database One) where Translations.shop = Result.shop and Translations.id = Result.id
And yes, there's a form active...which has a button on it which triggers a query that does domething with the linked table...
So with joins, I can get a good result in terms of selecting the right records, but I do need to delete those records from a table in Database One...
this is what I have now:
FROM tblTranslation_Table INNER JOIN tblResult ON (tblTRANSLATION_TABLE.COMPANY_ID = tblResult.Shop ) AND (tblTRANSLATION_TABLE.EAN_INSTORE= tblResult.instore_code )
WHERE tblResult.Los = -1;
The "tblresult.Los=-1" is the trigger for the records which should be deleted from tblTranslation_table (which is the linked table from the other database).
Right, ghozy. To check out your situation quickly, mike, try making your select query a delete query. If conditions allow, it should be a pickable option, else I think if you try to brute force it in the sql view (by inserting delete/other key words) it may error out with better info similar to ghozy's reply.
First, I am kind of curious as to why you're deleting records. Are these records errors, or obsolete? For historical purposes, you might want to make them 'inactive' (or somthing similar) instead of deleting records. But that's a procedural issue, not programming.
As for the programming, depending on the record-locking you have set and the join type, you cannot delete from a query if more than one table is involved. This is true for one-to-many relationships, and possibly for those with referential integrity.
You can delete a record if the table is being used by a form. I've done this with a "delete record" button, although some coding is required to refresh the list so the deleted record doesn't show.
You should also check the permissions from the first database to make sure it's not read-only. If you copied it onto a CD and pasted it in order to test on a 'safe' copy, then by default, the database became read-only. Just go through Windows Explorer and re-set it.
@ghozy: I tried something similar and got the same error as I get from your delete -statement: "Specify the table containing the records you want to delete"
@poliarci: converting the select-query into a delete-query produces the same error as mentioned above:
@Lisa: These records are errors indeed, inactivation is - for technical reasons - not an option, so that leaves deleting them.... The table isn't read-only either, so that too isn't the solution. Lisa, as you say that I can't delete records from a table when >1 tables are involved, this must have been something to do with the linking???
In an other situation I used this select query:
WHERE (TRANSLATION_TABLE.company_id) In (SELECT compid frOM loskopp27)
and (TRANSLATION_TABLE.ean_instore) In (SELECT matchid FROM loskopp27); "
In this case the loskopp27-table is part of the same database and then I can delete the selected records without any problem....
Are you good in VBA? If so, how about running a SELECT query that finds the records you want to delete, and save this to a recordset. Then, use an array to store the values of the primary keys (that identify a unique record) by using a FOR EACH statement. Then, run another procedure (a DELETE query or IF/THEN/ELSE) that goes through the table (on its own, not while linked to another table through a query) and deletes the records that match the numbers in the array. I'm not familiar enough with VBA to whip out some code on the spot, but this is an idea you might want to think about.