I don't understand why this Delete Query fails?

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

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;
 
Is StreetNameID a primary key in one of the tables? If you change the delete
query to a select query, can you edit the records from tbl_Street_Names?
 
well that error says that your DB is read onlywhich in all logical
sence is why you cannot delete it

overall your query seems fine you table structure is no to be desired
with a table name that is as long as your post but i suggest you check
the properties on the MDB file too see if it is read only and to see
if no one else has opened the file

Regards
Kelvan
 
Duane,

Yes, StreetName_ID is a primary key in tbl_Street_Names.
Ye, I can edit the names in tbl_Street_Names.



Duane Hookom said:
Is StreetNameID a primary key in one of the tables? If you change the delete
query to a select query, can you edit the records from tbl_Street_Names?

--
Duane Hookom
Microsoft Access MVP


efandango said:
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;
Click to expand...
Click to expand...
 
the tables(s) is not read-only.

The crazy name for the table is very much temporary. I am in the middle of a
complex, rer-ordering, restructuring, and renaming situation invlving
multiple tables, etc, my mind is full of various similar names, and i need to
be cystal clear about which tables i am deleting on...
 
well your query looks fine but try going into the table street names
and deleting a record you dont want manually without a query if you
can then your query is a fault if you cannot then your database is at
fault some how

Regards
Kelvan
 
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 said:
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;
Click to expand...

There is actually a bizarre bug in A97 where the table name can be one
character longer than the query processor can handle. I do not recall
the exact error message at this time.

Now your table name is 64 characters long. Just for grins, and if
none of the other suggestions work, try shortening the table name five
or ten characters.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
John, thank you.

your first SQL worked a treat:

DELETE
FROM tbl_Street_Names
WHERE tbl_Street_Names.StreetNameID In
(SELECT StreetNameID
FROM [tbl_EC_Postcodes_not_in_Waypoints but_in_Street_Names])


but why didn't my SQL work?

John Spencer said:
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
'====================================================

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;
Click to expand...
Click to expand...
 
I hate this. But the reason it didn't work is because it didn't work.
In other words, I don't know.

//SPECULATION MODE ON

In Access 97 that would probably have worked, but there seems to be a
change of some type in later versions. The change (if any) made it so
that if you have more than one table in the FROM clause Access can't
decide which table you want to delete records from.

/SPECULATION MODE OFF

I've just learned to rewrite my queries so that they conform to the way
the database engine works.

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

John, thank you.

your first SQL worked a treat:

DELETE
FROM tbl_Street_Names
WHERE tbl_Street_Names.StreetNameID In
(SELECT StreetNameID
FROM [tbl_EC_Postcodes_not_in_Waypoints but_in_Street_Names])


but why didn't my SQL work?

John Spencer said:
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
'====================================================

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;
Click to expand...
Click to expand...
Click to expand...
 
interesting notation because i tested his query in that format with
table1 and table2 and some dummy records and it worked fine though i
do like your speculation mode ^_^
 
Tony,

I shortened the table name, significantly; but it still didn't work.

John, I like that answer; in so much as I thought i was doing something
wrong. For the record, I am using Access 2007, and have come across this
problem on a number of tables in my DB. On those very same tables, I am able
to update the fields/records and pretty much do whatever i want with them,
except delete...



Tony Toews said:
efandango said:
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;
Click to expand...

There is actually a bizarre bug in A97 where the table name can be one
character longer than the query processor can handle. I do not recall
the exact error message at this time.

Now your table name is 64 characters long. Just for grins, and if
none of the other suggestions work, try shortening the table name five
or ten characters.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Click to expand...
 
Back
Top