Sabotoge? Determining who deleted what...

[rokerij]

We run Office XP with Access as our main db. Recently some strange things have been occurring, including the deletion of some vital information. Is it possible that the changes being inputted into forms, just are not saving due to an Access error? Whole records are missing also. I believe we have some users that may not be capable to make changes they see fit, but feel they should. Is there a way if I set up users and passwords for Access, to determine who it was that deleted that specific - record, form etc.?

S.C. Albertin
Database Administrator/Newbie Tech
United Way

Help me to find my way, so that I may help others find theirs...

 

[tracy1234]

I'm not sure about tracking changes, but how about disallowing deletions and edits on the input form?

-Tracy

 

[rokerij]

That is our next step to stop this from happening, but I am looking for a way to prove that it's someone specific. The strange occurrances of deletions and data changes are to numerous to be a system bug, at least I think. Thanks

S.C. Albertin
Database Administrator/Newbie Tech
United Way

Help me to find my way, so that I may help others find theirs...

 

[Garridon]

Database corruption can also cause it to seem to delete records. I had the experience of entering a record in, and Access not saving. I also had two archive queries (append/delete) that ran with the close of the form, and when the database was getting ready to corrupt, it would not append the record--but it did delete it. On other occasions, it would delete the contents of just one field.

Make lots of backups until you figure out what's going on. If you haven't done it already, go to Options and disable Display Database Window. There was an article in VBA Advisor that mentioned that many designers leave the database window in full view where the users can do dangerous things to it. Granted the users can hit F11 to access the window, but most don't know that. You can also disable the F11 key in code--just search the forums for information on that.


Linda Adams

http://www.hackman-adams.com

http://www.david-hedison.com

 

[jebry]

Hi!

To answer your question, forms have a Form_Delete event where you can capture the information you want when a record is deleted. You can use the Before Update event to capture the information when only specific pieces of information are deleted. If you need ideas about how to capture the information, let me know.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[MakeItSo]

About that tracking issue:
I would recommend you set up a workgroup (with Wrkgadm.exe in ...\Office\103x\ folder)
-Create unique logins for each user
-set an admin password (without that, the login form will not pop up)
-let each user not start the db directly, but via a link
-put sth like this in the link props:
"your full path to MSACCESS.EXE" /wrkgrp "full path to your workgroup.MDW" "path to db.mdb"
-assign each user specific rights
-Create a table called e.g. "History" with fields "user" and "login"
-on the open() event of your start form do this:
DoCmd.RunSQL "INSERT into History (user, login) VALUES ('" & Access.CurrentUser & "', #" & now() &"#)

This way you always know the last user.

You can then also use such an INSERT INTO statement into the delete() event on the entry form. This way you should be able to track record deletions.

Hope this all works,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[rokerij]

What would cause a db to corrupt? Faulty NIC? I will try and implement some of the advice up top - thanks to all and I will probably need more help, so many more thanks in advance!

S.C. Albertin
Database Administrator/Newbie Tech
United Way

Help me to find my way, so that I may help others find theirs...

 

[MichaelRed]

See faq181-261 for an example of tracking changes. This approach requires the implementation of Access Security, as well as restricting user access to forms for all data operations (add, edit and delete) of records as well as no access to design of objects (forms, reports, etc)

The advice re implementation of Ms. Access Security is NOT the recommended approach. I would strongly suggest that you obtain a third party tutorial which includes an extensive discussion of Ms. Access Security, read it, study it, implement it per the tutorial on several or more sample databases, make several backups of your production db, and only after having a good understanding of the approach, concepts and details of implementation, apply it to your production db.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[psemianonymous]

Also, I don't think that anyone has mentioned this yet:
DAILY BACKUPS
Keep the daily backups forever. The best thing about this is that you can get this started RIGHT NOW, by setting up a backup schedule with your IT department, if they'll agree to do it. They should, since you (apparently) have vital data on the database.

If you have to do the backups yourself, make a batch file like the following (all you have to do is change filenames and directories):

rem BEGIN BATCH FILE HERE

@echo off
rem CREATES A NEW DIRECTORY WITH THE YEAR-MONTH-DAY-HOUR-MIN
rem SO I COULD DO A BACKUP EVERY MINUTE IF I WANTED TO.
rem  .
rem CHANGE THE DIRECTORIES INVOLVED IN THE FILE COPYING.
for /f "tokens=1-4 delims=/ " %%a in ('date /t') do (set weekday=%%a& set month=%%b& set day=%%c& set year=%%d)
for /f "tokens=1-2 delims=/: " %%a in ('time /t') do (set hour=%%a& set minute=%%b)

c:
cd\atemp\trn
mkdir G:\Access\trn\backup-%year%-%month%-%day%-%hour%-%minute%-development%1
xcopy *.* G:\Access\trn\backup-%year%-%month%-%day%-%hour%-%minute%-development%1\*.* /s

rem END BATCH FILE HERE

About corruption: there is a great resource for this. Check the Corrupt MDBs FAQ I have a link to below.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MichaelRed]

if you rely on the IT (netCops) backing up ANY material, you should CAREFULLY check their exact process / procedures. Most Win based backup routines will not copy (i.e. backup) any file which is "open" in any manner, so if anyone leaves their system running w/ your db open, -no copy!. Further, many IT depts do not regularly (e.g. daily) review the backup logs to see which (if any) backups failed, so you will not even be notified that the backup did not occur. Other 'anamolies' occur in hte back up on an all to frequent basis for me to (any longer) trust the IT group with a simple request to do MY backups. After all, WHEN you find out that the most recent back up of your (vital) data was three weeks ago, it will be a bit late to chek their procedures, and while they will will catch some flak over it, guess who's credability (and performance review) will suffer.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places