Second call to method using ADODB cmd.execute gives ODBC - call failed

Question

The second call to AddUserToRole always throws ODBC - call failed.

If I try to add user to role "Everyone" first, it fails on "Sales". If I try to add user to "Sales" first, it fails on "Everyone".

The first call always works, the second always fails.

I call these methods:

Dim servUserRoles As New srvUserRolesService
servUserRoles.AddUserToRole CreateResults, "Sales"
servUserRoles.AddUserToRole CreateResults, "Everyone"

Here is the method:

Public Function AddUserToRole(userId As String, roleName As String) As Boolean
    Dim cmd As New ADODB.Command
    Dim affected As Long
    
    cmd.ActiveConnection = CurrentProject.AccessConnection
    cmd.CommandType = adCmdText

    cmd.CommandText = " INSERT INTO MSA_UserRoles(UserId, RoleId) " & _
                      " VALUES([parUserId], [parRoleId])  "
    
    cmd.Parameters.Append cmd.CreateParameter("parUserId", adVarChar, adParamInput, 50, userId)
    Dim servRoles As New srvRolesService
    Dim RoleId As String: RoleId = servRoles.GetRoleIdByName(roleName)
    cmd.Parameters.Append cmd.CreateParameter("parRoleId", adVarChar, adParamInput, 50, RoleId)

    cmd.Execute affected
    
    AddUserToRole = affected
    Set cmd = Nothing
End Function

Answer 1

I see that in one instance you're calling srvUserRolesService, in the other srvRolesService. Is that significant?

Neither of these services is natively part of Access. How are you accessing them?

---

-Tom. Microsoft Access MVP

Answer 2

srvUserRolesService and srvRolesService as just a couple classes I created. They each perform services related to the UserRoles and Roles entities in my project respectively. They perform business logic and CRUD in the database mostly, which is an Azure SQL DB.. But the code I posted is all that the code being ran. The GetRoleByName() function is the only thing you don't see, but it just does a DLookup and returns the RoleId when passed a Role name.

I was using the srvRolesService just to query the RoleId because its needed to pass into the INSERT as a parameter.

Here is the error:

debug.Print err.Number & ":  " & err.Description
-2147467259:  ODBC--call failed.

debug.Print errors.Count
 1 
 
debug.Print errors(0).Number & ":  " & errors(0).Description
3265:  Item not found in this collection.

Answer 3

Well I'm relieved and feel a little embarrassed. It turns out that my function servRolesService.GetRoleIdByName() WAS the problem. It was returning the same exact value every time it was called no matter what the RoleName was.

Public Function GetRoleIdByName(Name As String) As String
    GetRoleIdByName = Nz(DLookup("ID", "MSA_Roles", ""), "Name = '" & Name & "' ")
End Function

The second transaction could not commit because the table in question(MSA_UserRoles) doesn't allow duplicates on UserId, RoleId.

I mixed up the Nz [ValueIfNull] paramater with the Dlookup [Criteria] paramater.

Changed to:

GetRoleIdByName = Nz(DLookup("ID", "MSA_Roles", "Name = '" & Name & "' "), "")

I wasn't expecting that because the error message. I usually get some sort of message from SQL Server stating that the insert would violate some sort of constraint..

Thanks for your help