none
How to create a new table based on existing connection? RRS feed

  • Question

  • Hi,

    I would like to use exiting connection (MyQuery) to create a new table query but when I run the following code it opens a new connection. I would like to use the existing query how can I do it?

    Sub Query()

    Dim Conn As WorkbookConnection

    Dim SQLCommand As Variant

    SQLCommand = "SELECT ....."

    Set Conn = ActiveWorkbook.Connections.Add("MyQuery", "", "OLEDB; DSN=XXXX;", SQLCommand, xlCmdSql)

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB; DSN= XXXX;" _

        , Destination:=Range("$A$1")).QueryTable

        .CommandType = xlCmdSql

        .CommandText = SQLCommand   

        .Refresh BackgroundQuery:=False

    End With


    Guy Zommer

    Thursday, August 20, 2015 11:44 AM

Answers

  • >>>Thanks but I get an error object doesn't support this action

    could you provide more details about which code you got an error object doesn't support this action? Sreenshot and environment that will help us to reproduce and resolve your issue.

    Otherwise if you have created an existing connection, you could use ActiveWorkbook.Connections object to access existing Connection Object.

    ActiveWorkbook.Connections(1).ODBCConnection.Connection

    • Edited by David_JunFeng Wednesday, August 26, 2015 8:15 AM
    • Marked as answer by Guy Zommer Sunday, August 30, 2015 4:28 AM
    Wednesday, August 26, 2015 8:14 AM

All replies

  • Hi,

    can someone answer this?

    thanks


    Guy Zommer

    Sunday, August 23, 2015 1:09 PM
  • According to your descrition, I suggest you could modify your codes like below:

    Sub Query()
    Dim Conn As WorkbookConnection
    Dim SQLCommand As Variant
    
    SQLCommand = "SELECT ....."
    Set Conn = ActiveWorkbook.Connections.Add("MyQuery", "", "OLEDB; DSN=XXXX;", SQLCommand, xlCmdSql)
    
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            Conn.ODBCConnection.Connection _
            , Destination:=Range("$C$6")).QueryTable
            .CommandType = Conn.ODBCConnection.CommandType
            .CommandText = Conn.ODBCConnection.CommandText
            .Refresh BackgroundQuery:=False
    End With


    Monday, August 24, 2015 9:44 AM
  • Hi,

    Thanks but I get an error object doesn't support this action


    Guy Zommer

    Tuesday, August 25, 2015 7:10 AM
  • >>>Thanks but I get an error object doesn't support this action

    could you provide more details about which code you got an error object doesn't support this action? Sreenshot and environment that will help us to reproduce and resolve your issue.

    Otherwise if you have created an existing connection, you could use ActiveWorkbook.Connections object to access existing Connection Object.

    ActiveWorkbook.Connections(1).ODBCConnection.Connection

    • Edited by David_JunFeng Wednesday, August 26, 2015 8:15 AM
    • Marked as answer by Guy Zommer Sunday, August 30, 2015 4:28 AM
    Wednesday, August 26, 2015 8:14 AM