Hi, Nick.
I suggest the establishment of a MS Access recycle bin, as Access does not
free the memory until the database is compacted I also suggest renaming of
the 'Compact Database' function to 'Empty Recycle Bin'.
I don't want you to get your hopes up, because this isn't going to happen
without a significant cost to customers. Relational Databases don't work as
file receptacles like file systems do, so the database engine can't save
deleted objects and records intact, just in case the user may want to
restore them later.
A file is a separate, self-contained entity, so the operating system can
leave a deleted file intact on the hard disk at a protected address for
later retrieval. A deleted object in a database depends upon other database
objects and other metadata for its structure, properties, methods, and
relationships. The database engine can't keep tabs on these peripheral
items for deleted objects in addition to these peripheral items for the
current database objects without maintaining a potentially huge overhead and
storage requirements, which means a restructuring of the Jet data file
structure and a complete revamping of how Jet works (Jet currently partially
overwrites items marked for deletion while doing its housekeeping). Not to
mention the slowdown in performance as Jet does the extra work for
maintaining the extra metadata for deleted objects, and as Jet traverses
larger file sizes when holding the "recyclable" objects and to handle this
extra metadata for the recyclable objects.
In addition, a deleted record depends upon the table's structure, indexes,
and related records in related tables. If this deleted record were "kept in
a recycle bin," what would happen to it if it were later restored after the
table structure, indexes, constraints, or related records were changed?
You'd have an orphan square peg trying to fit into a round hole, so the
database engine would have to roll back all of the changes to the table
structures, indexes, constraints, and related records in order to get this
restored record to fit into the table again. There are no transaction logs
in Access, so Jet would have to be redesigned to create and use transaction
logs.
The expense for developing database engine software that has a "recycle bin"
capability of a file system would have to be borne by the customers in the
price they pay for the product. You might be willing to pay a few extra
hundreds of dollars for Access, but are all the other customers willing to
do so, too?
I feel that this
would potentially save an Access developer much time and annoyance when
the
either delete the wrong thing or not realise that something was dependant
on
the item that they just deleted.
Mistakes happen, so database professionals use transactions for database
operations that might not result in the intended consequences. That way,
the transaction can be rolled back to the original state instead of
committed. If an Access developer isn't experienced enough to use
transactions, then renaming the object (such as from tblMyTable to
*tblMyTable or ~tblMyTable) will keep the object in the database file, but
not mark it for deletion (where Jet will partially overwrite the object's
data while doing its housekeeping). And deleted objects and records can be
restored from backup when required, although this step may take a few
minutes.
This item of functionality (the recycle
bin) could also feature in the next release of SQL Server, however as
there
is no 'Compact Database' function is SQL Server I think that it would be
more
difficult to create.
Don't worry about SQL Server. It has its own methods of "shrinking" the
data file to get rid of deleted data, either automatically or by a DBA's
manipulations. A recycle bin within the data file would severely hinder the
performance of SQL Server and make it more complex than it needs to be.
And don't worry about a "recycle bin" saving a SQL Server developer much
time and annoyance when he deletes something that he shouldn't have. Any
"database guy" who deletes data that he shouldn't have in a client/server
database and can't restore it in a heartbeat has no business working as a
database professional, so will either be fired or handed a broom or other
equipment for another job he's more capable of.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Nick 'The Database Guy' McMillen" <Nick 'The Database Guy'
(e-mail address removed)> wrote in message
