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.
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
Last Comment
Tony
DELETE tblaccounting2.*
FROM tblaccounting2;
should worked, or to simplify:
DELETE FROM tblaccounting2;
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 ?
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 ........
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
ooops, mis-read that, which the backend database is MySQL not MS SQL.
If the error is on Access BE then its a corruption thing...check if the underlying table is Ms Access and perform C&R
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.
Please post the output of
Show create table <tablename>
Show create table <tablename>
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.
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.
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
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.
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).
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).
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.
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.
The backend db is mysql.
Info on DB,tables,and type are needed.
Info on DB,tables,and type are needed.
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.
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.
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
ASKER
ok... Thank you.
ASKER CERTIFIED SOLUTION
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
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
This is the sql view of the query:
DELETE tblaccounting2.*
FROM tblaccounting2;