Using Excel VBA to run SQL query

Question

I am fairly new to SQL and VBA. I have written a SQL query that I would like to be able to call and run from a VBA sub in an excel workbook and then bring the query results into the workbook. I have found some subs online (stackoverflow and other places) that claim to do this but I am having trouble understanding them as they contain no explanation. For example, here is a sub that I found online:

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
    Set rs = conn.Execute("SELECT * FROM Table1;")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

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

End Sub

First of all, would this work? Second, what do I need to replace in the sub (it looks like provider, data source, initial catalog, etc) and where do I find the info to replace them with?

I hope this question is not too confusing and I appreciate your help!

The code has comments above each 'section'. What exactly don't you understand? Essentially the code; creates a connection to the database, executes the query against the connection, loads the results in to ResultSet, copies the ResultSet into a table starting at range "A1"... — T I, Dec 09 '14 at 17:42
If your database has a username/password, then your sConnString needs to have "Password=###;User ID=###". ALso, the sConnString should have "Data Source=IP_ADDRESS_OF_SERVER" and if you're using the same computer for database & VBA, then you can use 127.0.0.1 for the IP_ADDRESS_OF_SERVER. — Michael, Dec 09 '14 at 17:49
What type of SQL database are you using? MS SQL? MySQL? SQLite? This will determine whether or not the VBA code will work. — Michael, Dec 09 '14 at 18:01
Thanks for your response, Michael. When I try to run this it highlights Dim conn As ADODB.Connection and says user-defined type not defined. I googled this and it looks like I need to use a 'type statement'. Do you have any experience with this? What would this look like in this case? — Sam, Dec 09 '14 at 18:21
Secondly, do you have "Microsoft ActiveX Data Objects 2.8 Library" as a reference checked in your VBA? — Michael, Dec 09 '14 at 18:24
"Microsoft Active X Data Objects 2.8 library" is not checked off but I can do so now — Sam, Dec 09 '14 at 18:26
Okay, I think your problem comes from the References I show in the first picture below. Make sure that second from bottom check box is checked (ActiveX) and try to run it. You should be able to get past the "Dim conn AS ADODB.Connection" line. Next, you'll need to setup the typical SERVER, UESRNAME, PASSWORD, and DATABASE in the ConnectionString. Finally, you'll need to create your query. — Michael, Dec 09 '14 at 18:27
Thanks, Michael. I really appreciate your help and time today. I have a few meetings to get to this afternoon but when I try this I will respond with the results! — Sam, Dec 09 '14 at 18:41

score 58 · Answer 1 · edited Dec 19 '16 at 22:05

Below is code that I currently use to pull data from a MS SQL Server 2008 into VBA. You need to make sure you have the proper ADODB reference [VBA Editor->Tools->References] and make sure you have Microsoft ActiveX Data Objects 2.8 Library checked, which is the second from the bottom row that is checked (I'm using Excel 2010 on Windows 7; you might have a slightly different ActiveX version, but it will still begin with Microsoft ActiveX):

References required for SQL

Sub Module for Connecting to MS SQL with Remote Host & Username/Password

Sub Download_Standard_BOM()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
   'Make sure to change:
       '1: PASSWORD
       '2: USERNAME
       '3: REMOTE_IP_ADDRESS
       '4: DATABASE
    ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User ID=USERNAME;Data Source=REMOTE_IP_ADDRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=DATABASE"

    'Opens connection to the database
    cnn.Open ConnectionString
    'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
    cnn.CommandTimeout = 900

    'This is your actual MS SQL query that you need to run; you should check this query first using a more robust SQL editor (such as HeidiSQL) to ensure your query is valid
    StrQuery = "SELECT TOP 10 * FROM tbl_table"

    'Performs the actual query
    rst.Open StrQuery, cnn
    'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
    Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

Thanks a lot for your answer, as I was recently looking for such an implementation. However for curiosity, could you tell us more about the usefulness of `Persist Security Info=True`, `Use Procedure for Prepare=1`, `Auto Translate=True`, `Packet Size=4096`, `Use Encryption for Data=False`, `Tag with column collation when possible=False` within the connexion string? Actually the query does work without those additional information. Best, — owner, Oct 22 '15 at 14:12
On `Persist Security Info=True`, see http://stackoverflow.com/questions/30419627/persist-security-info-property-true-and-persist-security-info-property-false — Andi Mohr, Apr 01 '16 at 16:33
solid example good explanation. at Sam the reason you put in the extra criteria is to have further control over default fixtures in case certain systems need to have the settings tweaked somewhat EDIT: the basic things you need in your connection string are password, username, db name and db ip address — Dexter Whelan, Jan 23 '17 at 11:33
Except this will no longer work on Office 64bit editions. You have to use 64bit OLEDB driver in that case - https://www.microsoft.com/en-au/download/details.aspx?id=20065 — Fandango68, Jun 18 '18 at 03:23
See the following answer for an easy way to build the connection string. It helped me solve an issue with authentication that was costing me a few hours. https://stackoverflow.com/questions/1008047/dsn-to-connectionstring/1008730#1008730 — mvbentes, Dec 07 '21 at 17:41

Using Excel VBA to run SQL query

1 Answers1

Linked