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]

I am going to try and fiddle around with the code you gave me to see if I can get it to work. Here is the DB that I am connecting to http://examples.oreilly.com/9780735666054-files/9780735666054_files.zip. And the server I was using is just .\SQLEXPRESS because I don't have the full version of SQL Server installed on my laptop. The DB that I am using (TSQL2012) is just a test DB that I use when creating it for other databases.

 

[Jerry Sullivan]

Hi Mitchell, I installed SQL Express and the TSQL2012 test DB so I could emulate your setup.

It took some tweaking to my earlier attempt, but this code works on my system.....

Sub ParameterQueryExample()
'---creates a ListObject-QueryTable on Sheet1 that uses the value in 
'        Cell Z1 as the ProductID Parameter for an SQL Query
'        Once created, the query will refresh upon changes to Z1. 

    Dim sSQL As String
    Dim qt As QueryTable
    Dim rDest As Range

       
    '--build connection string-must use ODBC to allow parameters
    Const sConnect = "ODBC;" & _
        "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")
    rDest.CurrentRegion.Clear  'optional- delete existing table

    
    Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
        Source:=Array(sConnect), Destination:=rDest).QueryTable

   
   '--add Parameter to QueryTable-use Cell Z1 as parameter
    With qt.Parameters.Add("ProductID", xlParamTypeVarChar)
        .SetParam xlRange, Sheets("Sheet1").Range("Z1")
        .RefreshOnChange = True
    End With

   
    '--populate QueryTable
    With qt
        .CommandText = sSQL
        .CommandType = xlCmdSql
        .AdjustColumnWidth = True  'add any other table properties here
        .BackgroundQuery = False
        .Refresh
    End With

       
    Set qt = Nothing
    Set rDest = Nothing
End Sub

 

[Mitchell5656]

I've been trying to run this code and I keep this error:

Run-time error '1004':

General ODBC error


It says the .refresh part in

with qt
.CommandText = sSQL
....
End with

is not working. I'm going to keep working on trying to solve this problem, but please let me know if you have any idea why it is not working properly.

 

[Jerry Sullivan]

I don't know why that's working differently for you.

Try modifying the code to do a static query instead of a parameter query.
Learning whether that works will help narrow down the cause of the problem.

    '--build SQL statement
    sSQL = "SELECT *" & _
            " FROM TSQL2012.Production.Products Products" & _
            [B][COLOR="#0000CD"]" WHERE Products.productid = 1[/COLOR][/B];"

    
    '--create ListObject and get QueryTable
    Set rDest = Sheets("Sheet1").Range("A1")
    rDest.CurrentRegion.Clear  'optional- delete existing table

    
    Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
        Source:=Array(sConnect), Destination:=rDest).QueryTable

   
   '--add Parameter to QueryTable-use Cell Z1 as parameter
[B][COLOR="#006400"]' temporarily comment out this block of code
  '  With qt.Parameters.Add("ProductID", xlParamTypeVarChar)
  '      .SetParam xlRange, Sheets("Sheet1").Range("Z1")
  '      .RefreshOnChange = True
  '  End With

[/COLOR][/B]

 

[Mitchell5656]

I just ran it, but I still got an error at the .Refresh section

 

[Mitchell5656]

So what exactly is the :

With qt
.CommandText = sSQL
.CommandType = xlCmdSql
.AdjustCoulumnWidth = True
.BackgroundQuery = False
.Refresh
End with

section supposed to do?

 

[Mitchell5656]

Scratch that last question section, I was tired and not paying attention. I was wondering if there was a way to also be able to change the column which you are querying the value for. Like how you take a value from the productid column, but instead take it so you can choose which column you would like to query. Is there an easy way to do this?

 

[Jerry Sullivan]

Were you able to get the VBA code to work for a parameter search?
If so, please post the modified version if you made any changes.

Please provide an example of how you would want that query to work in which you choose another column.
We should be able to modify the query to use different parameters or different field, but I don't completely understand your description.

 

[Mitchell5656]

Yes I did end up getting my VBA code to work. The only difference was that I have SQL Server Native Client 11.0 instead of 10.0. So for example in cell Z1 you had the code take a value from the product id column and query it based on that value. What I was curious was to see if say Z1 you entered in the column you wanted to query, so instead of productId you wanted to use categoryId you could type that instead, and in cell Z2 you would type in the value for categoryId. So this way you could query based off of different columns without having to modify the VBA code each time for a different result

 

[Jerry Sullivan]

It doesn't appear that you can use a parameter for a field name the same way we used it for a value.

An alternative would be to use VBA to read the values in Z1 and Z2 and replace the existing query with a new query using those parameters. Would that work for you?