Delete Query not deleting all records -- Lock Violation - #DELETED#

[heygoena]

I have been searched the web extensively and been unable to find an answer to my problem, so any help would be greatly appreciated.



I have built an Access form which allows a user delete all records based on a selected group ID and date. The Group ID and Date are selected from multiple select list boxes which have the following row source:

SELECT ID_Group, Group_Name FROM tbl_Group_List;

and

SELECT DISTINCT et.report_date FROM qry_existing_reports AS et WHERE (((InStr(get_global("GBL_delete_selected_groups"),[et.ID_Group]))>0));

(The selected listbox items are stored in a global variable and using this WHERE InStr() Syntax I am able to simulate the IN functionality of MySQL)

after the user has chosen the combination of groups and dates they would like to remove the following query is run from an onClick event:

DELETE m.*
FROM tbl_monthly_reports AS m
INNER JOIN tbl_group_info AS g
ON m.loan_ID=g.loan_ID
WHERE (((InStr(get_global("GBL_delete_selected_groups"),g.ID_Trade))>0)) And (((InStr(get_global("GBL_delete_selected_dates"),Cstr(m.group_date)))>0));

In most cases everything works as I designed and the records the user has chosen are removed. However some of the time (maybe 10% of the time), a few records from the group will not delete (less than 5 out of hundreds).

Access displays a message saying that the records were not deleted due to lock violations. To try to correct for this I double checked that Default record locking was set to No Locks/Exclusive/Unchecked record level locking. I also added Timestamp fields and re-linked the tables. When I try to view the tables in Access after performing the query I see #DELETED# in all fields which should have been deleted, but if I run a select query from MySQL I can still see the records. How can I ensure that this query runs every time and these remaining records are deleted as they should be?

Thank you!

[rpeare]

I don't know much about MySQL and I've not used your method to delete records you may have to find a way to commit the transaction, in other words tell MySQL to finalize the delete which would clean up a lot of the data, in some instances if you don't commit the transaction you can experience what you are.

[heygoena]

Thank you for the response, I'm not certain exactly what you mean by commit the transaction. As far as I can tell when I run queries on the ODBC linked tables, the query performs the action it is supposed to. From what I can tell, this issue seems to be related to the #DELETED# rows which are returned if I try to view the delete query. Perhaps there is something not quite right about my join? I am able to view and delete the records directly from MySQL, but this does not fix my problem that the form will only succeed in deleting all of the records it is supposed to most of the time. I think if I can find a way to solve the #DELETED# problem, the lock violation will stop and the rows will be removed. I have been using Access for less than a year, so I'm certainly no expert, but this is the feeling I get... Any further insights would be greatly appreciated.

[rpeare]

as I said I don't know much about MySQL, but when dealing with SQL (not ms access) in code no changes you make to the database are applied until you commit the transaction, I do not know if this is the same for mysql I just thought it might be a starting point for you.