When it comes to big amount of data, Excel application is not the best solution to work with, in case of storage. Much better fit would be a database like Access or MSSM. In this article I’m going to show You how to connect to Microsoft SQL Server using VBA.

Database & server
In my case I got AdventureWorks2016 database installed on my PC. I connected to that base and the view of Object Explorer in Managament Studio looks like this.

Here You can see the server name, database name and tables name in Tables folder, which all will be necessary in further steps.
References
To be able to create connection and then operate on pulled data from database, You need to check in the Tools/References 2 checkboxes: Microsoft ActiveX Data Objects Library and Microsoft ActiveX Data Objects Recordset Library.

It doesn’t have to be the version 2.8, just like in the screen above. You have much more possibilities.

When choosing the references try to think about the future users (ot their Office version) of this code You create.
ADODB Connection
After setting up the references You can declare connection of ADODB library.
Dim connection As ADODB.connection
Set connection = New ADODB.connection
Connection String
This is the main thing of ADODB connection, to be able to connect to the database. You need to know at least the provider, server name, database name and authentication method.
For MS SQL use SQLOLEDB.1 as provider. That should always work, unless You know that the provider for your database is different.
The server name You can see at the top of the object explorer.

So in my case it is (LocalDb)\LocalDbTest.
Let server_name = "(LocalDb)\LocalDbTest"
The database name is the name under the Databases folder tree in Object Explorer.

In my case this is AdventureWorks2016.
Let database_name = "AdventureWorks2016"
The authentication method depends on that if You pass the credentials in the connection string or use windows authentication and on provider. You can choose from the values: true
, false, yes, no and sspi.So putting all together it can look like this.
.ConnectionString = "Provider=SQLOLEDB.1;Server=" & server_name & _
";database=" & database_name & ";Integrated Security=SSPI;"
Solution to connect local database
In case of MS SQL use SQLOLEDB.1 as provider, but if You got your database locally, as I have, go with SQLNCLI11. This is something I was fighting with and looking for hours to connect.
.ConnectionString = "Provider=SQLNCLI11;Server=" & server_name & _
";database=" & database_name & ";Integrated Security=SSPI;"
Open connection & state check
After completing the connection string, You can also set other properties like timeout. In the end open the connection using .Open.
With connection
.ConnectionString = "Provider=SQLNCLI11;Server=" & server_name & _
";database=" & database_name & ";Integrated Security=SSPI;"
.ConnectionTimeout = 10
.Open
End With
If there was no error check the state of database You connected.
If connection.State = 1 Then
Debug.Print "Connected!"
End If
SQL query
Having opened database connection You need to ask for data, I mean to build SQL query. In my case I just want to take all (*) data from TestTable table. The database name in this query is not really needed, because it is already in the connection string.
Dim sqlQuery As String
sqlQuery = "Select * from [AdventureWorks2016].[dbo].[TestTable]"
If You are not familiar with SQL I can say that the basics are even easier than VBA. As I said, basics of SQL.
Recordset – copy paste data from database
If You want to get the data from the query, You need to create Recordset of ADODB library. There are 2 main things – sqlQuery and connection. About the rest of properties You can read here.
Dim rsSql As New ADODB.Recordset
rsSql.CursorLocation = adUseClient
rsSql.Open sqlQuery, connection, adOpenStatic
After You .Open the recordset – get the data from database with SQL query using created connection, You can paste everything in chosen location.
ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rsSql

And just like that all the data from the specified table is in the chosen range of your Workbook.

And remember that this method is not pasting headers, only the values of the columns below headline!
Connect to MS SQL code
Option Explicit
Sub connect2mssql()
Dim connection As ADODB.connection
Set connection = New ADODB.connection
Dim server_name As String
, database_name As String
Let server_name = "(LocalDb)\LocalDbTest"
Let database_name = "AdventureWorks2016"
With connection
.ConnectionString = "Provider=SQLNCLI11;Server=" & server_name & _
";database=" & database_name & ";Integrated Security=SSPI;"
'SQLOLEDB.1
.ConnectionTimeout = 10
.Open
End With
If connection.State = 1 Then
Debug.Print "Connected!"
End If
Dim sqlQuery As String
sqlQuery = "Select * from [AdventureWorks2016].[dbo].[TestTable]"
Dim rsSql As New ADODB.Recordset
rsSql.CursorLocation = adUseClient
rsSql.Open sqlQuery, connection, adOpenStatic
ThisWorkbook.Sheets(1).Range("A1").CopyFromRecordset rsSql
End Sub
So, this is how You connect to Microsoft SQL Server using Excel VBA!
At first sight it seems like complex stuff, but in the end it is not that hard and it opens wide range of possibilities to automate data from other sources than only Excel workbooks. Combining the VBA knowledge with some more complex SQL queries can lead to really big tools dealing with tons of data. It is much easier to connect to the source than converting the data to Excel format and then start the macro. Also it speeds up the whole work a lot.
I was searching for this. Many thanks. One more question, so can we run complex queries like joins in vba and pull the data from sql server? Thanks in advance.
Hello
Yes, You can!
Just like I showed You, put your query in the sqlQuery variable and pull the data 🙂
Thank you so much for this information! I have always been successful in connecting my VBA solutions in MS Access as a client to my company’s SQL Server, but I struggled for a long time to figure out how to connect to a SQL Server Express LocalDB instance on my personal computer until now.
Happy to hear that! 🙂
That stuff works. Thanks a lot – you made my day.
You’re welcome!
Thank you very much Tomazs. The option to use SQLNCLI11 Provider was key to make a succesfull connection using Execel VBA. I searched it for days, very helpfull.
Thank You, happy to hear that!