Records getting deleted mysteriously..

  • Thread starter Thread starter C Kirby
  • Start date Start date
C

C Kirby

I'm running a DB using MSDE (2000) that is interfaced by 2 different
ades running on PCs with Access 2000 Runtime. One of the ADEs is a
package accounting system that is very solid and stable, the other is
a custom application that I wrote (much less solid and stable). The
custom app only deals with a select few tables in the database, and
the table in question is not one of those.
With alarming regularity(daily), records are getting deleted out of a
particular table. I've set up a couple of dummy records in the table
and put a delete trigger on the table that creates record in a 'log'
table that tells me the user and the time that the records are
deleted.
The deletion (all records in the table) always occurs during business
hours (never over the weekend or at night) and the user responsible
varies among 3 or 4 different users. 2 of those users don't even have
rights to that table, so I'm really confused how those logins could
cause a delete on the table they don't have access to!??!
As far as I can tell, this is only happening to this particular table
( I hope!).
Is there a way that I can get more information on the process or
machine or anything else that is behind the deletion?
 
Yes, you can use the SQL profiler to get a lot more information about what
is causing this erroneous operation. You could also use a log reader, but
they are not free.

But before you open the SQL profiler and begin the battle with it, here a
little more information : the fact that your users don't have rights to the
table doesn't mean that they don't get an access indirectly, for exemple
from a Delete trigger or a Foreign key with delete right located in another
table.

Your ADE can also provide such an access via your code and the deletion may
be the result of a fault in it.

2 suggestions:

First, it may be the result of an open transaction wich make a roll back
of the whole table when it is closed without a commit. Usually, people
don't mess with the automatic gestion of transaction by Access, but in case
that you have tried to make your own transactions, that could easily have
the aforementioned effect.

Second, make sure that all the foreign keys in your table really where
they should and that they got the right values, otherwise the deletion of
only one record in another table might be able to delete all of your record
if the value of this foreign key have been duplicated. From what you say,
the table in question is isolated from the other tables, but maybe you have
duplicated the foreign key of another table without knowing it; for exemple
by reusing the SQL code of another table to create the new table.

Also, make sure that your ADEs have been compiled on all of your PCs
with the same version of Windows, MDAC and VBScript.

S. L.
 
Back
Top