Delete query not running

  • Thread starter Thread starter Sirocco
  • Start date Start date
S

Sirocco

I'm using Access 2000 with Windows NT. My delete query gives me the message
"Can not delete these records". The structure of the query seems to be
right, and displays the expected records when I press the "View" button in
upper left corner of screen. I've been doing this too long to need help
with what seems like such a simple thing. I've had no other problems
working with this database, including deleting individual records using
other methods besides a delete query, and my user account has full
permissions. Is there be a "special" security issue with this database
that only applies to delete queries? Why won't my delete query run?

Many thanks in advance.
 
I'm using Access 2000 with Windows NT. My delete query gives me the message
"Can not delete these records". The structure of the query seems to be
right, ...
Click to expand...

Please post the SQL string. Otherwise it's difficult to help.

Peter
 
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;
 
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;
Click to expand...

Ok, what table are you trying to delete? (Same question why Access can't
handle it ;-)

If it is [073-tblBSAddressDeleteList] try that:

DELETE FROM [073-tblBSAddressDeleteList] AS A
WHERE A.DebtorAddressID In
(SELECT B.DebtorAddressID FROM tblDebtorAddresses AS B
WHERE B.DebtorAddressID = A.DebtorAddressID);

(not tested.)

If you want to delete tblDebtorAddresses just swap the 2 tables between
main and sub query and leave the alias as it is.

HTH - Peter
 
I need to use a subquery? I wouldn't have guessed. I've used joined
tables before in delete queries in Access 97, don't they work in Access
2000? Is this an Acess 2000 "improvement"?


Peter Doering said:
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;
Click to expand...

Ok, what table are you trying to delete? (Same question why Access can't
handle it ;-)

If it is [073-tblBSAddressDeleteList] try that:

DELETE FROM [073-tblBSAddressDeleteList] AS A
WHERE A.DebtorAddressID In
(SELECT B.DebtorAddressID FROM tblDebtorAddresses AS B
WHERE B.DebtorAddressID = A.DebtorAddressID);

(not tested.)

If you want to delete tblDebtorAddresses just swap the 2 tables between
main and sub query and leave the alias as it is.

HTH - Peter
Click to expand...
 
I need to use a subquery? I wouldn't have guessed. I've used joined
tables before in delete queries in Access 97, don't they work in Access
2000? Is this an Acess 2000 "improvement"?
Click to expand...

I still don't know from which table you want to delete (maybe both?), so
I've given you the best I could. It's a clean solution anyway.

Peter
 
I want to delete from tblDebtorAddresses. I'll take you're advice and hope
for the best. Thank you!
 
Looks like you have your join backwards.

DELETE *
FROM tblDebtorAddresses INNER JOIN [073-tblBSAddressDeleteList] ON
tblDebtorAddresses.DebtorAddressID =
[073-tblBSAddressDeleteList].DebtorAddressID;

Kelvin

Sirocco said:
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;


Peter Doering said:
Please post the SQL string. Otherwise it's difficult to help.

Peter
Click to expand...
Click to expand...
 
Whoops, your suggestion isn't working. Is the syntax different for Access
97 and 2000? Is there a magic word I should know?

Thanks in advance.
 
Whoops, your suggestion isn't working. Is the syntax different for Access
97 and 2000? Is there a magic word I should know?
Click to expand...

It's the same on 97 and 2000 and 2002 ...

Did you follow my instructions to swap the table names in case you wanted
to delete from tblDebtorAddresses?

Here the updated syntax:

DELETE FROM tblDebtorAddresses AS A
WHERE A.DebtorAddressID In
(SELECT B.DebtorAddressID FROM [073-tblBSAddressDeleteList] AS B
WHERE B.DebtorAddressID = A.DebtorAddressID);

and in words:

Delete rows from tblDebtorAddresses that have a corresponding entry in
[073-tblBSAddressDeleteList] with the same DebtorAddressID.

HTH - Peter
 
Sirocco said:
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;


Peter Doering said:
Please post the SQL string. Otherwise it's difficult to help.
Click to expand...
Click to expand...
Hi Sirocco,

Here is something that changed in 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;207761


*** QUOTE *****
Delete Queries
***************************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes
for all versions of Microsoft Access.
****************************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new delete
query in Access 2000.
*** UNQUOTE ****

DELETE DISTINCTROW tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;


Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Thanks Gary, the UniqueRecords property is the answer I was looking for!
There are so many properties to these database objects how can anyone keep
them straight, esp if microsoft keeps fiddling with them. My joins were
correct, as was my overall strategy. Other than changing this property
from No to Yes, my original query would have worked! Thumbs down to
Microsoft for not leaving a good thing alone and completely disrupting my
learning curve.

And thank you Peter I eventually got the query to work with the subquery
also, it's an interesting, alternate approach that may come in handy in the
future.


Gary Walter said:
Sirocco said:
Here's the SQL string (and thanks for helping):

DELETE tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;


Peter Doering said:
On Mon, 10 Nov 2003 12:10:07 -0500, Sirocco wrote:

I'm using Access 2000 with Windows NT. My delete query gives me the message
"Can not delete these records". The structure of the query seems
Click to expand...
to
be
right, ...

Please post the SQL string. Otherwise it's difficult to help.
Click to expand...
Click to expand...
Hi Sirocco,

Here is something that changed in 2000

http://support.microsoft.com/default.aspx?scid=kb;en-us;207761


*** QUOTE *****
Delete Queries
***************************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes
for all versions of Microsoft Access.
****************************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new delete
query in Access 2000.
*** UNQUOTE ****

DELETE DISTINCTROW tblDebtorAddresses.*
FROM [073-tblBSAddressDeleteList] INNER JOIN tblDebtorAddresses ON
[073-tblBSAddressDeleteList].DebtorAddressID =
tblDebtorAddresses.DebtorAddressID;


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
Back
Top