VBA and SQL Server – Excel kitchenette

In several previous “recipes” I wrote about how to connect to Microsoft SQL Server from Excel in order to retrieve data to a worksheet. These were all “innocent” operations, since you could only read the data from the database, not to change them. Sometimes it is necessary to create a program that performs more complex manipulation over the database by running appropriate SQL query, which will be discussed in the text that follows.

In order to access the database you must first include the appropriate library which contains needed objects and methods. This is achieved by using ALT + F11 to enter the VBA Editor. Then you need to open the Tools menu, and further select the References option. A window will appear that contains a list of available libraries where it is necessary to check the Microsoft ActiveX Data Objects 2.0 library.

Let’s create a program. First you need to declare three variables:

Dim c As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connectionstring As String

First variable is needed to create a database connection, second is used for creating a recorset and the third is used for a connection string. The connection string differs from database to database, and iti is used to define the connection parameters and the database server with which we want to connect. More information can be found on the following link: http://www.connectionstrings.com

We define the string for connecting to the database as follows:

connectionstring = “Provider=SQLOLEDB;Data Source=EKSQL;” & _
“Initial Catalog=TESTDB;” & _
“Integrated Security=SSPI;”

Behind the Data Source word should indicate the name of the instance of the SQL server, and behind the Initial Catalog the name of the database that we want to connect to. After defining the string, we make a new connection and a recordset, and further we need to execute the desired SQL query using the Execute method. Its syntax is:

Execute (<SQL query>)

In the specific case, if we want to retrieve all the records from the Partners table, we will write:

Set rs=c.Execute(“SELECT * FROM Partners;”)

The Execute method also allows you to run other SQL commands, some of them can change or delete records, so be very careful when using it.

In the end, it is further necessary to retrieve data into a worksheet and close the connection to the database, and how to perform this you can see in the program that follows:

Sub SQL_Connection()

    Dim c As ADODB.connection
    Dim rs As ADODB.Recordset
    Dim connectionstring As String

    connectionstring = “Provider=SQLOLEDB;Data Source=EKSQL;” & _
                  “Initial Catalog=TESTDB;” & _
                  “Integrated Security=SSPI;”

    Set c = New ADODB.connection
    Set rs = New ADODB.Recordset
    c.Open connectionstring

Set rs = c.Execute(“SELECT * FROM Partners;”)

    If Not rs.EOF Then
        Sheets(1).Range(“A1”).CopyFromRecordset rs
        rs.Close
    Else
        MsgBox “Error, no data!”, vbCritical
    End If

    If CBool(c.State And adStateOpen) Then c.Close
    Set c = Nothing
    Set rs = Nothing

End Sub