How to connect to Microsoft SQL Server using VBA?

转载：How to connect to Microsoft SQL Server using VBA? - Simple Excel VBA

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.

1 2	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.

1	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.

1	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.

1 2	.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.

1 2	.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.

1
2
3

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.

1 2	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.

1
2
3

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.

1	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.

db excel