Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Handling of QueryTable-results (XP)

    Hello everybody,

    I am wondering whether it is possible to access the result of a query table other than by writing them to a certain range in the spreadsheet. I would like to handle the output directly in an array-variable within VBA. Is there a way to do so? Or do I have to get used to my "workaround"?

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

    Re: Handling of QueryTable-results (XP)

    You can open a DAO or ADO recordset in code, and use the GetRows method of the recordset to populate an array.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling of QueryTable-results (XP)

    Hello Hans,

    thanks for the reply. Your suggestion was my initial idea. Unfortunately I could not make it work, that's why I switched to querytables. Maybe you have an idea what's going wrong. I use the following connection string:
    <pre>Public Const gsCONNECTION As String = "ODBC;" & _
    "DRIVER={Oracle in instantclient10_2};SERVER=PEDB;" & _
    "UID=xxx;PWD=xxx;DBQ=PEDB;DBA=W;APA=T;EXC=F;XSM=De fault;" & _
    "FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F ;" & _
    "BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CS R=F;FWC=F;" & _
    "FBS=60000;TLO=O;"
    </pre>


    When using this with a querytable it works pretty cool (see code below)
    <pre>Function bQuery(sSQL As String, rngDestination As Range) As Boolean

    Dim qtTable As QueryTable
    Dim avValues() As Variant
    Dim adValues() As Double
    Dim lIndex As Long

    Const sSOURCE As String = "bQuery()"

    Dim bReturn As Boolean

    On Error GoTo ErrorHandler
    ' Zunächst gehen wir von ordnungsgemäßem Ablauf aus
    bReturn = True

    ' ----> ab hier steht der operative Code
    Set qtTable = wksOracleTables.QueryTables.Add(Connection:=gsCONN ECTION, _
    Destination:=rngDestination)
    With qtTable
    .CommandText = sSQL
    .FieldNames = False
    .AdjustColumnWidth = False
    .RefreshStyle = xlInsertDeleteCells
    .Refresh BackgroundQuery:=False
    avValues = .ResultRange
    .Delete
    End With
    Set qtTable = Nothing

    ErrorExit:

    ' ----> Aufräumarbeiten hier
    bQuery = bReturn
    On Error Resume Next
    qtTable.Delete
    Set qtTable = Nothing

    Exit Function

    ErrorHandler:

    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
    Stop
    Resume
    Else
    Resume ErrorExit
    End If

    End Function
    </pre>


    When I try to do so with an ADO recordset, I retrieve an error message (I use the same connection string). I used the code given below:
    <pre>Public Sub CreateDBConnection()

    Dim lAttempt As Long
    Dim gcnConnection As ADODB.Connection

    Const sSOURCE As String = "CreateDBConnection"

    Dim bReturn As Boolean

    On Error GoTo ErrorHandler

    ' Zunächst gehen wir von ordnungsgemäßem Ablauf aus
    bReturn = True

    ' ----> ab hier steht der operative Code
    ' Versuch eines Verbindungsaufbaus
    Set gcnConnection = New ADODB.Connection
    gcnConnection.ConnectionString = gsCONNECTION
    gcnConnection.Open ----> This causes the error!
    gcnConnection.Close

    ErrorExit:

    ' ----> Aufräumarbeiten hier
    ' Statusbar zurücksetzen
    Application.StatusBar = False

    ' Funktion verlassen
    Exit Sub

    ErrorHandler:

    ' Wir versuchen dreimal, die Verbindung herzustellen, bevor wir endgültig
    ' aufgeben
    If lAttempt < 3 And gcnConnection.Errors.Count > 0 Then
    If gcnConnection.Errors(0).NativeError = 17 Then
    Application.StatusBar = "neuer Versuch für Verbindungsaufbau..."
    lAttempt = lAttempt + 1
    Resume
    End If
    End If

    bReturn = False
    If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then
    Stop
    Resume
    Else
    Resume ErrorExit
    End If
    End Sub
    </pre>

    The error message is as follows (or similar, since I use the German version)
    <pre>[Microsoft][ODBC Driver Manger] Data source name could not be located, and no standard driver was assigned</pre>

    I do not have any clue what's going wrong with it. I managed to do the same with Access but I'm still unlucky with Oracle.
    If there is somebody who has a hint for me, I would be very happy.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Handling of QueryTable-results (XP)

    Try removing the <code>"ODBC;"</code> bit from the start when using it with ADO.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Handling of QueryTable-results (XP)

    Hi Rory,

    thanks a lot, it did the trick!

Posting Permissions

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