Totos Totos - 1 year ago 208
SQL Question

SQL Management Studio 2012 with Excel vba connection string set up

I would like to set up a vba code that would connect to sql management studio 2012 a run the query, which I would specify in the vba code. I have read every similar question here on stack overflow but when I try to replicate them, I always get an error, ussualy that the login failed for user.

I think I am setting up the string connection wrong. Also, I would need the user authentication by Windows authentication.

I know the database name, server name and my user name.

This is the code I am using and which is giving me an error.

Sub ConnectionExample6()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection

' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" & _
"server=SERVER NAME;uid=USER ID;pwd=MyPassword;database=DATABASE NAME"

' Create a Recordset by executing an SQL statement.
Set rs = cnn.Execute("Select top 100 * from "TABLE NAME" aac " & _
"where aac.EffectiveDate = '10/04/16'")

' Close the connection.

End Sub

Can someone walk me through the connection string and how to set it up step by step? Thank you.

Answer Source


If you're connecting to SQL Server, you should prefer Windows Authentication if that's available: you create a Login at server level for a group of Active Directory users, and then you create a Windows-Authenticated User in your database using that login.

That way you are keeping passwords and usernames out of hard-coded strings, and let the network deal with authentication.

Assuming you don't want to be maintaining passwords in dozens of copies of macro-enabled workbooks across your network, you'll want to use Windows Authentication.

Integrated Security=SSPI; Persist Security Info=True;


Connection strings are annoying - seems there's a different format/wording for every single different thing that's able to parse them!

Since you're using ADODB, you'll want to specify a Provider, a Data Source and, optionally, an Initial Catalog:

Provider=SQLOLEDB.1; Data Source=SQL Server instance name; Initial Catalog=Database name;


Each connection can be monitored on the server; when building your connection string you can optionally specify a Workstation ID to identify the machine the connection is for.

Workstation ID=computer name;

You can get the computer name by fetching the environment variable value, using Environ$:

Private Function GetWorkstationId() As String
    GetWorkstationId = Environ$("ComputerName")
End Function

Given a SQL Server instance named SomeSqlServer, a database named SomeDatabase, and using Windows Authentication, the ADODB connection string would look like this:

Dim connString As String
connString = "Provider=SQLOLEDB.1; Data Source=SomeSqlServer; Initial Catalog=SomeDatabase; Integrated Security=SSPI; Persist Security Info=True;"

Given SQL Authentication (with a hard-coded user name and password) for SomeUser with SomePassword:

connString = "Provider=SQLOLEDB.1; Data Source=SomeSqlServer; Initial Catalog=SomeDatabase; UID=SomeUser; PWD=SomePassword;"


You don't want to be concatenating arbitrary user input into a WHERE clause; avoid executing an SQL string directly from the ADODB.Connection object.

Instead, create an ADODB.Command, and parameterize your query.

Dim sql As String
sql = "SELECT Foo, Bar FROM dbo.FooBars WHERE Foo = ? AND DateInserted > ?"

Here we have 2 parameters.

First we create the command:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = sql

Then its parameters, assuming we have their respective values in param1Value and param2Value local variables:

Dim param1 As ADODB.Parameter ' a string parameter
Set param1 = New ADODB.Parameter
param1.Type = adVarWChar
param1.Direction = adParamInput
param1.Size = Len(param1Value)
param1.Value = param1Value
cmd.Parameters.Append param1

Dim param2 As ADODB.Parameter ' a date parameter
Set param2 = New ADODB.Parameter
param2.Type = adDate
param2.Direction = adParamInput
param2.Value = param2Value
cmd.Parameters.Append param2

Then we retrieve the recordset by executing the command:

Dim results As ADODB.Recordset
Set results = cmd.Execute

Of course this looks very verbose, but it can easily be refactored into functions dedicated to creating a parameter given a value of a certain type.

As a result, you avoid this situation, because you're no longer executing arbitrary user input concatenated into a query:

XKCD: Exploits of a Mom

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download