Re: I don't understand why this Delete Query fails?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Delete the relevant records from StreetNames

DELETE
FROM tbl_Street_Names
WHERE tbl_Street_Names.StreetNameID In
(SELECT StreetNameID
FROM [tbl_EC_Postcodes_not_used_in_Waypoints but_exist in_Street_Names)

Sometimes you can use DISTINCTROW to achieve the desired result, but usually that fails

DELETE DISTINCTROW tbl_Street_Names.*
FROM [tbl_EC_Postcodes_not_used_in_Waypoints but_exist in_Street_Names]
INNER JOIN tbl_Street_Names ON [tbl_EC_Postcodes_not_used_in_Waypoints
but_exist in_Street_Names].StreetNameID = tbl_Street_Names.StreetNameID;


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


efandango wrote:
I am trying to delete some records from one table with matching [StreetNameID] in another table, but when I try and run the delete query, i get an error:

'Could not delete from specified tables'. when I hit the help button for an explanation; I get this:

You tried to delete data from one or more tables, but the deletion could not be completed.

Possible causes:


You do not have permission to modify the table. To change your permissions assignments, see your system administrator or the table's creator. The database was opened for read-only access. The database is read-only for one of these reasons:

You used the OpenDatabase method and opened the database for read-only access. The database file is defined as read-only in the database server operating system or by your network. In a network environment, you do not have write privileges for the database file. In Microsoft Visual Basic, you used the Data control and set the ReadOnly property to True.


To delete the data, close the database, resolve the read-only condition, and then reopen the file for read/write access.




This is my query SQL:

DELETE tbl_Street_Names.*
FROM [tbl_EC_Postcodes_not_used_in_Waypoints but_exist in_Street_Names] INNER JOIN tbl_Street_Names ON [tbl_EC_Postcodes_not_used_in_Waypoints but_exist in_Street_Names].StreetNameID = tbl_Street_Names.StreetNameID;

.

Relevant Pages

  • Re: I dont understand why this Delete Query fails?
    ... (SELECT StreetNameID ... but why didn't my SQL work? ... "John Spencer" wrote: ... The database was opened for read-only access. ...
    (microsoft.public.access.queries)
  • Re: Secured Access to Forms
    ... could open and have read-only access) but when users try to open certain ... Currently I set security up on a database but it prompts for a password to ... database and info within but only certain users can change the data within. ... I would not assign a password to the guest account. ...
    (microsoft.public.access.security)
  • Re: I dont understand why this Delete Query fails?
    ... But the reason it didn't work is because it didn't work. ... I've just learned to rewrite my queries so that they conform to the way the database engine works. ... (SELECT StreetNameID ... The database file is defined as read-only in the database server operating system or by your network. ...
    (microsoft.public.access.queries)
  • Re: kerberos logon failure
    ... Not sure I understand the question - what do you mean by read-only access? ... If you look at sp_protects or something similar in the database are the ... > I recently started receiveing an error message on a Win2K server that has ... account experienced logon failure with Event ID 537. ...
    (microsoft.public.sqlserver.security)
  • RE: I dont understand why this Delete Query fails?
    ... Is StreetNameID a primary key in one of the tables? ... query to a select query, can you edit the records from tbl_Street_Names? ... The database was opened for read-only access. ... In a network environment, you do not have write privileges for the database ...
    (microsoft.public.access.queries)