CODEWITHSUNDEEP
sql-serversql-server-2008exceladovba
Dan
Dan

Reputation: 45752

Connecting to SQL Server over network using ADO from Excel VBA

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?

Upvotes: 4

Views: 35694

Answers (2)

Dan
Dan

Reputation: 45752

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.

Upvotes: 4

Russell Hart
Russell Hart

Reputation: 1851

Try this Connection string, 

ConnectionString = "Provider=SQLOLEDB;Data Source=MY-LAPTOP;Initial Catalog=SAFEXlive;User ID=MyUsername;Password=MyPassword"

Is this an AD Domain login? Make sure you have appended the domain to the username e.g, domain\user . I suggest using integrated security instead of this.

Upvotes: 3

Related Questions