Database level user security in MS Jet Engine

I am wanting to add a user access control system to one of my MS Access applications using the DAO system built into MS Jet database engine. I am working with accdb database on Access 2007.

I understand how to manage DAO users & groups.

I have discovered that, to "turn on" security control, I have to change the password on admin from blank to some non-blank value. This forces the user to login when opening the access database. What I have discovered, however, is that this also forces a user login in every Access datab开发者_C百科ase on the system.

My question: Is there someway to turn on the user login for some accdb's and leave it off for all others?

Access uses a workgroup information file to store the user and group account information. The default name is System.mdw

You can find out the name of your current workgroup information file and where it is located by examining the SystemDb property. For example, from within your Access session, switch to the Immediate Window and run this line:

? SystemDB 

However, you can have another workgroup information file and start up Access with a shortcut which includes the /WRKGRP switch followed by the full path to your alternate workgroup information file.

The shortcut pattern includes 4 elements.

"Path2EXE" "Path2Db" /WRKGRP "Path2MDW"

Here is a working shortcut from my Access 2003 system. (It should be all on a single line; I split it here so you won't need to scroll to see it all.)

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "P:\Access\wip\sandie\NewDB.mdb" 
/WRKGRP "P:\Access\wip\sandie\Security.mdw"

So the point is you can have two (or more) workgroup information files: the default without an admin password which you use for your unsecured databases; and another with an admin password which you use for your secured databases and reference with the /WRKGRP switch when you open them.

That leaves the question of how to create your alternate workgroup information file. Use the User-Level Security Wizard and choose the option "Create a new workgroup information file".

Another point that puzzles me is that you said Access 2007 and ACCDB. My understanding was that Access 2007 and 2010 still support user-level security, but only in the older MDB database format, not with the ACCDB format.

One final note: If you need bullet-proof security, ULS is not sufficient. It can be defeated with an Access password cracker. Supposedly they are easy to find, but I've never looked for one myself.