Connecting to SQL Server over network using ADO from Excel VBA
ado, excel, sql-server, sql-server-2008, vba
Solution
Solved. The answer is rather infuriating. The problem is in fact with the connection string, with the UID. Believe it or not changing `...UID= MyUsername;...` to `..UID=MyUsername;...`, i.e. removing the space character, was all it took! Thanks for suggestions though.
Problem
I need to connect to a SQL Server DB running on my laptop from another computer on a network. I am doing this both from a C# app as well as in a VBA macro. Both run perfectly on my laptop and the C# app runs perfectly over the network. However I cannot connect using VBA over the network. This is my connection string: `ConnectionString = "Driver={SQL Server};Server=MY-LAPTOP; DAtabase=SAFEXlive; UID = MyUsername; PWD=MyPassword"` Aside from the 'Driver={SQL Server}' this is the same as the connection string I am using in the C# app which works. I am then using the following code (With a reference to Microsoft ActiveX Data Objects 6.0 Library in VBE) to open the connection: ``` Dim oConnection As Connection Set oConnection = New Connection oConnection.ConnectionString = strConnectionString oConnection.Open ``` This works correctly if I run it on my laptop but if I run it on another computer on the network I get the error: "Run-time error '-2147217843 (80040e4d) [Microsoft][ODBC Server Driver][SQL Server]Login failed for user..." and the user it specifies it the windows log in for the computer. Are there some security settings I need to set in code or in excel? Or am I constructing the connection string incorrectly? What am I doing wrong?