Execute SQL written in a textbox with VBA

Question

With reference to this question: Avoid new line seperators in mySQL query within VBA code, I wanted to execude a SQL statement that is written in a textbox in the Excel-File.
Therefore, I created a textbox called SqlQuery1 looking like this:

In the VBA I refered to the textbox within the SqlString:

Sub Get_Data_from_DWH ()

    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
    
    SqlString = ThisWorkbook.Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text
                            
    Set rs = New ADODB.Recordset
    rs.Open strSQL, conn, adOpenStatic

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

However, I get runtime error 438 on the SqlString.
Do you have any idea what I need to change to make it work?

Divide problematic code string to separate steps. SET wbk = ThisWorkbook: SET sh = wbk.Sheet1: SET shp = sh.Shapes("SqlQuery1"): SET of = shp.OLEFormat: SET obj = of.Object: SET SqlString = obj.Text - what statement fails? — Akina
– Akina, Commented Sep 11, 2020 at 11:25
'MySQL ODBC 5.1 Driver' is quite outdated. If MySQL Server is 5.6 or newer use 'MySQL ODBC 8.0 Driver' as 8.0.21 contains several bug fixes e.g on parameters. If older MySQL Server use 5.3.14 (supports Server 4.1 to 5.7) — ComputerVersteher
– ComputerVersteher, Commented Sep 11, 2020 at 12:11

jamheadart · Accepted Answer · 2020-09-11 11:25:33Z

3

Thisworkbook.Sheet1 is not a valid object path, try instead:

SqlString = ThisWorkbook.Sheets("Sheet1").Shapes("SqlQuery1").OLEFormat.Object.Text

Or just

SqlString = Sheet1.Shapes("SqlQuery1").OLEFormat.Object.Text

And make sure the sheet is definitely named "Sheet1"

Also, you need to change

rs.Open strSQL, conn, adOpenStatic

to this:

rs.Open SqlString, conn, adOpenStatic

And you should probably use

Dim SqlString as String

at the start of the routine

answered Sep 11, 2020 at 11:25

jamheadart

5,3737 gold badges37 silver badges87 bronze badges

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

Comments

Havard Kleven · Accepted Answer · 2020-09-11 11:38:44Z

You're definitely on the right track here and I can see that you've tried solving this error! :-)

Your issue is with the rs.Open part, as far as I can see. I've shuffled your code around a bit. As far as I can see, you did not add the ADODB.Command part. I've added this in the code snippet below.

A word of advice to save time in larger modules; declare your connection as a private global string at the beginning, so you can access it later on in the module.

Private Const CONNECTION As String = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=XX.XXX.XXX.XX; DATABASE=bi; UID=testuser; PWD=test; OPTION=3"
    
Sub Get_Data_from_DWH()


    Dim cmd As ADODB.Command
    Dim conn As ADODB.CONNECTION
    Dim rs As ADODB.Recordset

    
    Set conn = New ADODB.CONNECTION
    conn.Open CONNECTION
    conn.CommandTimeout = 900
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    
    
    If Not conn.State = ADODB.adStateOpen Then GoTo Bugcatcher
    
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CONNECTION
    cmd.CommandText = ws.Shapes("TextBox 2").OLEFormat.Object.Text
    cmd.CommandType = adCmdText
    
    Set rs = cmd.Execute
    
        
    ws.Range("A1").CopyFromRecordset rs
        
    
        
Bugcatcher:
        Exit Sub
        
    
End Sub

Collectives™ on Stack Overflow

Execute SQL written in a textbox with VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related