How do I hide the SSN from unauthorized viewers? (Access 97 SR2)

Topic

New Reply

Is it possible to do something like:

If CurrentDB.username = “Admin” Then
Reports!RptEmployees!SSN.visible = 0
End If

How do I allow authorized viewers to see the SSN in a form or report, but hide it from others? Do I have to use a separate button that opens a different form/report for authorized viewers, or can it be done within the same form/report? Thanks for you help.

Reply | Quote

Replies

Assuming you are using User-Level security, you can use something like this on Form_Load event:

    If IsGroupMember(CurrentUser, "Admins") Then
        Me.SSN.Visible = True
    Else
        Me.SSN.Visible = False
    End If

The IsGroupMember function tests to see if a user (normally CurrentUser) is a member of a specified group:

Function IsGroupMember(strUserName As String, strGroupName As String) As Boolean
On Error GoTo Err_Handler

   'Determine if current user is member of specified group account:
    Dim ws As DAO.Workspace
    Dim usr As DAO.User
    Dim grp As DAO.Group
    Dim strErrMsg As String
    Dim i As Integer
    
    Set ws = DBEngine.Workspaces(0)
    Set grp = ws.Groups(strGroupName)
    Set usr = ws.Users(strUserName)
    
    For i = 0 To usr.Groups.Count - 1
         If usr.Groups(i).Name = strGroupName Then
             IsGroupMember = True
             Exit For
         Else
             IsGroupMember = False
         End If
    Next i

Exit_Function:
    Set ws = Nothing
    Set grp = Nothing
    Set usr = Nothing
    Exit Function

Err_Handler:
        strErrMsg = "Error No " & Err.Number & ": " & Err.Description
        Beep
        MsgBox strErrMsg, vbExclamation, "IS GROUP MEMBER FUNCTION ERROR"
    Resume Exit_Function
   
End Function

This function should be saved in a standard module so it can be used for any form/report where needed. This will work with report On Open event too. The SSN textbox visible property should be set to false in design mode. Note: Ensure that the group accounts that are restricted from viewing SSN’s do not have Read permission on the table(s) with SSN data, or these users may be able to open table directly & read data. The forms/reports should be based on RWOP (Run With Owner’s Permission) queries only.
HTH.

Reply | Quote

Thanks, Mark. That did the trick.

Reply | Quote

Mark – From reading your post last year it seems you have a deep understanding of user-level security and DAO. I’m trying to use DAO to access a Jet database from Word and I can’t figure out how to open the database and pass the username and password. Can you help me?

Reply | Quote

When working with Word & Access, for me it’s almost always exporting data FROM Access TO Word (via Automation); so I’m no expert when it comes to using VBA in Word. However, this quick test sub in a Word document using DAO objects worked OK. Example:

Option Explicit

Public Sub TestOpenSecureDB()
On Error GoTo Err_Handler

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strMsg As String

DBEngine.SystemDB = “C:AccessMySecuredApp.mdw”
Set ws = DBEngine.CreateWorkspace(“SecuredWS”, “USERNAME”, “PASSWORD”, dbUseJet)
Set db = ws.OpenDatabase(“C:AccessMySecuredApp.mdb”)
strSQL = “SELECT * FROM [TABLE1];”
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
MsgBox rst.RecordCount & ” records.”, vbInformation, “RECORDCOUNT”

Exit_Sub:
If Not rst Is Nothing Then rst.Close
If Not db Is Nothing Then db.Close
If Not ws Is Nothing Then ws.Close
Set ws = Nothing
Set db = Nothing
Set rst = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub

End Sub

The main issue is being sure to specify the workgroup information file (DBEngine SystemDB property) before specifying UserName & Password when using DAO CreateWorkspace method. In above example replace “MySecuredApp.mdw” with name of workgroup file used to secure the db, replace “MySecuredApp.mdb” with name of secured db, and of course replace “USERNAME” and “PASSWORD” parameters with valid UserName and password. I don’t have example of ADO equivalent offhand, but same principle applies, the connection string would have to specify the SystemDatabase (.mdw file) along with UserName, Password, and other parameters to be able to successfully connect to secured DB. (Note: tested in A2K, but DAO methods should work OK if using A97.)

HTH

Reply | Quote

For the record, example of sub that opens secured Access DB from Word, using ADO vice DAO:

Public Sub TestOpenSecureDB_ADO()
On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strCnn As String
Dim strSQL As String
Dim strMsg As String

Set cnn = New ADODB.Connection
strCnn = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“User ID=MyUserName;” & _
“Password=MyPassword;” & _
“Data Source=C:ACCESSMySecuredApp.mdb;” & _
“Jet OLEDB:System database=C:ACCESSMySecuredApp.mdw;”

cnn.Open strCnn
Set rst = New ADODB.Recordset
strSQL = “SELECT * FROM [TABLE1];”
rst.Open strSQL, cnn, , adLockOptimistic
MsgBox rst.RecordCount, vbInformation, “RECORD COUNT”

Exit_Sub:
If Not rst Is Nothing Then rst.Close
If Not cnn Is Nothing Then cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “ERROR MESSAGE”
Resume Exit_Sub
End Sub

As with previous example, obviously replace “MyApp”, “MyUserName”, “MyPassword”, etc with actual parameter values; and be sure to specify the System Database (.mdw file) used to secure the db in ADO connection string. Note: To get full connection string for secured db, login to db, then type in Immediate window:

? CurrentProject.Connection.ConnectionString

This will return full connection string (including many optional arguments than can be safely omitted; you will have to add the Password argument). When using examples from Word be sure to set reference to applicable object library (ADO or DAO). Above example tested in Word/Access 2K, using ADO 2.1 object library.

HTH

Reply | Quote

WOW . I’ve been drawn away to another project temporarily so haven’t tried this yet, but this is so cool I’m at a loss as to how to thank you.

Reply | Quote

Hi dear all
Can any one put an Example for it , i try to do it but can not .
Ashraf

Reply | Quote

Could you give a little more information? What exactly is going wrong?

Reply | Quote

You can’t use ordinary automation on Access 97 because it wasn’t an automation server, but the DAO code posted looks to me like it should work, even on Access 97 because it is using DAO to open the database and get a recordset, not trying to manipulate the application object.

Reply | Quote

I’ve tried this code and it works perfectly, but, sadly when I add code to read the file, it asks displays the login screen for the secured database. The actual line is : ActiveDocument.CustomDocumentProperties(“TAFirst”) = Nz(!ParameterTextValue, “”). Right up to this point, everything is wonderful … I set an index and use the Seek command to find the record I want – no problemo.

Its as if its letting me open the .mdb file and do some things, but not others.

Reply | Quote

I ran some tests in Word & did not encounter this problem. Once the secured db was opened in code, I was able to loop thru recordset & “do stuff” with a Word doc & was not prompted for User ID or password. See attached text file for sample code, exported from Word module (too lengthy to post in its entirety). For this example, I used user-level security wizard to create a secured copy of Northwind.mdb and new workgroup file named “NWSecured.mdw.”, and created simple Word template to capture Employee data from Northwind “Employees” table. When OpenSecureNW() sub runs, 9 new Employee Data docs are created and saved in specified folder, with no errors or prompts.

As noted, I don’t work in Word VBA much; the attd code is simpified version of typical export sub used in Access, modified for Word. For example, replaced NZ function (AFAIK it’s Access-specific) with Format function to avoid possible errors if field value is Null. In Access I use Word Document object variables. I don’t know if ref to ActiveDocument is causing problem. Had no problem setting value for Custom document property (defined in template) for HireDate.

If you can open recordset and read data before problem occurs, then recommend store data in an array or other suitable data structure, close recordset, then “do stuff” in Word doc using the “captured” data. For example, see GetNWEmployeeData() sub in attached file. Sub uses an array of a user-defined Type “NWEmployee” based on analagous fields in NW Employees table to capture data:

Option Explicit

Type NWEmployee
ID As Long
LastName As String * 20
FirstName As String * 10
HireDate As Date
End Type

You can loop thru recordset, populate an array variable based on this structure, then close recordset & use “captured” data to do stuff. If you still can’t resolve issue, recommend post code that generates error.

HTH

Reply | Quote

Your code ran like a racehorse and I was really stumped! Then your comments about NZ (and your *brilliant* idea of using Format instead) got me to thinking …. I had wanted to use NZ, so I had set a reference to the Access Object Library. *NOT* a good idea. The first time it encountered the “NZ” function, it asked for a username and password as per my workstation’s .mdw file! I got rid of the reference and the NZ and its fixed.
A million-billion thanks..

Reply | Quote

Glad you solved problem. When first started exporting Access data to Word, I quickly discovered that it’s NOT a good idea to try to assign a Null value to a Word bookmark or FormField. So I normally use NZ function for any field other than primary key, since there’s no problem setting value of bookmark or FormField to a zero-length string. But since there’s no NZ in Word, in this case I replaced NZ with Format to return a zero-length string in event of null value in field. The Format function known to be versatile, but I think this is one very useful use for function that goes overlooked when working with recordsets in code….

Reply | Quote

Make sure permissions have been removed for the Admin user. Otherwise, anyone could open that database from an unsecured workgroup file.

Reply | Quote