[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to open a Access 2003 Form with active directoryQuestion:

Posted on 2009-12-26
11
Medium Priority
?
276 Views
Last Modified: 2013-11-28

Hi Experts,
Sorry for my bad english. I'm a french speaker.
I 'm a bigginner in Access 2003. I 've create a data base  which is on a common hard drive for all users. This data base contains the activity of poeple workink in the office. The users can connect to this hard drive by using their active directory account.
I have a tab in my data base that identify users who are connected to the data base (i found this code on code-source.com). When a user add data, he select his name as the "agent". Actually, every agent can see the data of the others agents.
For confidentiality, because each user must see only is own data, i want the users to be able only to see their own data when they are connected by using their Active directory account.
The main tab, query and form in the data base contains the name of the users.  
So when the user log with his Active directoty account, he must see only his data. Thanks for your help.
 
Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Retourne le nom d'usager fourni lors du branchement au réseau.
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If
End Function


Sub AddEntree()

    Dim dbs As Database
    Dim rstEmployees As Recordset
    Dim strFirstName As String
    Dim strDate As String
    Dim strHeure As String
    Dim strType As String

    Set dbs = CurrentDb
    Set rstEmployees = _
        dbs.OpenRecordset("Mouchard", dbOpenDynaset)

    ' Obtient des données de l'utilisateur.
    strFirstName = fOSUserName
    strDate = Date
    strHeure = Time
    strType = "ENTREE"

' Continue seulement si l'utilisateur a entré
    ' des informations telles que prénom et nom.
    If strFirstName <> "" And strDate <> "" Then

        ' Appelle la fonction qui ajoute
        'l'enregistrement.
        AddName rstEmployees, strFirstName, strDate, strHeure, strType

        ' Affiche les nouvelles données ajoutées.
    Else
        Debug.Print _
            "Vous devez entrer une chaîne comme prénom et nom !"
    End If

    rstEmployees.Close
    dbs.Close

End Sub
Sub AddSortie()

    Dim dbs As Database
    Dim rstEmployees As Recordset
    Dim strFirstName As String
    Dim strDate As String
    Dim strHeure As String
    Dim strType As String

    Set dbs = CurrentDb
    Set rstEmployees = _
        dbs.OpenRecordset("Mouchard", dbOpenDynaset)

    ' Obtient des données de l'utilisateur.
    strFirstName = fOSUserName
    strDate = Date
    strHeure = Time
    strType = "SORTIE"

' Continue seulement si l'utilisateur a entré
    ' des informations telles que prénom et nom.
    If strFirstName <> "" And strDate <> "" Then

        'Appelle la fonction qui ajoute l'enregistrement.
        AddName rstEmployees, strFirstName, strDate, strHeure, strType

    Else
        Debug.Print _
            "Vous devez entrer une chaîne comme prénom et nom !"
    End If

    rstEmployees.Close
    dbs.Close

End Sub


Function AddName(rstTemp As Recordset, _
    strFirst As String, strLast, strHeure, strType As String)

    ' Ajoute un nouvel enregistrement dans un objet
    ' Recordset avec les données passées par
    ' la procédure appelante. Le nouvel enregistrement
    ' devient l'enregistrement en cours.
    
    With rstTemp
        .AddNew
        !Nom = strFirst
        !Date = strLast
        !Heure = strHeure
        !Type = strType
        .Update
        .Bookmark = .LastModified
    End With

End Function

Open in new window

STAT-2009.mdb
0
Comment
Question by:alaini
  • 7
  • 3
10 Comments
 

Author Comment

by:alaini
ID: 26126048
Thanks for your help.
0
 

Author Comment

by:alaini
ID: 26126052
waiting for your help !
0
 

Author Comment

by:alaini
ID: 26127009
waiting for the solution.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:alaini
ID: 26160849
Hi Expert,
Ok.  I'm still waiting for your solution. This question is verry important for me.
Best wishes for the new year 2010 to all the expert team.

Regards
Alaini
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 26167647
It looks as if you can get the UserName, using your fOSUserName function. From there, you would then need to limit your forms Recordsets to ONLY those records which are relevant to each user. How you do that depends on how your database is structured, but basically, if you have a field in your table named UserName, and you have "tagged" each record as belonging to a user by inserting a name in that field, then you'd build a query that would use your fOSUserName function, and use that as the Recordsource of your Form.

I'd do this in the Open event of your form:

Sub Form_Open()
  Me.Recordsource = "SELECT * FROM SomeTable WHERE UserName='" & fOSUserName & "'"
End Sub

Of course, your tables must already be structured in such a way where they are "tagged" as belonging to a specific user; if they are not, then you cannot do what you're asking to do.
0
 

Author Comment

by:alaini
ID: 26189215
Hi expert,

Thanks a lot for this firt step.
How my tables must be structured in such a way where they are "tagged" as belonging to a specific user ?
Have have put a copy of my database "stat_2009".mdb with my question.
Can you tell me if my structure is ok.

Regards
Alaini
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 26189301
You would add a field to each table where you can define who "owns" that record. For example, add a "sUser" field, then fill that field as appropriate to "tag" it to a specific user. When your database opens, you would run code such as I describe earlier to fill the form with ONLY those records relevant to that user.

As to HOW you do this - I'm not sure, since I have no idea how you determine who "owns" a specific record. You can add code in the Form's Current event to fill a field in a New record, if you want the currently logged in user to "own" any records they add:

Sub Form_Current()
  If Me.NewRecord Then Me.txUserName = fOSUserName
 End Sub

Assuming that you have a control name "txUserName", and that control is bound to the table field where you wish to store that information, then this would "tag" a New record with the value retrieved from fOSUserName.

I've glanced at your table structure and it looks okay to me, but I have no idea what, exactly, you're storing so I really cannot comment on it.

Also, you mention that you have this database on a network share, and it's used by several people. If that's so, then you should split this database into a BackEnd (tables only) and a FrontEnd (everything else, with links to the Backend tables), and you should then distribute a COPY of the FE to each user (i.e. install it on their workstation - do NOT allow them to run the FE from a network share). You'd place the BE on that network share, and each copy of your FE would "link" to that backend. Access includes a splitter wizard to help you with this. This is the correct method to use when working with multiple users. Failing to implement this architecture can cause some very odd issues, and could result in your users viewing other users records, or running into data conflicts.
0
 

Author Comment

by:alaini
ID: 26192330
Hi expert,

I was thinkink about this way of solution when a create the data base:

All the data in the database are stored in a query call " Requete total champs".
In this query, there is a field call " Agent " and a other fiels called " Direction ".
"Agent" is the name of the personne who is connected and " Direction" is the team of the agent. Each team have a manager. So the manager must be able  to see the data of the agent of his team (direction).

So If i apply the solution you propose :
Agent =  fOSUserName
But how can' i do for the manager of the team ?

Thanks for your help.
I agree your solution.

 
0
 
LVL 85
ID: 26193115
You would then need to somehow associates Agents with their "Team". This would need to be done through another table, and would of course increase the complexity of this quite a bit. You may be able to get by with adding another field to the your Agent table to show who their manager would be, and you could then query based on that. Otherwise, you'll need to build some form of association between Agent and Direction
0
 

Author Comment

by:alaini
ID: 26194719
Thanks a lot...
I see now how to manage with this solution..

I hope someone could give me the complete solution including the management
of the team.

Best regards

Alaini
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question