Securing MS Access

[chippyles]

I need to create my Access database so when the mdb is created the user has no ability to shift - Enter and view the tables and/or queries. I know I can modify the start up tool and create an mde. But even then people are able to view everything. What could I do?

 

[M8KWR]

you can hide the table in the mde, by right clicking on them, going into properties and saying hidden.

The way to get thm back is by going into Tools -> options -> View tab, and there should be a option saying "Hidden Objects"

Hope this helps.

 

[chippyles]

I know that, but I need something more secure.

 

[M8KWR]

how many forms do you have. If you had the one form, then you could write some code to say if the form as lost focus prompt for a password, and if the password is incorrect then close down the access file. But this would probably cause it to ask the user for a password when they exit the access db a normal route.

Why don't you just get rid of the toolbar and menu options.

But people could still get to the tables/queries through excel etc anyway if they knew how to.

Don't you trust you workers....

 

[chippyles]

There has to be a code that I can apply to the db opening or closing that prevents the mde from being shift - Enter.

 

[M8KWR]

what version of access are you using...

Do you have the menu visible, such as the Window one, because they could do it through there just as easy.

 

[chippyles]

I am using 97.

I can do the hidden, but they can unhide them, which then means not secure.

 

[M8KWR]

You are never going to get it totally secure, they is always going to be a way in for people to extract information, or change it without using access..

I find it best to isolate these people who would do this and give them a warning.

You could try the security wizard in the tools -> security.

I have not used it myself...

 

[FancyPrairie]

In this thread thread705-978122 I give you step by step instructions on how to setup security.

 

[JeremyNYC]

Chippyles,

It's true that Access is not fully secure. A real hacker will be able to get into you database REALLY easily. But if you're just worried about people poking around, it makes sense to disable the shift key bypass. Here's code that does that, in a few steps. I may have left something out, but my guess is you'll be able to piece this together:

Don't even THINK about doing this without backing up your database first. It's quite easy to lock yourself out.

Make a form that is only available to someone who is an administrator of your database, and make a table to store a value for whether or not you're in admin mode (or just use an existing table, if you have a constants table already (check out abcdataworks.com, the developers' section, to hear a bit about that).

The first two go on a form that only an admin can get to.
=========
Private Sub btnAdminMode_Click()
Call AdminMode(Not (Me!EnableShiftKeyBypass))
Me.Requery
End Sub
===========
Private Sub Form_Open(Cancel As Integer)

Me!txtAppPath = GetAppFilePathName()
Me!txtDataPath = GetSQLServerFullPathName()
If Me!EnableShiftKeyBypass = False Then
Me!btnAdminMode.Caption = "Enable Shift-Key &Bypass of Startup Options"
Else
Me!btnAdminMode.Caption = "Disable Shift-Key &Bypass of Startup Options"
End If

End Sub
============
Here's the AdminMode sub, and the two functions it needs:
=======================
Public Sub AdminMode(bAdmin As Boolean)
'(c)Copyright 2/6/01 Jeremy Wallace
On Error GoTo Error

Dim bolResponse As Boolean
Dim strMsg As String
Dim cmd As ADODB.Command
Dim strCaption As String

'Set the AllowBypassKey and associated properties to whatever is passed in. The AllowBypass key property determines _
whether or not the shift key disables the startup options. If the bypass key is disabled, an admin will have to _
open the database in non-admin mode, set the property, and close and reopen the database. The field in tblMain is _
used to display the current state on the main admin form.
DoEvents
If bAdmin = False Then
strMsg = "This shift-key bypass of the startup options is currently enabled. Do you want to disable it?"
strCaption = "Enable Shift-Key &Bypass of Startup Options"
Else
strMsg = "This shift-key bypass of the startup options is currently disabled. Do you want to enable it?"
strCaption = "Disable Shift-Key &Bypass of Startup Options"
End If

If vbYes = MsgBox(strMsg, vbYesNo + vbCritical, "WARNING") Then
DoEvents
Call SetMyProperty("AllowBypassKey", bAdmin)
Call SetMyProperty("AllowBreakInCode", bAdmin)
Call SetMyProperty("AllowSpecialKeys", bAdmin)
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "UPDATE tblMain SET EnableShiftKeyBypass = " & CInt(bAdmin) * -1
.Execute
End With
Forms!frmAdminSplash!btnAdminMode.Caption = strCaption
End If

Exit Sub

Error:
Select Case Err.Number
Case 2450 'can't find the form
Resume Next
Case Else
Call MsgBox(Err.Number & ": " & Err.Description, "Error in AdminMode")
End Select
End Sub
==============
Function SetMyProperty(MyPropName As String, MyPropValue As Variant) As Boolean
'Taken from the following cdma posting
'From:T Tran (tktran@earthlink.net)
'Subjectisabling Shift Key in Access ADP - Thanks to J. David Suttin
'Newsgwoups: comp.databases.ms -Access
'Date:2002-06-14 11:54:21 PST
On Error GoTo SetMyProperty_In_Err

Dim Ix As Integer

With CurrentProject.Properties
If fn_PropertyExist(MyPropName) Then 'check if it already exists
For Ix = 0 To .Count - 1
If .Item(Ix).Name = MyPropName Then
.Item(Ix).Value = MyPropValue
End If
Next Ix
Else
.Add MyPropName, MyPropValue
End If
End With
SetMyProperty = True

SetMyProperty_Exit:
Exit Function

SetMyProperty_In_Err:
'change to your own error routine...
Call MsgBox("Error " & Err.Number & ": " & Err.Description, , "Error in SetMyProperty")
SetMyProperty = False
Resume SetMyProperty_Exit

End Function

=====================
Private Function fn_PropertyExist(MyPropName As String) As Boolean
'--------check if a property already in the properties collection --------------------
fn_PropertyExist = False
Dim Ix As Integer

With CurrentProject.Properties
For Ix = 0 To .Count - 1
If .Item(Ix).Name = MyPropName Then
fn_PropertyExist = True
Exit For
End If
Next Ix
End With

End Function

============
MAKE THAT BACKUP!!!!!!!!!!!!!

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[Adz316]

This application saved me loads of time having to code for myself and and so easy to use....download from...

http://www.jamiessoftware.tk/propeditor/pe_download.html

Make sure you back up first. Then use these free Property Editors.

Good luck,
Adz