Control Database form Excel through VBA

Question

I am trying to create an excel addin which has set of Functions to pull value from the database (I use MS SQL Server). So my query will return only one record set. I use something like below in my vba code.

Using Excel VBA to run SQL query

But the problem with this is if I have my custom function in 100 cells, the macro makes connection to the DB everytime and retrive data from the DB.

Is there a way where I can make one connection and use that connection to write as many queries as I want?

Answer 1

Simple, run all your 100 function/ loops to access the database. One you've finished then close the connection. Look at your modified code below...

Option Explicit

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
                  "Initial Catalog=MyDatabaseName;" & _
                  "Integrated Security=SSPI;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString

    '>>>> run 100 loops
    Dim i As Integer

    For i = 1 To 100
        Set rs = conn.Execute("SELECT * FROM Table" + 1 + ";")

        ' Check we have data.
        If Not rs.EOF Then
            ' Transfer result.
            ' I assume that you've 100 Sheets
            Sheets(i).Range("A1").CopyFromRecordset rs
        ' Close the recordset
            rs.Close
        Else
            MsgBox "Error: No records returned.", vbCritical
        End If
    Next

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub

I've add 100 loops and run it before the connection to database is closed. Hope it useful.

Answer 2

In this kind of cases do not do db operations in loops. This is time consuming and not proper useage. Instead, in a loop create select, insert or whatsoever statement and then complate the loop operations and calculations then open the connection just one time and send request (created sql script) to db and get response from db then close connection. Thats all. db operations must be sparated from dailiy and recursive opeations.(antipattern) best regards.

Answer 3

Disclaimer: while this is not a direct solution to the problem described in the post I would like to add this approach as a much faster and easier solution to the problem described.

Step 1: create a (possibly hidden) sheet where you pull all the SQL data that you need in this Excel file. Pull the data into one table with all the necessary columns / dimensions to get the data afterwards from this table.

Here is what I mean by that. Let's assume that you need in this Excel file some data from the table Users on the SQL server as well as some information from a StockMarket table on the server. From the table Users you want the UserID, the first name, last name, and the job title. From the table StockMarket you will need the Stockmarket ID and the price for this particular share. As these prices are by date you also need the the quote date for the price.

Now, since you want all in one table you must think of a smart way to combine all this data into one table. One approach could be the following:

Afterwards you can get all the data from the above table with lookup functions like these:

=INDEX(SQLdata,MATCH(1,(SQLdata[Table]="Users")*(SQLdata[UserID]=25),0),4)

_{Note, that I named the table SQLdata to make it easier when looking at the formula and understanding it. Also, like this you can easily scan your Excel file for any reference to this table.}

Another approach could be the following to make the table more concise:

Note, that this time I am mixing Strings with Numbers and also Strings with Dates (which is very poor design and for some people here even impossible to think of). Also, the column headers are now less descriptive. Yet, this works too:

=INDEX(SQLrev,MATCH(1,(SQLrev[Table]="Users")*(SQLrev[Dimension1]=25),0),5)

_{Note, that I called the table this time SQLrev.}

Both solutions allow you also to aggregate data from the table. So, if you want (for example) the average price for Apple in 2017 then you can use the following formula to sum up the the quotes for this year and divide them by 3:

=SUM(IF("StockMarket"=SQLrev[Table];1;0)*IF("AAPL"=SQLrev[Dimension1];1;0)*SQLdata[Price])/3

The most notable advantage for this approach is that you merely have to update one table in the entire Excel file which means that there is only one SQL pull from the server.

The most notable disadvantage (apart from the SQL select which might get pretty complicated to write) is that you need to know of all the data that needs to reside in this table. If the data is not pulled into this table then none of the above formulas will be able to retrieve these values.

While this approach certainly has its downsides this is much easier to implement than the Excel AddIn you are aiming for.

_{All above formulas are array formulas and must be entered pressing Ctrl + Shift + Enter. For more information on array formulas please read the following: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7}