apiGetUserName Function

[koolwaters]

Can someone please explain EXACTLY how this function work?
The reason I want to know is that I have a split database using in the office.

We have 3 types of users:
Power User: No restrictions
Laptop User: Limited Restrictions
Desktop User: Very Limited Restrictions

A desktop user is unable to right click or add files to thier desktops and cannot access the C drive. The laptop users can.

The problem I am having is that only the laptop users are able to log into the database.

This is the code I got from the Intenet to check the user name.

Option Compare Database
Option Explicit

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

Function fOSUserName() As String
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 = vbNullString
    End If
End Function
Function getempstatus() As String
getempstatus = DLookup("UserLevel", "tblUsers", "UserName = '" & fOSUserName() & "'")
End Function
Function fOSMachineName() As String
Dim lngLen As Long, lngX As Long
Dim strCompName As String
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
End Function

This is the code I created to check the user name added to the database against the network user name.

Public Function ckUserLoggingin()
    Call setrst
    rst.Open "select * from tblUsers"
    If rst.RecordCount >= 1 Then
        rst.MoveFirst
        For i = 1 To rst.RecordCount
            If fOSUserName() = rst!UserName Then
                If rst!Active = "Yes" Then
                    Call setrst1
                    rst1.Open "select * from tblEmpLogin where rec =1"
                    rst1!empstatus = getempstatus()
                    rst1!EmpName = fOSUserName()
                    rst1.update
                    rst1.Close
                    Set rst1 = Nothing
                End If
                DoCmd.RunMacro "Startup"
                Exit Function
            End If
            rst.MoveNext
        Next
        i = MsgBox("Access Denied! You do not have sufficient privilidges to open this file.  Please check with the system Administrator.  Quitting...", vbCritical, "Access Denied")
        DoCmd.Quit
    End If
End Function

setrst Function:

Private Sub setrst()
    
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseClient

End Sub

The ckUserLoggingin function is called from an AutoExec macro.

The latop users were able to access the database but when a desktop user tries, they get an error in a message box:

Title:
Action Failed

Macro Name:
AutoExec

Condition:
True

ActionName:
RunCode

Arguments:
ckUserLoggingin

Can anyone also tell me if the restrictions would cause this problem because when I had initially researched the apiGetUserName function, from my limited knowledge, I understood it is supposed to be using LDAP to query the domain controller. If that is the case, then I do not think the restrictions on the machines would make a difference.

Please help!!!! I appreciate any feedback.

Thanks.

 

[xenou]

Are restrictions for the desktop users from your IT department and related to their use of the PC in general? Or are there also differences in your database for different kinds of users?

Some ideas:
1) Put a msgbox in the first line of the autoexec routine. When the msgbox pops ups, hit {control + pause/break}. You can now step through the code with F8 from that point on to do a line by line evaluation.

2) Access must create a lock file. Be sure the desktop users have enough rights to create the lockfile. Therefore, the db must not be on the C: drive or in any such restricted directory.

3) If the users have restricted rights int the DB, be sure they have rights to run everything associated with the login processes - a chicken/egg problem (need to authenticate the user, but the user must run some DB processes in order to authenticate).

 

[Norie]

Why don't you just set up security groups in Access itself?

It might not be a good idea to rely on what the API functions you are using returns.

They are probably doing what they're supposed to but that might not be quite what you want.

 

[koolwaters]

Hi Xenou,

The restrictions on the computers are set by the IT department and are related to the usage od the computers. The database also has restrictions for different users.

The backend is stored on the server and there is a front end folder on each user's machine.

I will try your suggestions tomorrow and let you know.

Regarding your suggestion Norie, I always have problems when I have used Access security so I do not use it.

 

[Norie]

Michelle

I know Access security isn't the greatest but I honestly wouldn't recommend relying on using Windows API to get the username.

It will work to some extent but it is possible that it won't work as you want/expect it to.

Also I don't quite see how your code is meant to work.

Can't you just use the username in the criteria of the first recordset?

 

[boblarson]

Michelle

I know Access security isn't the greatest but I honestly wouldn't recommend relying on using Windows API to get the username.

Sorry Norie but that is one of the silliest statements I've seen you make. What are you thinking. The Windows API is one of the BEST ways to get the network login user name of the user.

 

[Norie]

Michelle

As far as I'm aware GetUserName doesn't query the LDAP.

To do that, as far as I know anyway, you would need to use something like ADO and have to create a connection or 'get' a connection to the active directory.

Have you tried stepping through the code to see exactly what's happening?

Maybe it would be simpler to make the check of the name the first thing you do?

Then if needed you can quit Access if you need to.

 

[koolwaters]

Hi Norie,

I will take a look at stepping through the code this afternoon and let you know.

Thanks for your assistance as usual.

 

[koolwaters]

I got the problem sorted.

Thanks Xenou!

IT checked and the users only had Read permissions on the C:\Data folder.

They have changed it to Full Control and that has solved the problem.

Thanks again for the assistance.