Dynamic Query of a SQL table in Excel

[Mitchell5656]

So I've been stuck working on this problem for the last several days, and would really like a solution to it.
What I am doing is going into the data tab in excel and getting external data from my SQL server (I will provide a link to my database). And I select the Products table, and now have the table showing in my spreadsheet. Now what I am trying to do is open the connections tab, and click on the table that I am connected to, and go to the table properties and definition tab. Under Command Type I change that to SQL. In the Command Text I want to enter a dynamic query. What this dynamic query does is in Sheet2 cell a1 you are supposed enter a category and in cell a2 you enter some property for that category. So for example with the data I have you enter categoryid for the category and for the property you enter 1 and then sheet 1 is queried so it shows the productid, productname, supplierid, categoryid, unitprice, and discontinued for all products with categoryid 1.
I have written up something that I want to enter into the Command Text, but it is not working: <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">SELECT * FROM "TSQL2012"."Production"."Products" WHERE [sheet2$a1] = [sheet2$a2];</code>
database: File
Unzip it, and it is the TSQL2012 DB
If there is anything else that you need or have questions about, please post. This is the way I need to solve this problem so unfortunately other solutions will not be helpful.

 

[Mitchell5656]

**bump**

 

[Jerry Sullivan]

This article might help...
Customize a parameter query - Excel - Office.com

If you place "?" characters in the query text, you'll be prompted to define the source of each parameter.
One method of defining the source is to reference a cell.

For example....

SELECT Persons.Day, Persons.FirstName, Persons.LastName, Persons.P_Id
FROM Persons Persons
WHERE (Persons.FirstName=?)

Then define Parameter1 to be:
=Sheet1!$Z$1

 

[Mitchell5656]

Where do you define Parameter1? Or how would you define it?

 

[Mitchell5656]

When I ask how do you define it, I want to know how you define it in VBA.

 

[Jerry Sullivan]

Are you using VBA to create the connection and the query, or do you already have a connection and query and you want to modify it from having a constant reference to having a parameter reference?

 

[Mitchell5656]

I want to create the connection and the query. From there I do want to be able to have a parameter reference.

 

[Jerry Sullivan]

This article gives an example using ADO to create a query using a parameter.

How To Invoke a Parameterized ADO Query Using VBA/C++/Java

If you'd like help adapting that for your application, please post the Connection String and SQL Command Text that result from your manually created query (using a constant instead of a parameter).

 

[Mitchell5656]

SELECT *
FROM TSQL2012.Production.Products Products
WHERE (Products.productid=?)

Sub Macro1()
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.\SQLEXPRESS;Use Procedure for Prepare=1;Aut" _
        , _
        "o Translate=True;Packet Size=4096;Workstation ID=MITCHELLWALKER;Use Encryption for Data=False;Tag with column collation when pos" _
        , "sible=False;Initial Catalog=TSQL2012"), Destination:=Range("$A$1")). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("""TSQL2012"".""Production"".""Products""")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Users\Mitchell Walker\Documents\My Data Sources\._SQLEXPRESS TSQL2012 Products.odc"
        .ListObject.DisplayName = "Table__._SQLEXPRESS_TSQL2012_Products"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Not sure where the connection string is there, but this is what I have. Thank you for your help.

 

[Jerry Sullivan]

This turned out to be more challenging than I expected.

Below is some code you can try. Because I'm not able to test this on your server setup, it's likely this will require some modifications to get it to work.

Sub ParameterQuery()
    Dim sSQL As String
    Dim qt As QueryTable
    Dim rDest As Range

    
    '--build connection string-must use ODBC to allow parameters
    Const sConnect As String = _
        "Driver={SQL Server Native Client 10.0};" & _
        "Server=.\SQLEXPRESS;" & _
        "Database=TSQL2012;" & _
        "Trusted_Connection=yes;"

        
    '--build SQL statement
    sSQL = "SELECT *" & _
            " FROM TSQL2012.Production.Products Products" & _
            " WHERE Products.productid = ?;"

    
    '--create ListObject and get QueryTable
    Set rDest = Sheets("Sheet1").Range("A1")
    Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
        Source:=sConnect, Destination:=rDest).QueryTable

        
   '--add Parameter to QueryTable and populate with Query
    With qt
        .Parameters.Add Sheets("Sheet1").Range("Z1"), xlRange
        .CommandText = sSQL
        .CommandType = xlCmdSql
        .Refresh BackgroundQuery:=False
        .Refresh
    End With

    
    Set qt = Nothing
    Set rDest = Nothing
End Sub

Some obstacles in getting this to work that might not be solved in this code...

1. Getting an ODBC connection string that works for your server. You're currently using an OLEDB provider to connect. An ODBC string must be used if the query will have parameter(s).

2. When the Query is refreshed, it seems to want pop up the "Enter Parameter Value" input box regardless of whether the parameter has already been defined.