Using Excel Vba to Run SQL Query

Run SQL-statement in Excel Cell with VBA

Option 1:

Splitting the query into parts using CONCATENATE function can delete empty spaces which are needed to run the query correctly.

Therefore, depending on the query instead of =CONCATENATE(C2,C3) you need to use =CONCATENATE(C2," ",C3).

---

Option 2:

If the above solution does not work you can also solve the issue by doing a work-around and copy&paste the CONCATENATE function from Cell C1 into another cell as Values:

Sub Get_Data_from_DWH()

Sheet1.Range("C4").ClearContents
Sheet1.Range("C1").Copy
Sheet1.Range("C4").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
        
    Set conn = New ADODB.Connection
    conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XX.XXX.XXX.XX; DATABASE=bi; UID=testuser; PWD=test; OPTION=3"
    conn.Open
    
    strSQL = Sheet1.Range("C4")
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

    Sheet1.Range("D1").CopyFromRecordset rs
    
    rs.Close
    conn.Close
    
End Sub

---

In the comments it was discussed that there is no limitation on an Excel cell.

For a simple text there is no limitation but the entry of a formula is limited.

In the specific case in the question a formula is necessary because the entry in Cell B1 should be a variable that can be changed by the user anytime.

---

Therefore, I think the only way to

a) seperate the SQL-statement from the executing VBA-Code

b) use a query with more than 255 characters

c) apply entries in an Excel cell as variables

is one of the above solutions.

How do I run a query from Excel VBA

Essentially, you are connecting to MS Access in two different ways: frontend with the Access COM object and backend with ADO. Additionally, you are combining two DB APIs, DAO with CurrentDb and ADO with ADODB.Connection, which both have recordset objects.

Consider either using first connection via the Access COM application and DAO or close the COM object and connect to new database with ADO.

Approach 1: Run all operations with COM connection and DAO

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' INITIALIZE DAO DATABASE
Set dbss = objAccess.CurrentDb

' OPEN DAO RECORDSETS
TMPQueryN = "SELECT * FROM [N]"
TMPQueryO = "SELECT * FROM [O]"
Set TMPRecordsetN = dbss.OpenRecordset(TMPQueryN)
Set TMPRecordsetO = dbss.OpenRecordset(TMPQueryO)

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE DAO OBJECTS
TMPRecordsetN.Close: TMPRecordsetO.Close
Set TMPRecordsetN = Nothing: Set TMPRecordsetO = Nothing: Set dbss = Nothing

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase
objAccess.Quit

Set objAccess = Nothing

Approach 2: Close COM connection without DAO and open ADO connection

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase()
objAccess.Quit()

Set objAccess = Nothing


' CONNECT TO DATABASE VIA ADO -----------------------------------------------------
Dim TMPConnection As ADODB.Connection
Dim TMPRecordsetN As ADODB.Recordset, TMPRecordsetO As ADODB.Recordset
Dim TMPQueryN As String, TMPQueryO As String

' OPEN CONNECTION
Set TMPConnection = New ADODB.Connection
MPConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath
TMPConnection.Open TMPConnectionString

' OPEN ADO RECORDSETS
Set TMPRecordsetN = New ADODB.Recordset
TMPQueryN = "SELECT * FROM [N]"
TMPRecordsetN.Open TMPQueryN, TMPConnection

Set TMPRecordsetO = New ADODB.Recordset
TMPQueryO = "SELECT * FROM [O]"
TMPRecordsetO.Open TMPQueryO, TMPConnection

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE ADO OBJECTS
TMPRecordsetO.Close: TMPRecordsetN.Close: TMPConnection.Close
Set TMPRecordsetO = Nothing: Set TMPRecordsetN = Nothing: Set TMPConnection = Nothing

Performing SQL queries on an Excel Table within a Workbook with VBA Macro

One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:

Sheets("shtName").range("namedRangeName").Address

Which will spit out an address string, something like $A$1:$A$8

Edit:

As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

Which results in a string like =Sheet1!$C$1:$C$4. So for your code example above, your SQL statement could be

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"

Error while trying to run a SQL query with VBA excel

Especially for complex queries, consider separating SQL and VBA and parameterizing the SQL as a prepared statement with qmark placeholders. ADO supports parameterization with the ADO command object which coincidentally you already use! This allows you to avoid any DECLARE and messy, even dangerous concatenation. Also, because of parameterization, purposely use date types and avoid any FORMAT or CONVERT needs. You can also avoid #temp1 with a single statement:

SQL (save as .sql or string in an Excel cell)

Query below uses more informative aliases and uses AS operator for column aliasing. Also, all system commands are consistently capitalized for readability. Please note the use of qmarks (?) for parameters and not @ variables. Please test query and adjust as needed.

SELECT x.[Charge_slabs_A], x.[Slab_weight_Discharged_A],
       x.[AVG_Charg_Temp_A], y.[AVG_DisCharg_Temp_B]
FROM 
   (SELECT COUNT(CASE
                    WHEN h.[FURNACE_NUMBER]=1 
                    THEN h.[slab_weight] 
                    ELSE NULL 
                 END) AS [Charge_slabs_A],
           1000 * AVG(r.[fa_weight]) AS [Slab_weight_Discharged_A],
           AVG(CASE 
                  WHEN m.[Furnace]='A' 
                  THEN m.[charge_temperature]
                  ELSE NULL 
               END) AS [AVG_Charg_Temp_A]
   FROM fix.dbo.Fce_HD_Hourly AS f
   LEFT JOIN ALPHADm.dbo.Mill_Temp_Aims AS m 
        ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[charge_time]), 0) = f.[_TimeStamp]
   LEFT JOIN ALPHADm.dbo.reheats_hourly_data AS r 
        ON r.[start_time]= f.[_timestamp]
   LEFT JOIN alphadm.dbo.HFNCPDI h
        ON  h.[counter] = m.[mill_counter]
   WHERE f.[_TimeStamp] BETWEEN ? AND ?
     AND m.[charge_time] BETWEEN ? AND ?
   GROUP BY f.[_TimeStamp]
  ) AS x 
FULL OUTER JOIN 
  (SELECT AVG(CASE 
                 WHEN m.[FURNACE] ='B' 
                 THEN convert(real,isnull (m.[ave_disch_temp],'0')) 
                 ELSE NULL 
              END) AS [AVG_DisCharg_Temp_B],
          f.[_TimeStamp] AS [Time]
   FROM fix.dbo.Fce_HD_Hourly AS f
   LEFT JOIN Mill_Temp_Aims AS m 
        ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[discharge_time]), 0) = f.[_TimeStamp]
   WHERE f.[_TimeStamp] BETWEEN ? AND ?
     AND m.[discharge_time] BETWEEN ? AND ?
   GROUP BY f.[_TimeStamp]
  ) AS y 
ON y.[Time] = x.[_TimeStamp]

VBA (reads in above query and binds date parameters)

Private Sub UpdateButton_Click()
    Dim oCon As ADODB.Connection, oCmd As ADODB.Command
    Dim rs As Object, SQL_1 As String
    Dim WS As Worksheet, n As Long
    
'GET DATES
    Dim StartDate As Date, EndDate As Date
    With ThisWorkbook.Sheets("A&B Sankey")
        StartDate = CDate(.Range("R2").Value)
        EndDate = CDate(.Range("T2").Value)
    End With
    
'CONNECT FUNCTION
    Set oCon = DbConnect
    Set oCmd = CreateObject("ADODB.Command")
    oCmd.CommandTimeout = 0
    oCmd.ActiveConnection = oCon
    
'READ IN SQL
   With CreateObject("Scripting.FileSystemObject")
       SQL_1 = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
   End With
   ' SQL_1 = ThisWorkbook.Sheets("MySQLSheet").Range("A1")

'EXECUTE RESULT
   With oCmd
       .CommandText = SQL_1
                
       ' BIND ? PARAMETERS IN SQL (USING adDate TYPES)
       For n = 1 to 4
           .Parameters.Append .CreateParameter("startdateparam" & n, adDate, adParamInput, , StartDate)
           .Parameters.Append .CreateParameter("enddateparam" & n, adDate, adParamInput, , EndDate)
       Next n

       ' CREATE RECORDSET
       Set rs = .Execute
    End With
    
'SHOW RESULT
    With ThisWorkbook.Sheets("-Input Data-")
        .Range("B20:CC20000").ClearContents
        .Range("B20").CopyFromRecordset rs
    End With

'CLOSE
    MsgBox "Result written to " & WS.Name & _
           "For " & StartDate & "-" & EndDate, vbInformation, "Finished"
    rs.Close: oCon.Close
    Set rs = Nothing: Set oCmd = Nothing: Set oCon = Nothing
End Sub

Function DbConnect() As ADODB.Connection
    Dim sConn As String
    sConn = "Driver={SQL Server}; SERVER=; " & _
            "UID=; PWD=; DATABASE=;"
    Set DbConnect = CreateObject("ADODB.Connection")
    DbConnect.Open sConn
End Function

---

Related Topics