Excel control database via VBA
I am trying to create an excel addin that has a set of functions to output a value from a database (I am using MS SQL Server). So my query will only return one set of records. I am using something like below in my vba code.
Using Excel VBA to Execute SQL Query
But the problem is, if I have a custom function in 100 cells, the macro communicates with the DB every time and fetches data from the DB.
Is there a way that I can make one connection and use that connection to record as many requests as I want?
source to share
Simple, run all 100 functions / loops to access the database. Then you will complete the connection. Take a 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 add 100 loops and run them before closing the database connection. Hope this is helpful.
source to share
In such cases, no db operations are performed in loops. This is time consuming and not properly used. Instead, in a loop, create a select, insert or whatever statement, and then complicate the loop and calculation operations, then open the connection just once and send the query (generated by the sql script) to db and get the response from db, then close the connection. It's all. db operations have to be split from dailiy and recursive opeations. (antipattern) Best wishes.
source to share
Disclaimer: While this is not a direct solution to the problem described in the post, I would like to add this approach to a much faster and simpler solution to the problem described.
Step 1: create a (possibly hidden) sheet where you pull out all the SQL data you need in that Excel file. Pull data into table one with all required columns / dimensions to get data after that from that table.
This is what I mean. Suppose you need in this Excel file some data from a table Users
on the SQL server and also some information from a table StockMarket
on the server. The Users
user ID, first name, last name, and job title are required from the table . From the table, StockMarket
you will need the Stockmarket ID and the price for that particular stock. Since these prices are by date, you also need a quote date for the price.
Now, since you want everything in one table, you should think of a clever way to combine all this data into one table. One approach might be as follows:
Then you can get all the data from the above table using search 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 to see and understand. Moreover, you can easily scan the Excel file for any link to this table.
Another approach might be to make the table more concise:
Note that this time I am mixing Strings
with Numbers
as well as Strings
with Date
(which is very bad design, and for some people it is even impossible to think here). In addition, the column headings are now less descriptive. However, this works too:
=INDEX(SQLrev,MATCH(1,(SQLrev[Table]="Users")*(SQLrev[Dimension1]=25),0),5)
Note that I was calling the SQLrev table this time.
Both solutions also allow you to collect data from a table. So, if you want (for example) the average price for Apple in 2017, then you can use the following formula to sum 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 benefit to this approach is that you just need to update one table in the entire Excel file, which means there is only one on the server .
The most notable disadvantage (besides the choice of SQL, which can get quite difficult to write) is that you need to know all the data that needs to be in that table. If the data is not pulled into this table, none of the formulas above will be able to extract those values.
While this approach certainly has its drawbacks, it is much easier to implement than the Excel AddIn you are aiming for.
All of the above formulas are array formulas and must be entered by 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
source to share