MS Access Security - I can't get it to work!!

[HMJ]

I have tried twice to implement MS Access Security using the wizard, and both times it blew up in my face. What I have is a database that needs the following restrictions applied:

Admin group:
Permissions: Read and Write
Opens into a specific switchboard form (form SB#2)
Access to multiple forms and reports. This is done on the switchboard.
Each user has individual ID and password


Management group:
Permissions: Read Only
Opens into a specific switchboard form (form SB#3)
Restricted to one form only, but multiple reports. This is done on the switchboard.
Each user has individual ID and password

Training group:
Permissions: Read and Write
Opens into a specific switchboard form (form SB#4)
Opens into one form only, but multiple reports. This is done on the switchboard.
Each user has individual ID and password


How can I do this? I can seem to get MS Access security to work. If I could have a VBA type of security logon that then directs individuals to the appropriate area, that would work too.

Any ideas? THANKS
Harry Jessen
HMJessen@Yahoo.com

 

[AccessAce]

I grabbed this code from another thread here, then modified it a bit. Parts of it might work for your problem.

Private Declare Function api_GetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Private Declare Function api_GetComputerName Lib "Kernel32" Alias _
      "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function CNames() As String
'UserorComputer; 1=User, anything else = computer
Dim NBuffer As String
Dim Buffsize As Long
Dim wOK As Long
Dim Uname, Cname As String
Dim LAccess As Date

    Buffsize = 256
    NBuffer = Space$(Buffsize)
       
    'Gte the Username
    wOK = api_GetUserName(NBuffer, Buffsize)
     Uname = Trim$(NBuffer)
    
    'Reset all variables
    Buffsize = 256
    NBuffer = Space$(Buffsize)
    
    'Get the Computer Name
    wOK = api_GetComputerName(NBuffer, Buffsize)
    Cname = Trim$(NBuffer)
    LAccess = Now()
    
    CNames = Uname & "   " & Cname & "   Last Accessed at: " & Format(LAccess, "MM/DD/YYYY HH:NN")
End Function

Using portions of this code on the OnOpen event of the switchboard, you can determine which group the username or computer name belongs to. Then run this code:

    Me.Filter = "[ItemNumber] = 0 AND [Argument] = '" & SB#X & ' "
    Me.FilterOn = True

where X is the switchboard number you want.

 

[phcar]

Bypass the wizard and do the securities manually. The Access Bible has a good guide to this procedure. The techniques are too lengthy to describe here.

 

[HMJ]

TO: AccessAce
I am relative new to VBA and my experience level with Access has grown greatly since I start this 'small' project. I don't understand all of the code you wrote. Maybe more detail would help?


TO: phcar
The Access Bible? Where/What is this?

Harry Jessen

 

[JeremyNYC]

Harry,

That code won't you any good.

Get the Access Security FAQ from MS and read it three times. Really. Three times. It's dense stuff. Then just follow the instructions there.

I've got a copy of it on my website.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[FancyPrairie]

This is how I do it and it works for me. I use the Security Wizard to secure my database. Once secured, I go into security and create my groups and users. I assign rights to the groups I created (I assign no rights to the individual users). Then I assign users to the appropriate groups.

Here's the steps involved in creating a workgroup file for the front-end database. You will need to follow the same steps for the back-end database.


1. Backup your System.mdw file in case you mess up (this is the one that ships with Microsoft). Also backup your database.
2. Open your database
3. Add a new user (i.e. MyAdminsUser) Make sure this new user is a member of the group "Admins" (This user represents the Administrator of the project and, as such, will have all rights to the database)
4. Assign a password to user Admin (User Admin is the default user for unsecured databases)
(By assigning a password for Admin, you will be forced to login to Access everytime you open a database. Don't worry about, will take care of that later.)
5. Exit Access
6. Restart you database (Access will ask you to login. Login as the new user (no password)
7. Start the Security Wizard
8. In the first screen check the box entitled "Create a new workgroup information file"
9. Select Next
10. In the second screen, select Browse (located to the right of the file name field). At this point you will name your workgroup file (I would suggest putting your new workgroup file in a location that everyone will access (i.e. a network server))
11. Select the check box entitled "I want to create a shortcut to open my secured database"
12. Select Next
13. On the third screen, simply select Next (You'll want to secure all existing database objects)
14. On the fourth screen, simply select next (Don't select any groups to be included in your workgroup information file)
15. On the fifth screen, select the check box entitled "No, the Users group should not have any permissions"
16. Select Next
17. The sixth screen asks you to list the names of users you want added to this workgroup. The only one you want included is the new user you created earlier. Add the new user to the list. By default, your network name is shown, remove it. The new user should be the only one in the list.
18. Select Next
19. On the seventh screen, select the check box entitled "Select a user and assign the user to groups". The field entitled "Group or User Name" should contain the name of the new user you created earlier. And the Group Admins must be checked.
20. Select Next
21. Select Finish

You now have a secured workgroup. Now, login in using the new user's name and create your groups and users and assign rights to them.
You'll need to do the above steps for your back-end database also.

When you are done, you will want to remove the password for user Admin in the System.mdw file. So, launch access and open an unsecured database, it will ask you to enter your name and password. Enter "Admin" and the password you assigned to Admin in step 4 above. Once you are into the database, remove the password for user Admin.

 

[tlbroadbent]

The FAQ areas of Tek-Tip's forums contain much good info. For example, here are a few FAQs about Access security.

faq181-1131, faq181-83, faq181-1164 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[RickSpr]

I'm sorry you've had trouble using the wizard--and we've all been there--but I, too have to urge you to try again. If you build your own security system in code, you'll almost certainly leave gaps though which anybody could enter. For example, there's no way you could protect against your database being read by code in another database.

You don't say what problem you had with the wizard, but the most common one is that, after you've done all the work, you discover that your database can be opened via the default workgroup (system.mdw) without even a logon prompt. This is caused by an error in creating your custom workgroup file. You must create it using the Workgroup Administrator, and enter a unique Workgroup ID. You can not start with a copy of system.mdw and make changes to it.

If you're willing to try the wizard again, let us know how it "blew up in your face" the last two times, and we may be able to tell you what you're doing wrong. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[FireViking]

Follow jeremy's advice. I had a similar problem and found that you need to create the worgroup first before running the security wizard. Read the FAQ in jeremy thread above.

 

[phcar]

Microsoft Access 97 Bible, Gold Edition
IDG Books

http://www.idgbooks.com