How to Protect a Database with your own Security

How protection for a database.

This FAQ is designed to create your own security/protection system it will not replace access security. BUT I use it instead.
If you read FAQ181-4888. It show that you can have a form that allows user to login to the database. It used the dlook function.

This FAQ it takes the process one step further, to help protect you data.
Step 1.
Disable the F11 key in the tools startup menu.

Step 2.
Diable the shift key to stop users passing the login screen. This FAQ does not describe how this done. There are excellent ones in the Forms FAQÆs.
On startup, load the password form. Buttons on the form are the OK and Cancel buttons. The form has 2 textboxes (login name and password).
The cancel button exits access.
The OK button does the DLookup (see faq181-4888 for the code).
Also add a field called LogedIn in the table you store passwords. When you login set value of LogedIn to 1.
When Loading a form or report use the filter code, or use a macro with the where condition. Then users will only see data for there area. You would have set a list of departments in the table.

Step 3.
Use a timer on a form to close the database on non activity is recommended, the form will close after ( what time you set) of the form being idol.
Have a sub form on your menu form that shows the users that is logged in. You will need to reset the value of LogedIn value to 0 on exiting the database. Remember if use timer function to log idol user out use a macro to reset LogedIn to 0

Step 4.
Create a form called exit that only opens for a few seconds (using timer function).

The code below should be added to the exit form on ôOn Openö [Event Procedure] property:
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Set rst = New ADODB.Recordset
Set cnn = CurrentProject.Connection
Dim sql As String
sql = "Select * from Employees where login= " & "'" & FOSUser & "'"
rst.Open sql, cnn, adOpenKeyset, adLockOptimistic
rst!LogedIn = 0
rst.Update
rst.CLOSE
'End check name

NOTE. The data entry form should be made read only, to edit data on this form, you will need to add an edit button to allow changes. This will stops accidental changes to your data.

The Microsoft web site, has code, if you need to add audit facility to forms. It shows any changes made to a form in a memo field .

Creating your own tool bar and menu barÆs. Use macros to create the menu bar to run commands. This will prevent the normal menu bar being visible. Specify this in the tools startup shortcut menu bar. The form properties of a form allow you to specify both menu and tool bars you use.