Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    Assuming you are using User-Level security, you can use something like this on Form_Load event:
    <pre> If IsGroupMember(CurrentUser, "Admins") Then
    Me.SSN.Visible = True
    Else
    Me.SSN.Visible = False
    End If
    </pre>

    The IsGroupMember function tests to see if a user (normally CurrentUser) is a member of a specified group:
    <pre>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
    </pre>

    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.

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks, Mark. That did the trick.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Secured DB from Word (A2K)

    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 <!t>[TABLE1]<!/t>;"
    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

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Secured DB from Word (A2K)

    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 <!t>[TABLE1]<!/t>;"
    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

  8. #8
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Secured DB from Word (A2K)

    WOW <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>. 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.

  9. #9
    2 Star Lounger
    Join Date
    Nov 2002
    Posts
    160
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Secured DB from Word (A2K)

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

  10. #10
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Secured DB from Word (A2K)

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

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Secured DB from Word (A2K)

    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.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Secured DB from Word (A2K)

    I've tried this code and it works perfectly, but, sadly <img src=/S/sad.gif border=0 alt=sad width=15 height=15> 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.

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Secured DB from Word (A2K)

    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
    Attached Files Attached Files

  14. #14
    Star Lounger
    Join Date
    Feb 2001
    Location
    Vancouver, BC, Br. Columbia, Canada
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Secured DB from Word (A2K)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> 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..

  15. #15
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Secured DB from Word (A2K)

    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •