Functions to convert SQL into VBA strings

Question

I construct hundreds of SQL Queries in an excel sheet and each one is placed in a cell of 1 column. What I am looking to do is run each of these SQL statements from excel.

Just wondering if anyone knows a way to convert all my SQL into VBA Strings to that I can loop through all rows to run each query.

I found this which is what I want to do but is there a way I can alter the code so it can read off excel cells rather than a Form?

http://allenbrowne.com/ser-71.html

Thanks

EDIT: Here is a sample SQL that I am trying to convert

SELECT 
TT.TEST_TABLE_ID,
TT.TEST_TABLE_NO,
TT.MEMBERSHIP_NUMBER,
TT.TEST_TABLE_TYPE,
from TEST_TABLE TT

I think because each Select is in its own line it causes problems when it converts.

EDIT #2: Here is my code that executes SQL

Sub GetData()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
    Set Data = Sheets("Results")
    Data.Select
    Cells.ClearContents
    Conn.Open "PROVIDER=ORAOLEDB.ORACLE;DATA SOURCE=ORCL;USER ID=user;PASSWORD=password"
    cmd.ActiveConnection = Conn
    cmd.CommandType = adCmdText
    'sqlText = How to reference Valid SQL cells
    cmd.CommandText = sqlText
    Set RS = cmd.Execute
    For X = 1 To RS.Fields.Count
        Data.Cells(1, X) = RS.Fields(X - 1).Name
    Next

    If RS.RecordCount < Rows.Count Then
        Data.Range("A2").CopyFromRecordset RS
    Else
        Do While Not RS.EOF
           Row = Row + 1
           For Findex = 0 To RS.Fields.Count - 1
             If Row >= Rows.Count - 50 Then
                Exit For
             End If
             Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
           Next Findex
           RS.MoveNext
        Loop
    End If
    Cells.EntireColumn.AutoFit
End Sub

in the SQL text part I want to be able to reference my column of SQL statements that I have. I thought I needed to convert it but you guys are right that if referencing it I can Just use your code Brad.

I tried to incorporate your code brad where my 'sqlText = How to reference Valid SQL cells is but had no success

Answer 1

Here is a start to the code I think you need.

I have placed the SQL in a sheet named "SQL", in Col A. The issues with this are: (1) You are placing field names in a row, then the data that is returned into a row. That will require two rows per SQL statement. (2) I copied the SQL statement from sheet "SQL' and placed in Col A of "Results" (you mentioned you wanted to place results to right of SQL String. (3) You clear the contents of "Results" sheet, so you need to be careful not to erase your SQL if you decide to combine sheets.

Option Explicit

Sub Process_SQL_Strings()
Dim cmd         As New ADODB.Command
Dim sqlText     As String
Dim Row         As Long
Dim Findex      As Long
Dim Data        As Worksheet
Dim iFldCt      As Long
Dim conn        As ADODB.Connection
Dim rs          As ADODB.Recordset
Dim sConn       As String
Dim lLastRow    As Long
Dim lRow        As Long

    Set Data = Sheets("Results")
    Data.Select
    Cells.ClearContents
    conn.Open "PROVIDER=ORAOLEDB.ORACLE;DATA SOURCE=ORCL;USER ID=user;PASSWORD=password"
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText

''        Set conn = New ADODB.Connection
''        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
''                                 "Data Source=C:\data\access\tek_tips.accdb;" & _
''                                 "Jet OLEDB:Engine Type=5;" & _
''                                 "Persist Security Info=False;"
    conn.Open sConn

    'sqlText = How to reference Valid SQL cells
    lRow = 1
    Do
        sqlText = Sheets("SQL").Range("A" & lRow)
        If sqlText = "" Then
            MsgBox "Finished processing " & lRow & " rows of SQL", vbOKOnly, "Finished"
            GoTo Wrap_Up
        End If

        Set rs = New ADODB.Recordset
        rs.Open sqlText, conn, adOpenStatic, adLockBatchOptimistic, adCmdText

        Data.Cells(lRow, 1) = sqlText

       If not rs.EOF then
        For iFldCt = 1 To rs.Fields.Count
            Data.Cells(lRow, 1 + iFldCt) = rs.Fields(iFldCt - 1).Name
        Next

        If rs.RecordCount < Rows.Count Then
            Data.Range("B" & lRow).CopyFromRecordset rs
        Else
            Do While Not rs.EOF
               Row = Row + 1
               For Findex = 0 To rs.Fields.Count - 1
                 If Row >= Rows.Count - 50 Then
                    Exit For
                 End If
                 Data.Cells(Row + 1, Findex + 1) = rs.Fields(Findex).value
               Next Findex
               rs.MoveNext
            Loop
        End If
        Cells.EntireColumn.AutoFit
      End If
        lRow = lRow + 1
    Loop
Wrap_Up:
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

Answer 2

You'll need to create a connection to your database and loop through all the cells and execute your code in each cell.

You can use ADO to to make the connection (need to add a reference to Microsoft ActiveX Data Objects 6.1 Library)

You'll need to figure out your connection string, open a connection, then loop through all the cells and execute the SQL in those cells.

Dim cnn As New ADODB.Connection
Dim connectionString As String
Dim cmd As New ADODB.Command
Dim c As Range, ws As Worksheet
Dim rst as ADODB.Recordset

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\Database3.accdb;Persist Security Info=False;"
cnn.Open connectionString
cmd.ActiveConnection = cnn

For Each c In ws.Range()
    cmd.CommandText = c.Value
    set rst = cmd.Execute 
    'do what you need to with your new recordset before moving on to the next SELECT
Next c

Answer 3

I am using something this:

Function SQLQueryRun(ByVal query As String, ByVal returnData As Boolean) As Variant
Dim Conn As New ADODB.Connection
Dim ADODBCmd As New ADODB.Command
Dim ret As New ADODB.Recordset

    Conn.ConnectionString = "connection_string_here"
    Conn.Open
    ADODBCmd.ActiveConnection = Conn
    ADODBCmd.CommandText = query
    Set ret = ADODBCmd.Execute()
    If returnData Then
        If Not ret.EOF Then SQLQueryRun = ret.GetRows()
    Else
        SQLQueryRun = True
    End If
    Conn.Close
    Set Conn = Nothing
    Set ret = Nothing
End Function

If the second argument is False nothing is returned by function. Are you expecting results from query run?
Also I use a macro to create Query/Pivot table from sql contained in windows clipboard, if you are interested let me know.