Excel VBA Parameterized Query

[KG Old Wolf]

I've created a VBA-based query that uses a worksheet cell value as an input parameter. The query uses an Access database and returns with the desired record(s). It works just fine BUT....I get a new "Connection" listed in my Data - Connections and a new "ExternalData" range name listed in Formula - Name Manager.

Through VBA I create the range name I want and would like to ensure the extraneous connections and ExternalData ranges aren't created.
I assume there are .Member commands that will suppress this but I can't seem to find them.... any pointers will be appreciated.

Thanks

 

[RSXchin]

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

 

[KG Old Wolf]

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

 

[KG Old Wolf]

DUH

QueryRefresh is not the same as Refresh....

that fixed it,
thanks

 

[RSXchin]

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

Range("a1").QueryTable.Refresh

 

[krysolov]

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:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim stSQL As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rn As Range
    Dim qt As QueryTable
     
    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=DBNAME;" & _
    "Data Source=SVRNAME"
     
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets(1)
     
    With ws
        Set rn = .Range("A1")
    End With
     
    stSQL = "<some large SQL statement>"
     
    Set cn = New ADODB.Connection
     
    With cn
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rs = .Execute(stSQL)
    End With
     
	Set qt = ws.QueryTables.Add(Connection:=rs, Destination:=rn)
	With qt
		.RefreshStyle = xlInsertDeleteCells
		.Refresh
		.MaintainConnection = False
	End With

     'Cleaning up.
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

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.

 

[RSXchin]

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

 

[krysolov]

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."

 

[xenou]

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).

 

[krysolov]

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