How Do I do this one..code to run macro if event doesnt happen

  • Thread starter Thread starter Zach
  • Start date Start date
Z

Zach

I have been tasked to work on a database that has
sensitive and confidential information inside. The
database will be backed up to CD and the upgrades to the
database will also be made via CD.

The database uses user level security with me as the Admin.
I need some way to say that if I dont log in, or open a
form, or click on a control, say at least once a month or
week, then a macro will run and delete all the tables with
the sensitive data.
This will ensure that the data wont last long outside of
the groups control. No one inside the group will know
about this feature, to ensure security. It needs to be
inside the actual database in case the program is copied.

I was told to link the log into a table and have a query
compare my log in date to a point in the future and if
there is a null entry before that date then the macro
would run.
I have no idea where to start with this or how to get into
the log in date or even the VB code to do it...

Any help is greatly appreciated.

Zach
 
You would have to keep track of when you are logged on in a table and check that table
every time anyone opens the database. It could be in the Open event of your startup form.

Now, for the problem. If someone is trying to hack your database, there is no guarantee
that they will open it in the usual way, thereby bypassing your delete routine. The data
can be gotten to using a variety of methods, especially if you assume that if they have
access to get a copy of the file then they probably have access to get a copy of the
workgroup file as well. Also, deleting the data doesn't remove it from the file until the
file is compacted. There are some recovery places that can recover deleted data.
 
so is there any way to do this? I dont really have to
worry about hackers as much as the information being
copied and used by someone else. If I choose compact on
close, that would take care of the deleting the info,
right? Most of the users have little or no access
training. The information is just of a sensitive nature
that my bosses want to ensure that its secured in any
event, and automatically deleted if necessary. The macro
is already there to dump the system if needed, so they
want a auto dump too.

Any ideas

Zach
 
Yes, it can be done and the compact on close would help.

You will need the following in the OnOpen event of your initial form.

You would need to get the logged on user name and if it is you, update a field in a table
that you have just for this. It will be a table with just the one field and one value, the
last date you logged on.

You would then check this date and if it is older than a certain date you would delete the
data, set the compact on close property, and close the database.

To get the currently logged on user:
strUser = CurrentUser

To delete data:
Set db = CurrentDb
db.Execute "Delete * From TableName;"
'More tables here
Set db= Nothing

Run the execute statement for each table you want to delete data from. If you have
referential integrity set up you will need to delete from the many table before you delete
from the table on the one side of the link or set up cascade deletes (dangerous).

To set the compact on close:
Application.SetOption "Auto Compact", True

To close the database:
Application.Quit
 
Ok I added the strUser to the on open to a form that I
only use. I can read VB, but I dont know enough yet to
write anything like this. Ive created a table called Log
with a field called log in, using Date/Time, and input
mask for short date.
How Do I write the code to put my log in on open to this
table and the code to compare it to a future date that
keeps changing? Thank you for your help
-Zach
-----Original Message-----
Yes, it can be done and the compact on close would help.

You will need the following in the OnOpen event of your initial form.

You would need to get the logged on user name and if it
Click to expand...
is you, update a field in a table
that you have just for this. It will be a table with just
Click to expand...
the one field and one value, the
last date you logged on.

You would then check this date and if it is older than a
Click to expand...
certain date you would delete the
data, set the compact on close property, and close the database.

To get the currently logged on user:
strUser = CurrentUser

To delete data:
Set db = CurrentDb
db.Execute "Delete * From TableName;"
'More tables here
Set db= Nothing

Run the execute statement for each table you want to delete data from. If you have
referential integrity set up you will need to delete from
Click to expand...
the many table before you delete
 
Ok, the following will use DAO, so if you don't have the reference set, you will need to
set it. To do so, open a code window (Alt+F11) and go to Tools|References. There should be
a check next to Microsoft DAO 3.6 Object Library. If you are using Access 97 or older,
this should already be set, but the version will be older than 3.6.

You will need to manually place an initial date in the table.

In the OnOpen event of the form you created:

Dim db As DAO.Database, strUser As String
Dim dteDate As Date
Set db = CurrentDb
strUser = CurrentUser
If strUser = "Admin" Then 'or whatever your login is
db.Execute "UPDATE Log SET Log.[Log In] = Date();", dbFailOnError
End If
dteDate = DLookup("[Log In]", "Log")
If DateDiff("d", dteDate, Date) > 30 Then 'pick your number of days here
db.Execute "DELETE * FROM TableName;"
'Do this for each table you want to delete data from
End If
Set db=Nothing
Application.SetOption "Auto Compact", True
Application.Quit


This is untested, but should be close enough to get you going.
 
Back
Top