Hi Jared,
Access ships with system.mdw and security is already implemented whenever
you use Access. For all databases, it uses this system.mdw file and
silently logs you in as the 'Admin' user which has full privileges.
When you decide that you want to secure a database and have uesrs log in,
you create a new workgroup file (mdw); the wizard did this for you.
What it also does is it makes this new workgroup file the new default mdw to
use for all sessions. That is why you are getting a login prompt for all
databases. The login is tied to the Access session, not to a specific
database. There can only be one default at a time.
Open Access (but not any database). Use Tools, Security, Workgroup
Administrator, and choose join to rejoin the system.mdw that ships with
Access. It has no groups/users/passwords in it and you won't get a login
prompt using it.
For your secure database, you'll need to rejoin your secure mdw. You can
use Workgroup Administrator.exe to switch between different mdw
files(tedious) or use a desktop shortcut just for your secure mdb. The
target in the shortcut can use the /wrkgrp switch to tell Access to use a
different mdw file for this session. Leave your default mdw as system.mdw.
The target would look like
"path to msaccess.exe\msaccess.exe" "path to your db\db1.mdb" /wrkgrp "path
to your mdw\secure.mdw"
Actually it's likely that the wizard created a desktop shortcut for you,
unless you said no. You can give your users this same shortcut.
The security FAQ is available for download at
http://support.microsoft.com/?id=207793