- Microsoft Access
- Windows OS
- VBA
- MySQL Server
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.
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 ........
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.
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.
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.
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.
-
![]()
received a Solution
ms access 2010 execute stored procedures on mysql not working -
![]()
wrote an Article
![]()
Delete Query Vs Tagging in MS Access
-
![]()
created a Video
![]()
Shuffle - 2 - Totals Query
-
Explore Cloud Class® ![]()
Certification: MOS Microsoft Office Specialist - Access 2016 Part 1
Premium Content
You need an Expert Office subscription to comment.Start Free Trial