Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: QueryTables.Add(Connection:= (2003 SP3)

    What is strValue?

  2. #2
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QueryTables.Add(Connection:= (2003 SP3)

    My mistake. It should say strChain which is the value passed in. Sorry about that.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    QueryTables.Add(Connection:= (2003 SP3)

    I'm attempting to populate Sheet1 with a list of data from our SQL Server 2005 database. The following query works well if I don't add the WHERE clause.
    I keep getting a type mismatch error with the .CommandText = Array line when I add the WHERE clause.

    Some other useful info regarding my question:
    1. I pass in a code called strChain
    2. Each chain has 1 or more service lines
    3. I want to return only the service lines for the chain code that is passed in.
    4. If you know of a better way to pass in an SQL string to the database, I'm happy to give it a try!

    Sub ChainSvcLines(ByVal strChain As String)
    On Error GoTo Error_Handler

    With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DRIVER=SQL Server;SERVER=999.99.99.9;UID=user123;PWD=******** *;APP=Microsoft Office 2003;WSID=XXX-XXXXXX" _
    , Destination:=Range("I2"))
    .CommandText = Array("SELECT DISTINCT CM_CLIENT.CHAIN, " & _
    "CM_Service_Line_Master.ServiceLineDescription , CM_CLIENT.LINE_OF_BUSINES " & _
    "FROM CM_DEBTOR INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client " & _
    "INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID " & _
    "WHERE CM_CLIENT.CHAIN = ' " strChain & " ' ")
    .Name = "qryChainLOB"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    Error_Handler:
    MsgBox Err.Number & " " & Err.Description

    End Sub

    As always, any suggestions would be most helpful.
    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: QueryTables.Add(Connection:= (2003 SP3)

    What happens if you use
    <code>
    .CommandText = "SELECT DISTINCT CM_CLIENT.CHAIN, " & _
    "CM_Service_Line_Master.ServiceLineDescription , CM_CLIENT.LINE_OF_BUSINES " & _
    "FROM (CM_DEBTOR INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client) " & _
    "INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID " & _
    "WHERE CM_CLIENT.CHAIN = " & Chr(34) & strChain & Chr(34)</code>

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QueryTables.Add(Connection:= (2003 SP3)

    Hans,
    I did two things.
    1. I defined a variable called strSQL in the beginning of the procedure like this:
    Dim strSQL as string

    strSQL = "SELECT DISTINCT CM_CLIENT.CHAIN, "
    strSQL = strSQL & "CM_Service_Line_Master.ServiceLineDescription , "
    strSQL = strSQL & "CM_CLIENT.LINE_OF_BUSINES FROM CM_DEBTOR "
    strSQL = strSQL & "INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client "
    strSQL = strSQL & "INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID "
    strSQL = strSQL & "Where CM_CLIENT.CHAIN = '" & strChain & "'"

    2. I took the " =Array("...") " part out of the .CommandText = Array("...")
    and replaced it with .CommandText = strSQL and it works.

    It seems that if you build your query by recording a macro, it inserts the .CommandText = Array("...") for some reason.

    As always, thanks for your guidance. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: QueryTables.Add(Connection:= (2003 SP3)

    Congrats on getting it to work.

    Yes, the macro recorder creates an array, not sure why. It isn't necessary, as you have found.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •