Avatar of Tony
Tony
 asked on

DELETE QUERY not working.

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.
Microsoft AccessVBAMySQL Server

Avatar of undefined
Last Comment
Tony

8/22/2022 - Mon
Tony

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

DELETE tblaccounting2.*
FROM tblaccounting2;
Ryan Chong

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 ?
arnold

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 ........
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ryan Chong

ooops, mis-read that, which the backend database is MySQL not MS SQL.
John Tsioumpris

If the error is on Access BE then its a corruption thing...check if the underlying table is Ms Access and perform C&R
Gustav Brock

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

Please post the output of
Show create table <tablename>
Tony

ASKER
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.
Tony

ASKER
NOte:  This DELETE query was working fine before, I don't understand why is not working now..
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
arnold

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.
Andrei Fomitchev

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 Chong

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

The backend db is mysql.

Info on DB,tables,and type are needed.
Tony

ASKER
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 Chong

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Tony

ASKER
ok... Thank you.
ASKER CERTIFIED SOLUTION
Tony

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question