Access Security -- Total confusion

[bostonfrog]

I tried following the steps for creating a secure database in Access using a 12-step instruction sheet from a link on Access Security Methods. I backed up the "system.mdw" as "system-bak.mdw" in another folder.
I created a new .mdw and named it secure.mdw using Workgroup Administrator. I created a test database (test_db.mdb)and created a my own user (Michel) and assigned it a password.
Then I created a shortcut on desktop to Access and the file using the /wrkgrp "c:\windows\system32\secure.mdw" "path\name of database" parameters within the shortcut.
So that other users can use Access on this computer, I RE-joined the "system.mdw" using the workgroup administrator. However, every new instance of Access is requiring my user name and password now, even though I had re-joined system.mdw. What's wrong? The properties of system.mdw do not indicate that it was modified since installation. Do I need to copy over the backup I made of system.mdw back to the system32 folder? Because I had similar problems to this in the summer, I gave up using Access security -- very frustrated.

 

[psemianonymous]

OK, I think I know what you did wrong.

First, let me show you a little tool that will help you know what workgroup file you're working with:

Press CTRL-G to get to the Immediate Window, then type (or paste) in,
?syscmd(acSysCmdGetWorkgroupFile)

That will tell you the workgroup file you're using.


Now. If you have used the Workgroup Administrator to re-join the system.mdw (default) workgroup, then you shouldn't be prompted for a password if you are just opening Access. Unless, of course, you've been modifying the default MDW file all along. Which apparently, you have?


The foolproof way to do this is to open Access with your special workgroup file via a shortcut every time. Then there's no confusion as to whether you have joined a specific workgroup or not--leave it on the default.



For now, your quick solution is to (after making yet another backup,) copy over the system.mdw file with your system-bak.mdw backup and see what happens. You'll probably have to start over, as all you security information is stored in the default system.mdw file.


Good luck.
PS--here's the official MS FAQ:
Microsoft Access Security FAQ:

http://support.microsoft.com/support/kb/articles/Q165/0/09.asp

http://download.microsoft.com/download/access97/faq1/1/WIN98/EN-US/Secfaq.exe

(This is the Security FAQ for *ALL* version of Access)

http://support.microsoft.com/support/access/content/secfaq.asp

(On-Line version of the Security FAQ)

 

[bostonfrog]

Thanks foolio. Right now I am at the other office on a different computer -- my Access files or new ones open fine without bringing up a user name/password combo on this computer. I will try your advice. Yesterday, I did copy (not move) my backed-up system-bak.mdw and, using the Workgroup Administrator, made it my default workgroup file; Access is still opening up the dialog box requesting the user name and password -- even when I am opening any Access file or even if I try to create a new one.

However, I may have made 1 fatal error, but I am not sure:
I opened the secured Test.mdb with the shortcut and /wrkgrp parameters BEFORE rejoining the system.mdw. As I said, Windows 2000 doesn't show any modification date to system.mdw at all since it was installed in 2002. I am using Access 2000. Any thoughts much appreciated. I do remember my user name and password, and wonder whether I could change the security back to the default user (Admin) that is established by default and requires no password?

 

[psemianonymous]

1. If you want to eliminate the security prompt, just change the admin user's password to blank (nothing). This will eliminate the symptoms, if not the problem...

2. If you open Access with the /wrkgrp parameter, it overrides the default "joined" workgroup file. That's the point of setting up a special shortcut that uses the /wrkgrp parameter - you can have multiple databases, pointing to different workgroup files, without disrupting Access' "normal" behavior.

3. You weren't totally clear on this point, so just to clarify--switching the "joined" workgroup while Access is open does not affect already-opened application windows.

4. Using ?syscmd(acSysCmdGetWorkgroupFile) will eliminate confusion as to what workgroup file your Access application is using. It will tell you.



So let me tell you how *I* edit workgroup information:

1. Use the workgroup administrator to create the new workgroup. Let's call it "C:\new.MDW" for this example.

2. Close workgroup administrator, never to return. We don't need it anymore.

3. Create a shortcut that has a target of:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /wrkgrp "c:\new.MDW"

(NOTE: the location of MSACCESS.EXE may vary on your computer)

4. Name this shortcut "Open Access With New.MDW". Put this on the Desktop.

5. Double-click on your shortcut. You will not (yet) be prompted for a password.

6. You'll have to create a new database (db1.mdb) to be able to get into Access. Do so.

7. Tools->Security->User And Group Accounts.

8. Change Logon Password tab

9. Set the password for the Admin user.



Any time you need to enter the special workgroup, use the shortcut. Any other time, just use the default MSACCESS shortcut, i.e. "Microsoft Access" on the Start Menu.

 

[bostonfrog]

Thanks for the detailed instructions, foolio12! When I go back to that computer tomorrow morning, I will try what you said.
Of course, after having re-joined the default system.mdw yesterday, I didn't attempt to open the file on my desktop with the /wrkgrp parameter shortcut. I understand that it would use those /wrkgrp parameters regardless of whether or not I re-joined system.mdw. I just attempted opening any previous database files from a folder or just create any new one (like db1.mdb). When I get into to Tools |Security, do I simply leave the Logon password field blank for Admin, as I want there to be no password? Before I get to Step 7 mentioned above, I will be asked for a username/password to edit the new db (db1.mdb).
-- Michel

 

[psemianonymous]

When you're at the change password deal, type in the old password, and just don't type in a new password.


Again though, your default system.mdw file should have no admin password on it. Maybe you can find a coworker and copy *their* system.mdw file to your computer and see if that will reset the admin password.


So what I'm saying, is use the new process (steps 1-9) once you have a fresh, working copy of the default system.mdw file. And, of course, steps1-9 only get you started, the actual security implementation is more steps...