ODBC call failed

[mana]

Hello,

i have the following code in my access and i use it in a shared network, when i run the program i don't have any problems but when i run it fro other users i have the ODBC call failed error. can you help me please? I am new to vba

Function Get_Bearbeiter_Detail(ByVal vBearbeiter_Code As String, Optional ByRef vBearbeiter_Name As String, Optional ByRef vReadOnly_Flag As Boolean, Optional ByRef vAnalyser_Flag As Boolean, Optional ByRef vAdmin_Flag As Boolean) As Boolean
 
 
On Error GoTo Err
   DoCmd.Hourglass True
 
    Dim rst As ADODB.Recordset
 
    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient
 
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
 
    Dim prm As ADODB.Parameter
    Set prm = New ADODB.Parameter
 
    With cmd
        .ActiveConnection = CurrentProject.AccessConnection
        .CommandText = "qry_mgntUserManagement"
        .CommandType = adCmdTable
        .NamedParameters = True
         Set prm = .CreateParameter("@Bearbeiter_Code?", adVarChar, adParamInput, 10, vBearbeiter_Code)
        .Parameters.Append prm
 
 
 
        'rst.Open
        Set rst = .Execute
    End With
 
    If Not rst.EOF Then
        With rst
            vBearbeiter_Name = !Username
            vReadOnly_Flag = !ReadOnly_Flag
            vAnalyser_Flag = !Analyser_Flag
            vAdmin_Flag = !Admin_Flag
        End With
        Get_Bearbeiter_Detail = True
    Else
        With rst
            vBearbeiter_Name = "Nicht Autorisiert"
            vReadOnly_Flag = False
            vAnalyser_Flag = False
            vAdmin_Flag = False
        End With
        Get_Bearbeiter_Detail = False
    End If
 
    DoCmd.Hourglass False
 
    rst.Close
    Set cmd = Nothing
    Set prm = Nothing
    Set rst = Nothing
'End If
 
    Exit Function
Err:
    DoCmd.Hourglass False
    MsgBox "Leider hat diese Funktion einen Fehler ausgelöst - bitte wenden Sie sich and die EDV! - (Get_Bearbeiter_Detail - '" & Err.Description & "')"
End Function

i have this error : Leider hat diese Funktion einen Fehler ausgelöst - bitte wenden Sie sich and die EDV! - (Get_Bearbeiter_Detail , ODBC call failed)

 

[JHB]

Comment out the errorhandling, (the line "On Error GoTo Err"), to get the code line which cause the error!

 

[mana]

hello

what do you mean
when the other users run the program they get the below message:

Leider hat diese Funktion einen Fehler ausgelöst - bitte wenden Sie sich and die EDV! - (Get_Bearbeiter_Detail - ODBC call failed

 

[mana]

hello

can someone help me in this error?

 

[JHB]

hello

what do you mean
when the other users run the program they get the below message:

Leider hat diese Funktion einen Fehler ausgelöst - bitte wenden Sie sich and die EDV! - (Get_Bearbeiter_Detail - ODBC call failed

But that isn't tell which codeline cause the problem, does it, (therefore turn the errorhandling of until you've solved the problem)?

 

[namliam]

My best guess is that your backend database has a user/pw to it that is unique to the user logged into windows. Get autorization for your users to use the database and you should be good to go.

 

[mana]

hello

thank you for your reply
sorry but i am new to ms access and i don't know exactly what to do
can you tell me please how i should give authorization to my users?

 

[namliam]

What table / connection is your query qry_mgntUserManagement using?

 

[mana]

it is using "dbo_Nacharbeitszeit_usermanagement"

 

[namliam]

And that is a ODBC table that connects into (what looks like) a SQL Server database.

Your users need access to that database

 

[mana]

hello

yes it is a linked table to the sql server database
i gave permissions to the users to this table
is it not enough?

 

[namliam]

Depends on the access rights on the SQL server, if your ODBC connection fails for exactly what you do... then the likely suspect is they dont have the proper security settings somewhere .... Though I am no SQL Server expert, do you use the windows user to authenticate? or use a single functional account to access the SQL Server?

 

[mana]

thank you for your reply

i use the windows authentication

 

[namliam]

And do your users have access to your SQL on their windows account?

 

[mana]

I have just the user name of the users
i also add the sql_user, i think it includes all of the users, am i right?
do you mean this?

 

[namliam]

I dont know much about priviliges needed on the SQL server, they will need I believe access to the Server, database and table.