Excel VBA Parameterized Query

so, you don't want it to create a new connection each time? if that's the case, use query refresh instead.

Thanks for the answer but I still get a new connection created each time... here's the code

PracticeAbbr = Worksheets("Parms").Range("B6")
'
'
' Extract client division information
'
sConn = "ODBC;DSN=CMS2;Description=CMS2;APP=Microsoft Office 2010;DATABASE=cms_data;"
'
sSql = "SELECT Division.DivisionAbbr, Division.FeeSchedUpdate, Division.projDate, Practice.PracticeAbbr, Practice.PracticeName, Practice.FiscalYearEnd" & Chr(13) & "" & Chr(10)
sSql = sSql & "FROM cms_data.dbo.Division Division, cms_data.dbo.Practice Practice" & Chr(13) & "" & Chr(10)
sSql = sSql & "WHERE Division.PracticeID = Practice.PracticeID AND Practice.PracticeAbbr='" & PracticeAbbr & "'"
sSql = sSql & "ORDER BY Division.DivisionAbbr"
'
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)
'
oQt.Refresh

DUH

QueryRefresh is not the same as Refresh....

that fixed it,
thanks

Don't add a new connection. use this code instead

How did you make it so it doesn't add a new set of columns every time the marcro runs?

I have a requirement of getting data from a MS SQL 2005 database based on some parameters on Sheet2

The initial creation of QueryTable is easy and is similar to what was posted by Wolf, but on consequent events it keeps adding columns to the right, in addition to creating more connections. I cannot just refresh the existing QueryTable, since the underlying selection criteria has to be modified, but I'm lost at how to do this. Here's my code:

I've tried putting code before adding a QueryTable to delete existing ones, which still adds data to the right of the existing data and doesn't replace it. I've tried modifying the .Recordset property, but that throws a run-time error.

Not sure how Query Refresh would accomplish this task.

Any help would be greatly appreciated.

you gotta change the properties of the query. there's an option to overwrite data. use that one.

My understanding was that the following property did just that:
.RefreshStyle = xlInsertDeleteCells

According to MSDN, "Partial rows are inserted or deleted to match the exact number of rows required for the new recordset."

I've rarely (or never) seen anyone using a recordset like this with a query table. It's interesting but may have limitations. In any case, since you are adding a new QueryTable you should delete the old one, and all its data.

Other options:

1)
Since you are using an ADO recordset, you could just delete the existing data and use the copyfromrecordset method to write the new data in (more here: http://www.erlandsendata.no/english/index.php?t=envbadac)

2)
Or, forget the recordset. Use a "normal" query table. edit the command text and then refresh it. (I don't use querytables a lot so I don't know offhand of a good link - I know there are some good pages for more info on this though).

I've come across the linking of recordset to the querytable in one of the forums, I believe in one of the MSDN ones, and the suggestion was made by a MS tech who said it was something along the lines of being "more efficient" and would allow to change the selection criteria by re-composing the SQL statement based on whichever conditions, instead of trying to use the Paramters collection and ? substitutions, especially since I may not always need the same parameters or have them apply to the same columns. The end result would still be the same columns, so the layout of the output would not be changing.

Since that was my exact requirement, I went with the recordset option, but it doesn't seem to be working.

My big is why I'd need to do a .Add every time the data is refreshed. I'd assume that .Add should be only necessary on the first ever execution of the creation of connection/recordset/qt, but then the Macro should simply check if the qt/connection already exists. Update the rs with the new SQL statement, based on whatever criteria, then update the qt.recordset and finally do the .Refresh on the qt, removing extra old rows automagically (and without adding new columns, naturally). However, I cannot seem to find any examples of this. Everywhere I look there's the .Add/.Delete combination, which does not seem to be the most efficient way.

So I guess, given the requirements, what WOULD be the suggested/common/efficient way to accomplish this: rs; clear data, qt.Add, qt.Delete; something else?

Thank you,
Sergey