Giving View Only Access to Others

[Joe4]

I have designed a database in Access 2002. I understand that Access is not really designed to be a database to allow multiple users the ability to update it simultaneously.

My questions is this: Is it possible to set-up security to give a few users (say 2-12) view only access while only giving 1-2 people the ability to update the database? If so, what is the best way in going about this? Is doing something like this OK, or could it lead to corrupted databases?

I'd love to hear people's recommendations and opinions on this matter.

Thanks!

 

[Corticus]

Hi jmiskey,

There are several ways you can test for user-access, one would be with a password as follows:

Option Compare Database
Option Explicit

Private Sub Form_Load()

Dim pw As String
pw = InputBox("Please enter password...")

If pw = "password" Then
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
Else
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletions = False
    MsgBox ("Invalid Password, please remain seated and calm " & Chr(10) _
    & "as the authorities are on their way!")
End If

End Sub

Or you could test their userID with the following:
this goes in a standard module

Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function UserId()
Dim lpBuff As String * 1024
GetUserName lpBuff, Len(lpBuff)
UserId = Left$(lpBuff, (InStr(1, lpBuff, vbNullChar)) - 1)
End Function

This is for the form(s)

Option Compare Database
Option Explicit

Private Sub Form_Load()

Dim pw As String
pw = UserId()

If pw = "username" Then
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
Else
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletions = False
    MsgBox ("Invalid Password, please remain seated and calm " & Chr(10) _
    & "as the authorities are on their way!")
End If

End Sub

If you need a little more help, check this example out:
http://www.theillumni.com/posts/password.zip

I created 2 forms, one utilizing each method.

HTH,

 

[Joe4]

Corticus,

Thanks for the reply. I will be sure to check them out.

I took an Access class last year, and they discussed built-in Security Wizards. Have you ever done anything with that, or heard anything about it?

 

[Corticus]

Dang, I forgot about all that,

Yeah, select Tools|Security|User and Group Permisions,

Then you can set what kind of access each user has to what objects. Seems pretty straight forward. I like trying to figure out stuff so much I forget about the wizards sometimes!

Thanks for pointing that out!

edit: Access does not seem to identify the user correctly with their wizard, it calls me 'admin', which is my user level, but not my user name. I think you want to be able to specify user access by their name, which my method will allow.

-Corticus

 

[Joe4]

I recall the instructor saying that in Access 2000, there are some security "loop holes" if you don't use the wizards. I am not overly concerned about that. I just want to give most people view only access and keep the database from crashing or becoming corrupt!

 

[Corticus]

The wizard identifies you if your user has been correctly set up under Tools|Options|General etc. My method will identify user's by the network logon/id, which should be mor useful

Regarding loopholes, you're data will never really be safe in Access, if that is a concern. One only need link tables from another db and they will have complete access to your info (more or less). You can go to Tools|Startup, and uncheck pretty much everything there to limit some access, then save as an mda to keep them out of your VBA. You can also lock your tables using group permissions, but you'll have to muddle through setting that all up. Even locked as tight as you can get it, there is still plenty of software out there that will let you in easy.

Here's some more on that theme:
http://www.vb123.com/toolshed/02_access/mdbSecuritywizard.htm

HTH,

 

[Joe4]

Thanks again for all your help!