We help IT Professionals succeed at work.

DELETE QUERY not working.

Tony
Tony asked
on
Hi,

I am using MS-ACCESS as a front end.  MySql as Back end.

I have a DELETE query, to delete the content of a Table (No conditionals on the query at all, so delete all the records in the table).

But for some reason, the query is deleting all the records, except one record.   I executed the DEL query again an still not able to delete that specific row or record.

Also, the table shows #delete# in all the fields if I open the table, but if I refresh the table, the same record appears again.

Please advise.
Thank you.
Comment
Watch Question

Author

Commented:
Note:
This is the sql view of the query:

DELETE tblaccounting2.*
FROM tblaccounting2;
Ryan ChongSoftware Team Lead

Commented:
DELETE tblaccounting2.*
FROM tblaccounting2; 

Open in new window


should worked, or to simplify:

DELETE FROM tblaccounting2;

Open in new window


try check your MS SQL and see if there's any triggers in table: tblaccounting2 or other processes that adding the record into table: tblaccounting2 ?
Distinguished Expert 2017

Commented:
In addition to RYan's you do not seem to be checking for a response

blind it is impossible to explain why or what is preventing a specific row from being deleted.

Run
The delete directly on the MySQL.

If there is an error or it should include.  What is in the last row?
You might be getting a blank row, I.e. All rows deleted but access front end presents ........
Ryan ChongSoftware Team Lead

Commented:
ooops, mis-read that, which the backend database is MySQL not MS SQL.
John TsioumprisSoftware & Systems Engineer

Commented:
If the error is on Access BE then its a corruption thing...check if the underlying table is Ms Access and perform C&R
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

Also, the table shows #delete# in all the fields if I open the table, but if I refresh the table, the same record appears again.


That sounds as the table isn't linked properly.

Delete the linked table and recreate the link ensuring that a primary key is selected.

Distinguished Expert 2017

Commented:
Please post the output of
Show create table <tablename>

Author

Commented:
Hi Guys,,\\
I traied all your suggestions, but none of them is working...any other suggestion?
I have verified that the linked table are the same data type..
I have relinked
Open a new Ms-Access engine to see it the original was corrupted....

Nothing worked so far.... can not delete a specific row or record...
The error Ms-Access is displaying is this:
MS-ACCESS can't delete 0 record(s) in the delete query due to key violations and 1 record(s) due to lock violations.

Please assist.

Author

Commented:
NOte:  This DELETE query was working fine before, I don't understand why is not working now..
Distinguished Expert 2017

Commented:
It says there is a lock on the record you are trying  to delete.

Check table tablename
Repair table tablename

Check the table directly on the MySQL.

Double check your interface to make sure it is not locking the row.
If you have references from other table(s) - foreign key - it could prevent deletion of the referenced table.

It is like this:
- DELETE worked on the table 1.
- Someone created table2 referenced table1.
- DELETE does not work for table1 until you remove references to rows of the table1.

It is quite common situation.

if you have:
CREATE TABLE table1
CREATE TABLE table2
CREATE TABLE table3

DELETE TABLE table3
DELETE TABLE table2
DELETE TABLE table1
- reverse order.

You can also just drop FOREIGN KEYS (if any).
Ryan ChongSoftware Team Lead

Commented:
Not too sure for Linked Tables in Access for Referential Integrity setting, but you may have a look on that as well for cascading delete options.

Guide to table relationships
https://support.office.com/en-us/article/guide-to-table-relationships-30446197-4fbe-457b-b992-2f6fb812b58f

you can also google for more details and how to configure it.
Distinguished Expert 2017

Commented:
The backend db is mysql.

Info on DB,tables,and type are needed.

Author

Commented:
Hi,

I finally decided to create the table again. with exactly the same set up and fields (delete the old one).  That solved the issue.

I don't know however, what was wrong with that table.
I hope it does not happen again.
Any suggestion

Thank you.
Ryan ChongSoftware Team Lead
I don't know however, what was wrong with that table.
I hope it does not happen again.

well, we don't have the exact environment to test so we can't actually know what had happened.

but some action could probably happen which trigger and insert the extra record into it.

so, it would be better if you monitor the newly re-created table and see whether your issue is reoccurring.

Author

Commented:
ok... Thank you.
Re-create table.