Setting a database password in't a best practice for securing a database. Although you could protect the backend database using a password, this password will be stored in the frontend database and can be read by sophisticated users. If the backend tables
are already linked in the frontend when specifying a password for the backend database, one must delete all linked tables from the frontend and re-link them (specifying the password).
User-level security is a complex topic and I will not explain all of it in detail, here you will find more about it:
Simply "joining" a workgroup using the Access menu commands does not have an effect on the database opened at all. Specifying a workgroup here means telling Access to open that other mdw file (instead of the default system.mdw from your user profile) when
starting Access in the future. Configuring security in the database right now will be quite useless and will not secure your database. To see that I am right, start Access and join the default workgroup again and re-open the backend database. This should not
be possible if your database was secure.
The reason why "other databases prompt for credentials" is that you set a password for the Admin user in the workgroup file that Access uses when starting (your new workgroup). When the Admin user in the workgroup used has a password, the user is prompted
(and if not, the user is automatically logged on as user Admin). To solve this issue, re-join the default workgroup. This should always be done after creating a new workgroup file. To tell Access to use your new workgroup when opening your database, use a
shortcut to Access that contains command line parameters: "DatabasePathAndFile" /wrkgrp "WorkgroupPathAndFile"
Users and groups are stored in the mdw file, permissions are stored in the database.
Here are the essential steps for securing an unsecured database.
Create and join a new workgroup file using the Workgroup Administrator. Close and restart Access. Create users and groups in the new workgroup, configure group membership. Don't forget to add a user account for you that belongs to the Adminis group. Afterwards, remove the Admin user from the Admins group.
Define a password for the Admin user (you are logged on as Admin) Close and restart Acces. Create a new blank database and log on using your username when promted to (no password yet).
Define your password, if you wnat to (and you definitely should). If you want, specify the default permissions for new objects in your database.
Import all objects from the unsecured database to the new database (including custom menues and import/export specifications, if present).
Configure/verify permissions. The Users group and the Admin user should have no persmissions at all, don't forget to select the object "Database" in the permissions dialog.
Create a shortcut that starts Access, opens your database and uses the new workgroup file
Re-join the default system.mdw file to make Access use that one by default again.
In steps 1 and 3, don't forget to write down (or take screenshots of) the workgroup information shown and the username/groupname and PID. This information must be available if case that your workgroup file has to be re-created for whatever reason. (The PID
is only displayd while entering it. After creating the user/group, it can never be displayed again.)
Following these steps for frontend and backend databases will make them secure.