ODBC Connection Error (1 Viewer)

[jsdba]

I'm testing connnecting to sql server from another computer. I get a runtime error. See attached picture.

Code work if i run from my computer but not from another user\computer. I'm not sure what the problem is. Could it be that i'm using windows authentication to login to the sqlserver? Should i change authentification mode to sql server login?

Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strName As String
Set cnn = New ADODB.Connection
[COLOR="Red"]cnnStr = "Driver={SQL Server};Server=PC162;Database=AdventureWorks2014;Trusted_Connection=Yes;"[/COLOR]
strName = "Staine"
cnn.Open cnnStr
strSQL = "UPDATE Person.Person SET LastName= '" & strName & "' WHERE BusinessEntityID=10703"
cnn.Execute strSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

I've set up ODBC driver on the other computer and connected successfully. I'm really not sure what i'm missing here. Any help is greatly appreciated.

 

[Ranman256]

Try it without the driver portion. (mine doesnt have it and it works)
cnnStr = "Server=PC162;Database=AdventureWorks2014;Trusted_Connection=Yes;"

 

[jsdba]

No go Ranman. New Error Data source name not found and no default driver specified. The problem seems to be with the "login" from another user on another computer.

 

[TJPoorman]

If you are using Windows authentication then the user that is accessing the datasource has to be specified in the SQL Server security.

Your options are either:
A. Add the users to the security on the Server
B. Change your code to use SQL Server authentication (which is less secure)

EDIT:
You could also add an AD user group and add your users to the group. This way you don't have to modify your SQL Server's security every time someone new comes in or someone leaves.

 

[jsdba]

If you are using Windows authentication then the user that is accessing the datasource has to be specified in the SQL Server security.

Your options are either:
A. Add the users to the security on the Server
B. Change your code to use SQL Server authentication (which is less secure)

EDIT:
You could also add an AD user group and add your users to the group. This way you don't have to modify your SQL Server's security every time someone new comes in or someone leaves.

Thanks TJPoorman, i think this is the answer i was looking for, i didn't know i had to do this. I haven't done it yet but i think this is going to work.