VBA parameter query syntax error

[KG Old Wolf]

All,

I need a fresh set of eyes... I just can't see the issue with the first "with" line of code (connection string). I am attempting to write my first "parameter-driven" query in VBA. Thanks for any suggestions....

Sub qry_Divsions()
'
'
'   Parameter driven query to extract specific client Division locations
'
Dim qry_Practice_Abbr As String
qry_Practice_Abbr = Range("prm_Practice_Abbr")
'
'
With ActiveSheet.Query.Tables.Add(Connection:=Array( _
"ODBC;DSN=CMS2;Description=CMS2; _
APP=Microsoft Office 2010;DATABASE=cms_data; _
Trusted_Connection=Yes), Destination:=Range("$A$1"))
        '
        .CommandText = Array( _
        "SELECT Division.DivisionAbbr, Division.FeeSchedUpdate, Division.projDate, Practice.PracticeAbbr, Practice.PracticeName, Practice.FiscalYearEnd" & Chr(13) & "" & Chr(10) & "FROM cms_data.dbo.Division Division, cms_data.dbo.Practi" _
        , "ce Practice" & Chr(13) & "" & Chr(10) & "WHERE Division.PracticeID = qry_Practice_Abbr")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_CMS2"
        .Refresh BackgroundQuery:=False
        '
    End With
'
'
End Sub

 

[RoryA]

It should be QueryTables.Add rather than Query.Tables.Add

 

[KG Old Wolf]

Thanks but still no joy.....

 

[RoryA]

What is the error now?

 

[KG Old Wolf]

I should have posted that... sorry

Compile error:
Syntax error

Thanks,
Ken

 

[RoryA]

On which bit?

 

[Ruddles]

You have a couple of line continuation characters in the middle of your connection string and unmatches quotes.

Try:-
[CODE]With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=CMS2;Description=CMS2;" _
& "APP=Microsoft Office 2010;DATABASE=cms_data;" _
& "Trusted_Connection=Yes"), Destination:=Range("$A$1"))
[/CODE]

I think that's right... dashing out... catch you later...

 

[KG Old Wolf]

Rory....

The error remains in the same line of code... the block from the first WITH

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=CMS2;Description=CMS2;APP=Microsoft Office 2010;DATABASE=cms_data; _
Trusted_Connection=Yes"),Destination:=Range("$A$1"))


Much appreciated,
Ken

 

[KG Old Wolf]

Ruddles' suggestion worked. I now have an issue with the way I am trying to introduce the parameter but that is something I will research further before troubling anyone further.

thanks for your time and help.

Much appreciated,
Ken