"Danny J. Lesandrini" <dl*********@hotmail.com> wrote in message
news:br************@ID-82595.news.uni-berlin.de...
PC:
As a rule, nothing can be deleted from the client interface, except order
line items, and that happens rarely. (In fact, delete actions are logged
to a table and we can count them.) Invalid orders are cancelled, not deleted.
It is still very possible that the developer's code is creating, and
deleting records. You don't have to use a temp table, but can still do a
lot of deleting.
Further, it is MOST CERTAINLY a know and well document issue that failure to
close recordsets can cause bloat. In addition, if using JET 4.0, the service
updates to JET fixes several well known bloating problems also. So, no, you
can't write out the fact that the code may be un-necessarily writing out,
and copying records. Further, as mentioned, the issue of NOT closing
recordsets is also well documents. And, also, it well documents that several
bloat issues are fixed by the updates to JET.
There is no doubt that as a developer, we MUST TAKE VERY large efforts to
avoid un-necessary deleting and creating of records in a table. However,
since for the last 20 years the major database systems (dbase/Foxpro, and
now JET) did not recycle deleted record space, then any developer would make
HUGE EFFORTS to avoid unnecessary deleting, and creating of records.
For example, I wrote my own custom record locking routines for a ms-access
application. Normally, you have a table called tblLocks. You can simply add
a new record. All I did was make a keyId field (that is the
tablename*ReocrdId as the key). Since the field is set to not allow
duplicates, then I two users can't add the same record with the same
table/key name. This is gives me a nice locking routine (else a error will
occur).
Of course, the problem with the above design is that this design requires
one to delete records, and add records. Each time I lock a record, I would
have to "add" a new record to this table. And, then when I un-lock, I would
have to delete the record. Again, as developer I should be REALLY be scolded
here. Bad Bad Albert!.
So, what did I do?
Well, very simply I created a table with the above fields, but instead of
deleting the records when done, I simply update that table/id field to
blank. In other words, I don't EVER delete from that locking table. When
need to make a lock, I FIRST SEARCH for the current user name, and
workstation name, AND THE LOCKID = null. That way, if any locking record
exists, but not used, then I grab it, and use that record. If the user have
several records locked, then yes...I might wind up adding a new record. So,
my table looks like:
"Lockitem" "NetWorkLogon" "ComputerName"
"tour*423" "Groups" "TAMMYPC"
"tour*393" "Groups" "TAMMYPC"
"BOOK*5037" "reception" "RECEPTION"
"tour*452" "Groups" "TAMMYPC"
"" "Groups" "TAMMYPC"
"" "Staff" "RONNIE"
"" "Staff" "RONNIE"
"" "Groups" "TAMMYPC"
So, what happens if TammpyPc needs to lock a reocrd, I FIRST do a query for
a reocrd with her workstation name, and her computer name. If find a record
with LockItem = null, then I USE THAT RECORD first. Of course the above
design allow a user to have more then one record locked, and from different
tables.
In other words, by JUST slightly modify my design, then above now DOES NOT
CREATE ANY database bloat. You can imagine with 5, or 6 users editing, and
working on 100's of records a day, the above table would cause a
considerable amount of bloat as my code locks, and un-locks records.
However, since I do NOT DELETE the record when done to un-lock, then I don't
have to try and recover the space!. I simply set the lockitem field to null.
Next time I need a locking record, I query the above table. Since I re-cycle
the records, then virtually no bloat occurs with this design.
I mean, we all know that deleting, and re-creating records means that space
will NOT be recovered. Since we all know this, then modifying our designs to
take this into account is just common sense.
So, I am not really convinced that un-necessary records are being deleting,a
and recreated in this application. If this is not occurring, then the
failure to close the reocrdets, or not having applied the JET service packs
is to blame.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com http://www.attcanada.net/~kallal.msn 
