Users on Different Domains

[bhj83]

First, let me state I'm by no means an Access expert. When I took my job, I found out the guy before me had created a lot of Access databases that are still in use. I previously had not used Access much at all.

I'm having an issue with users on a different domain accessing the database. My predecessor set up the database to find the domain and user login information and use that to login to the database. Therefore, I have to add users and put their domain and user name. Well, there are now individuals who are on different domains and they are unable to access the database even though I've entered the information correctly in the database. I'm including the VB Script for the permissions module. Any help would be greatly appreciated! Thanks!

Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function UserName() As String
' Returns the network login name
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
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = ""
End If
End Function
Public Function ValidateUser(NetworkName As String) As Integer
On Error GoTo ErrHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String
strsql = "SELECT A.DomainName, A.EmpName, A.RoleID, A.Active FROM tblUsers AS A " _
& "WHERE ((A.DomainName) = '" & NetworkName & "');"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strsql)
rst.MoveFirst

Select Case rst.RecordCount
Case Is = 1
If rst.Fields("RoleID") > 0 Then
Role = rst.Fields("RoleID")
End If
ValidateUser = 1
Case Else
ValidateUser = 0
Exit Function
End Select
ValidateUser_Exit:
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
ValidateUser = 0
Resume ValidateUser_Exit
End Function
Public Function SetControlPermissions(lngRole As Long, frm As Form) As Boolean
On Error GoTo ErrHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strsql As String, strCtl As String
Dim blnVisible As Boolean, blnEnabled As Boolean, blnLocked As Boolean

strsql = "SELECT A.RoleID, A.ControlName, A.Form, A.IsVisible, A.IsEnabled, A.IsLocked, A.Type " _
& "FROM tblObjectPermissions AS A " _
& "WHERE (((A.RoleID) = " & lngRole & ") AND ((A.Form) = '" & frm.Name & "'));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strsql)
rst.MoveFirst

Do
strCtl = rst.Fields("ControlName")
blnVisible = rst.Fields("IsVisible")
blnEnabled = rst.Fields("IsEnabled")
blnLocked = rst.Fields("IsLocked")


With frm.Controls(strCtl)
If rst.Fields("Type") <> "Button" Then
.Locked = blnLocked
End If
.Visible = blnVisible
.Enabled = blnEnabled
End With
rst.MoveNext
Loop While Not rst.EOF

SetControlPermissions = True

SetControlPermission_Exit:
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

ErrHandler:
SetControlPermissions = False
Resume SetControlPermission_Exit
End Function

[pkstormy]

When you say they are unable to access the database, what do you mean?

Do you mean they can't see the database or that they can't open it or that they can open it but then get an error or it doesn't let them see/edit something?

If they can't see the database to even open it, that's a different problem not related to MSAccess.

If they can't open it, check their permissions to the folder making sure they have both read and write permissions to the folder (including to the backend db if it's a split db.)

If they can open it but 'then' get an error or something else happens, then try adding this in the code:


msgbox "UserName = " & Username

...after...
If lngX <> 0 Then
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = ""
End If

and

msgbox "NetworkName passed to ValidateUser function = " & NetworkName

...after...
Public Function ValidateUser(NetworkName As String) As Integer

Then compare what the message box says with whats in the 'permissions' table (which looks like it's called: A) to make sure it's returning/matching returned values to what is in the table (ie. add other msgbox code in places to see where the code is either stopping or what other values are being returned.)

If UserName and NetworkName are both blank in the msgbox, then you'll most likely need to find why they are blank which might have to do with how that user logs in. There are some different methods on how to return these values (see the GetUser method in the code repository). For example, we have users that log in as their loginID but behind the scenes, they are really logging into the computer as 'dom-user' (and that's what using the code similar to what you supplied would recognize.) I then changed the code to grab the UserName differently (ie. using UserName=Environ('user_name') without the API call) which resolved the problem.