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

If you can copy the SQL from one of your cells and run it in a query tool, there is no need for any additional formatting, just read the SQL from the cell and execute it directly in your VBA. If you think there is some additional formatting required, you should explain exactly what it is, and provide examples. The link you posted is about including SQL in VBA source code, and is not required just to execute that SQL. From your current post, @Brad's answer already provides a solution. — Tim Williams
– Tim Williams, Commented Apr 3, 2014 at 16:05
Yes sorry thats where I was not clear. I have a Valid SQL statement, and I am trying to execute this SQL statement in VBA Source code which requires formatting changes — scrayon
– scrayon, Commented Apr 3, 2014 at 16:08
Yes, I am expecting to have the results returned to me. No inserts/updates/deletes its more of a verification to see if the data exists so just selects — scrayon
– scrayon, Commented Apr 3, 2014 at 16:26

Wayne G. Dunn · Accepted Answer · 2014-04-03 19:30:35Z

2

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

edited Apr 3, 2014 at 19:30

answered Apr 3, 2014 at 14:41

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

23 Comments

scrayon Over a year ago

doesn't look like any of the strSql = "strSql = """ & strSql & """" got added, the double of quotes got added when looking at debug

scrayon Over a year ago

might be something with Replace(strSql, vbCrLf, strcLineEnd)?

Wayne G. Dunn Over a year ago

Are you trying to take a VALID SQL string and convert to a 'string' that can be executed? Or are you trying to parse a SQL string into multiple lines of VBA code with continuation lines? I took string (ignore the |) |SELECT Table1.*, Table1.FldA FROM Table1 WHERE (((Table1.FldA)="A1"));| and it converted to |strSql = "SELECT Table1.*, Table1.FldA FROM Table1 WHERE (((Table1.FldA)=""A1""));"|

scrayon Over a year ago

Yes - I am trying to take a valid SQL string and convert it to a string that can be executed via VBA..sorry for the confusion

Wayne G. Dunn Over a year ago

To insure ONLY 1 row returned "Select Top 1 ...". To test for more than 1, after opening RS: | RS.MoveLast | RS.MoveFirst | Debug.Print RS.Recordcount |

|

avb · Accepted Answer · 2014-04-03 14:29:40Z

0

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.

answered Apr 3, 2014 at 14:29

avb

1,8821 gold badge14 silver badges25 bronze badges

13 Comments

scrayon Over a year ago

Thanks but my main issue is the conversion of my SQL Select to VBA, ive already been able to run queries off of my DB via VBA Im just trying to get it in the right VBA notation

avb Over a year ago

What do you exactly mean by conversion SQL to VBA ?

Brad Over a year ago

if you have SQL there is no conversion necessary. the queries are not "running in VBA". VBA is just facilityating the execution of that SQL somewhere else. SQL is SQL.

scrayon Over a year ago

Sorry maybe conversion isnt the right word but in order to run the query on VBA, it needs to be formatted in a certain way. If you go to my link provide you'll see he needs to change the string inorder to run on VBA

avb Over a year ago

Are your queries contained in one Excel row?

|

Brad · Accepted Answer · 2014-04-03 16:43:58Z

0

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

edited Apr 3, 2014 at 16:43

answered Apr 3, 2014 at 14:30

Brad

12.4k4 gold badges51 silver badges82 bronze badges

3 Comments

scrayon Over a year ago

Thanks but my main issue is the conversion of my SQL Select to VBA

Brad Over a year ago

@JohnJones I updated to include returning a recordset

scrayon Over a year ago

Hey Bard - Let me update my question to include my code so far.

Collectives™ on Stack Overflow

Functions to convert SQL into VBA strings

3 Answers 3

23 Comments

13 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

23 Comments

13 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related