How to fully lock an access database

I have an access database that I need to limit access to only forms (in ordert to add, edit and save data in tables)  However, do not want users to have access to programming code, queries, etc)
(Reason: Have installed database in the past and several employees thinking they know how to program in access have made adjustments and caused errors in database.)  I am aware of the security wizard, but aren't there ways around it?)  I want to lock this completely down if possible.
softsupportAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"I must backup, do a DECOMPILE, and then create a MDE from the MBD."

If ... there are any compile errors, making an MDE will fail, so ... just wanted to be sure you had a 'clean' MDB first.  

MDE do not 'secure' tables and queries.  IF ... that is a requirement, then the best that Access has to offer is  ULS (User Level Security).

Here are some links to get you started on that:

Here are some links ...

step by step instructions on how to do it right
www.jmwild.com   

Excellent resource book which I have
http://www.vb123.com/map/       

Microsoft info:
http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp
http://support.microsoft.com/?id=207793

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can create an MDE from the MDB.  This will 'lock' vba code, and users will not be able to open forms or reports in Design view.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
After you do the Decompile procedure below to be sure everything is 'clean' so to speak, then do this:

Tools>>Database Utilities>> Make MDE ...
follow the steps.
***************** MAKE A BACKUP FIRST OF YOUR MDB AND SAVE IT ***

But ... do this first:

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

6) Close the mdb
7) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an example of the command line syntax  (be SURE to adjust your path and file name accordingly) for executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\AzDoc\Pgrm\AzDocPgrm2K3.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
softsupportAuthor Commented:
To make sure I understand:

I must backup, do a DECOMPILE, and then create a MDE from the MBD.  
Once this is done, no one will be able to access code, queries, etc.  When I need to make changes to database again.... Will I be able to?   Sorry, pretty inept in this security thing.  And what about the User-Level Security in Access... not needed?  No access will be granted in the MDE file?
0
 
TextReportConnect With a Mentor Commented:
With an MDE file the users can still modify the tables and queries, the MDE stops them modifying Forms, Reports and Modules.

If you want to stop them modifying tables and queries either don't use them or secure the application using the Access security. And no it is not 100% secure but hopefully it is more secure than your users capabilities.

Cheers, Andrew
PS. You can also hide the objects it does confuse some.
0
 
softsupportAuthor Commented:
So the Access security will secure all that I set at user level?  And was it the MDE that is not 100% or Access Security also.  Is it possible for the database to be accessed if security is enabled?  I know nothing is 100%, but what is the closest?  How do you hide objects?
I have created the database on a laptop and will install on a server.  Can the security be setup on the laptop and then install database on server?  Laptop is running Access 2003, machines accessing server are running Access 2007.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"So the Access security will secure all that I set at user level?"
With ULS, you can assign permissions down to the individual object level if you like.  Basically, there are Groups and Users.  Reading the links I provided will give you a GREAT overview on the subject ... and show you the correct way to use ULS.

" Is it possible for the database to be accessed if security is enabled? "
Yes  of course, based on the object permissions YOU assign using the Workgroup Administrator tool.
Note ... A2007 / ACCDB's no longer support ULS. However, an A2003 MDB with corresponding WorkGrouop file (MDW) will still work in A2007 environment.

"Can the security be setup on the laptop and then install database on server"
Yes.  When you implement ULS, a secured MDW file (different from the default system.mdw) file is created.  You must include the secured MDW file on what every system you move the MDB to. Then, using the Workgroup Admin tool OR VBA Code or a Command Line start parameter, you open your secured MDB by specifying the MDW file to use.  Access into the MDB is then based on the permissions you have assigned to each Group/User/Object.

Again ... please read the info in the links.

There are several ways you can hide objects.  Right click over any object, select Properties and check the Hidden box. Then, in Tools>>Options>>View tab ...  UnCheck the Show Hidden Objects and System  Object check boxes.  And of course, you can hide the database window also.

That's the basic story.

mx
0
 
sonchoyCommented:
You can also lock down your VBA code. Goto your VBA editor
Tools>your database properties
Click on protection, then check lock project for viewing and create a password.
This will prevent your user or anyone to from changing your VBA code if they don't know password
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"This will prevent your user or anyone to from changing your VBA code if they don't know password"

Of course, create an MDE does all of that and more.

mx
0
 
sonchoyCommented:
Once you create a MDE, you can't go back to your MDB file. You have to have a Back up of your MDB file before you create a MDE. That's I avoid creating MDE.
0
 
TextReportCommented:
When you create an MDE you have an MDB file let's say its called MyDatabase.MDB then you tell it to create an MDE file called MyDatabase.MDE ( I normally create it in a different folder, you don't overwrite the MDB file with the MDE and you continue to develop in the MDB but distribute the newly built MDE.
Cheers, Andrew
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"You have to have a Back up of your MDB file before you create a MDE. That's I avoid creating MDE."

Seriously ... I don't work more the 15-20 minutes without backing up an MDB during development.

So, that is hardly a reason for not creating an MDE.  Also, if you forget the password on the VBA project ... you have the same issue.

mx
0
 
sonchoyCommented:
Well, let the author decide it which is best for him/her
0
 
softsupportAuthor Commented:
Ok.... tried to do the .MDE..... received error message.  Did the COMPILE, received error message in the COMPILE, but the code works.  What to do now?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
"received error message in the COMPILE, "

You *must* fix the compile errors first. You cannot create an MDE wherein compile errors exist in the MDB - Access will just not let you do that, sorry.

mx
0
 
softsupportAuthor Commented:
Here is the code with error message


Error message says "Compile error"  Else without IF.  I put a message in parenthesis the ELSE that is erroring out.
 
 
Public Function CompareMeals(DisAllow As Long, McAtt As Long, McOrd As Long, McCap As Long) As Long
If [DisAllow] > [McAtt] Then
    If [DisAllow] > [McOrd] Then
        If [DisAllow] > [McCap] Then
        CompareMeals = DisAllow
    Else
        CompareMeals = McOrd
            Else   (this is the Else it is erroring out on)
            CompareMeals = McCap
    End If
          End If
Else
    If [McAtt] > [McOrd] Then
        CompareMeals = McAtt
    Else
        CompareMeals = McOrd
    End If
End If
 
Else
    If [McOrd] > [McCap] Then
        CompareMeals = McOrd
    Else
        CompareMeals = McCap
    End If
End If
 
End Function

Open in new window

0
 
TextReportCommented:
You can't do  If, If, Else, Else you have to do an Endif before the 2nd else
Cheers, Andrew
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Here:
Public Function CompareMeals(DisAllow As Long, McAtt As Long, McOrd As Long, McCap As Long) As Long
If [DisAllow] > [McAtt] Then
    If [DisAllow] > [McOrd] Then
        If [DisAllow] > [McCap] Then
        CompareMeals = DisAllow
    Else
        CompareMeals = McOrd
            Else   (this is the Else it is erroring out on)
            CompareMeals = McCap
    End If
          End If
Else
    If [McAtt] > [McOrd] Then
        CompareMeals = McAtt
    Else
        CompareMeals = McOrd
    End If
End If
 
Else

That last Else is out of place.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually, there are more Else's out of place.  

You need to go through and straighten those out.   I'm not sure what logic you intend, so you need to fix those.

If ...

Else

ElseIF

ElseIF

End IF

is the basic structure

mx
0
All Courses

From novice to tech pro — start learning today.