Replace SQL command text in data connection in Excel

Question

Is there a way to replace the SQL command text in the data connection in Excel via VBA?

For example, I have a data connection named "Accounts" and it has a simple query in the command text that states

Select a.* 
from Recon a 
where a.recon in 
('12345','67890')

I have the SQL statement built in a sheet1 (cell A2 through cell A5) that allows users to enter different "recon" numbers. Instead of them having to copy and paste this into the command text in the data connection. I want to be able to have a macro that copies and paste the SQL from cell A2 through cell A5 into the command text in the data connection and then refresh the data connection which is connected to Table1.

What have you tried so far, this is simple to achieve from the listobject/querytable properties. — Nathan_Sav
– Nathan_Sav, Commented Aug 15, 2017 at 15:51
This is what I tried so far. Sub RefreshData() ActiveWorkbook.Connections ("Recon") .OLEDBConnection.CommandText = Worksheets("Sheet1").Range("Recon_SQL") ActiveWorkbook.Connections("Recon").Refresh End Sub — AlmostThere
– AlmostThere, Commented Aug 15, 2017 at 16:01
Important: consider what happens when Little Bobby Tables plays with your spreadsheet - xkcd.com/327 — Mathieu Guindon
– Mathieu Guindon, Commented Aug 15, 2017 at 16:07
the idea is to have the tab that contains the SQL to be hidden and password protected, so that the SQL does not get manipulated(yes, users who are savvy enough can go into the data connection and change it, but its not of concern). Also the database being pinged asks for the users log-in information, so if something were to happen the DBA could see who did it. — AlmostThere
– AlmostThere, Commented Aug 15, 2017 at 16:13
If your connection string has a password, you're using SQL Authentication, which as far as the DBA is concerned means anything and whatever could have connected to the database. Use Windows Authentication if the DBA needs to be able to see who did it. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 15, 2017 at 16:23

FunThomas · Accepted Answer · 2017-08-15 16:01:47Z

2

Depending on the connectiontype, you can change the command text using

wb.Connections(1).OLEDBConnection.CommandText = ...

or

wb.Connections(1).ODBCConnection.CommandText = ...

(with wb being the workbook you are working on).

However: Be aware about SQL Injection!

answered Aug 15, 2017 at 16:01

FunThomas

31k4 gold badges24 silver badges40 bronze badges

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

1 Comment

AlmostThere Over a year ago

How do I reference the specific connection of "Recon"? I tried

Sub RefreshData() ActiveWorkbook.Connections ("Recon") .OLEDBConnection.CommandText = Worksheets("Sheet1").Range("Recon_SQL") ActiveWorkbook.Connections("Recon").Refresh End Sub

AlmostThere · Accepted Answer · 2017-08-15 17:47:23Z

I originally started with this code which can be seen in some of my comments

Sub RefreshData() ActiveWorkbook.Connections
("Recon") .OLEDBConnection.CommandText = Worksheets("Sheet1").Range("Recon_SQL")
 ActiveWorkbook.Connections("Recon").Refresh End Sub

I then through trial and error I arrived at the following. I created my SQL string text with another macro and then called that string for my command text.

 With ActiveWorkbook.Connections("Recon").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = strsql 'SQL string created by recon_sql_string_creation macro

        End With

        ActiveWorkbook.Connections("Recon").Refresh
End Sub

Collectives™ on Stack Overflow

Replace SQL command text in data connection in Excel

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related