Microsoft Access Undelete or Lack Thereof
The first thing that happened was an error. I did not have Delete privilege granted to me on this database. I quickly corrected that, noting that the error messages appeared before it asked me for confirmation that I really meant to do this operation.
So, again selecting and electing to delete these rows, as it prompted me with it's readback of what I am about to do to my database that I cannot undo, and do I really want to do it? message, I elected to say "No" just for grins. My rows came back— sort of. They had the #Deleted text in every column, and I had this feeling that they hadn't really come back. Good thing I really meant to delete them!
This got me to thinking. Obviously Access had actually gone ahead and deleted them before asking if I really wanted to delete them. It was expecting to bring these rows back via some mechanism evidently. I checked the table definition and it was a MyISAM table, not a suprise since I had set it that way on purpose. So I did the following:
CREATE TABLE table2 LIKE table;and linked this table into Access via ODBC just like the first table. I then did the 'delete a couple rows, change my mind' thing again.
ALTER TABLE table2 ENGINE InnoDB;
INSERT INTO table2 SELECT * FROM table;
So my conclusion is that the Access front end is counting on transactions being supported in order to roll back the operation if the user elects not to perform it.
My real concern here is the "You are about to delete 1 record(s)" message, the key words being "about to"— it has already done so! Especially since it has blithely begun a transaction and deleted your rows, confident in it's ability to roll this transaction back, and completly unaware that the underlying storage engine does not support transactions and has already irrevocably deleted them.
This gives rise to a whole slew of further questions. First of all I don't exactly like Access's style of first deleting the records and then telling me I am about delete them and do I really want to, etc. I really feel it should ask me first.
That said, obviously it sends some command to initiate a transaction. At some point deep in the bowels of MySQL this command performs a null action since the underlying storage engine does not support transactions. However, it returns as if it did. The question is, since I am using an engine that is not transaction-safe, should this command return an error? Should there be an option to enable or disabling returning an error? What were the design factors leading to the current way it is implemented?