Passing SQL code to server and retrieve results

[MojoJojo2023]

Hello all,

I use a simple report daily that is copy and pasted into an excel worksheet where it is analyzed. It can be quite a pain to get into the system for this report everyday, so I would like to take the SQL code and return the results directly into my worksheet using VBA. I have used ADODB connections to query the server before, but have not attempted anything like this before. The code I am trying to pass is being shown to me similar to the below:

Create #DealTable (
[DealName] varchars(45)
,[DealNumber] varchars(45)
,[Location] varchars (45),
)

Insert into #DealTable execute dbo.DealTrack @IDLog

Select
[#DealTable].[DealName] [DealName]
,[#DealTable].[DealNumber] [DealNumber]
,[#DealTable].[Location] [Location]

From [#DealTable] (NoLock)

'N, @IDLog = 1



I understand that this code is 1) Creating a table, 2) Filling the table based on the output of a stored procedure, 3) querying the temporary table, and 4) passing the selected parameter to the stored procedure. Should I pass it all through under one ADODB Recordset? Should I break into three distinct parts (ADODB.Recordset, ADODB.Command, then Recordset again)? OR is there a better alternative?

I love VBA and am really trying to refine my approach, so any help would be greatly appreciated!

 

[Cubist]

See if this thread helps.

How to connect Excel to an RDBMS Like SQL Server

Is it possible in Excel to connect to a SQL Server DB Instance to pull data via a query?

www.mrexcel.com

 

[Candyman8019]

I use something like this for SQL queries. This should do the trick whether a recordset or a command. My sheet has fields for data source, initial catalog and security type, but you could simply add that info directly into the connection string in the code. Similar with the SQL query, I enter it into cell A9...so it can be easily modified if need be.

Private Sub RunSQLUpdate()
Dim constr As String
Dim conn As Object
Dim strSQL As String
Dim rs As Object

'clear last results
Sheet10.Range("A12:ZZ1048000").ClearContents
On Error Resume Next

'Confirm the user wants to proceed with a DB update.
If InStr(1, LCase(Sheet10.Range("A9").Value), "update") > 0 Then
    If MsgBox("Are you sure you want to update the database?", vbYesNo) = vbNo Then Exit Sub
End If

    ' OPEN CONNECTION '
    constr = "provider=sqloledb;Data Source=" & Sheet10.Range("DataSource").Value & ";initial catalog=" & Sheet10.Range("InitCat").Value & ";" & Sheet10.Range("DBSecurity").Value
      
    strSQL = Sheet10.Range("SQLStmt").Value
      
    Set conn = CreateObject("ADODB.Connection")
    conn.Open constr

    ' EXECUTE QUERY '
    'conn.Execute strSQL

    'Print result starting in cell A12
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.ActiveConnection = conn
    rs.Open strSQL
      Sheet10.Range("A12").CopyFromRecordset rs

    ' CLOSE CONNECTION '
    conn.Close
    rs.Close
    Set conn = Nothing

    MsgBox "Transaction complete!", vbInformation
End Sub