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

    Connecting to Oracle Database (XP SP2)

    Hello everybody,

    I try to connect to an Oracle Database from within Excel via VBA. It seems I can establish the connection. Now I would like to figure out the tablenames from the tables included in my database. Unfortunately, it seems I am far from the solution. Here is the code I used:
    <pre>'-------------------------------------------------------------------------------
    ' Description:
    '
    ' Date Developer Action
    '-------------------------------------------------------------------------------
    ' 2006-12-11 Jörg Aniol, PE-GB Erste Version
    '

    Private Const msMODULE = "MDataAccess"

    Option Explicit

    Public gcnAccess As ADODB.Connection


    '-------------------------------------------------------------------------------
    ' Comments: Testet die Verbindung zu einer Oracle-Datenbank
    '
    ' Arguments:
    '
    ' Returns:
    '
    ' Date Developer Action
    '-------------------------------------------------------------------------------
    ' 2007-11-07 Jörg Aniol, PE-GB Erste Version
    '
    Sub ConnectToDatabase()

    Const sSOURCE As String = "ConnectToDatabase()"

    Dim lAttempt As Long
    Dim rsRecordSet As ADODB.Recordset
    Dim sSQL As String

    On Error GoTo ErrorHandler

    ' ----> ab hier steht der operative Code
    ' ConnectionString aufbauen
    gsConnect = "DRIVER={Oracle in instantclient10_2};" & _
    "SERVER=PEDB;UID=aniol;PWD=aniol;DBQ=PEDB;"

    ' Versuch eines Verbindungsaufbaus
    Set gcnAccess = New ADODB.Connection
    gcnAccess.ConnectionString = gsConnect
    gcnAccess.Open

    sSQL = "SELECT TNAME FROM TAB;"

    Set rsRecordSet = New ADODB.Recordset
    rsRecordSet.Open sSQL, gcnAccess, adOpenForwardOnly, adLockReadOnly

    If Not rsRecordSet.EOF Then
    Sheets("Tabelle 2").Range("A1").CopyFromRecordset rsRecordSet
    Else
    MsgBox "No data located!"
    End If

    ' Verbindung schließen
    gcnAccess.Close

    ErrorExit:

    ' ----> Aufräumarbeiten hier
    Application.StatusBar = False
    Exit Sub

    ErrorHandler:

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

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


    Attached you will find two screenshots from Oracle SQL Developer. They show the strucuture of my DB and the same SQL command.
    Here is what I want to do:
    <UL><LI>find out the table names contained in my database
    <LI>select a table from the resulting table list
    <LI>work with the selected table[/list]I have no idea what's going wrong. My problem is that I am new on both databases in general and accessing them from within Excel.

    Any help will be highly appreciated!!

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

    Re: Connecting to Oracle Database (XP SP2)

    I don't see any attachment...

    I don't think there are many Loungers who work with Oracle, but who knows... <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

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

    Re: Connecting to Oracle Database (XP SP2)

    Ooops, somehow they are lost in space...

    OK, lets have a new attempt
    Attached Images Attached Images

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

    Re: Connecting to Oracle Database (XP SP2)

    You haven't told us yet what goes wrong with the code you posted.
    - Do you get an error message? If so, what does it say?
    - Does it return incorrect results? If so, in what sense?
    - Something else? If so, what?

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

    Re: Connecting to Oracle Database (XP SP2)

    HUGE image that caused horizontal scrolling moved to zip file by HansV - please don't post pictures larger than 640 pixels wide.

    Hello Hans,

    what are you doing on your computer at 9 p.m.? Do you never stop working?
    I will try to be a little more detailed. I am sorry, since I have never done something similar before and am absolutely not used to databases, it is difficult for me to describe what's going wrong.
    When the code is running, it seems that establishing the connection is working fine. But I just found some error items within the ADODB.Connection object (see screenshot). When it comes to writing the recordset to the table, I receive an error message, saying that the Index is outside the range. The rsRecordSet.EOF property is FALSE, telling me, that there are results within my recordset. But the RecordCount property has the value (-1). I think something is wrong with my connection (because of the above mentioned errors). Unfortunately, I have no idea where to search.
    I have installed the Oracle Instantclient 10.2. With this, it is possible to create a querytable manually from within Excel (the macro recorder told me how to create the connection string). I have no idea whether this is sufficient for what I want to do, or if something is still missing.
    This is all the information I have at the moment. I hope that this gives some hints regarding my problem. Maybe there is someone out there who has done this before and could give me some gerenal hints.
    Attached Files Attached Files

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

    Re: Connecting to Oracle Database (XP SP2)

    A forward only recordset always returns a record count of -1, that is nothing to worry about. To be able to calculate the recordcount accurately, the recordset would have to be able to go to the last record and back, that is not possible in a forward only recordset.

    Are you sure that the worksheet name "Tabelle 2" is correct (inclusing the space)?

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

    Re: Connecting to Oracle Database (XP SP2)

    Hello Hans,

    sorry for the large image, I will keep it in mind next time I post a pic. Regarding my last question, I don't know why, but after a reboot of my computer the posted routine worked like expected, so sorry for bothering you with this. Nevertheless, I am chewing on the next part. In my database results of a variation calculation can be found. Some of the columns document input parameters, others store results. In order to find out what were the input values for a certain parameter, I would use the following SQL-statement:

    SELECT MAN_AN FROM TEST GROUP BY MAN_AN;

    MAN_AN is such an input parameter, and the statement delivers the required information (at least when I try this with Oracle SQL Developer). I use the following code to store the result in my worksheet:

    <pre>'-------------------------------------------------------------------------------
    ' Comments: Filtert einen Parametersatz aus der Datenbank
    '
    ' Arguments:
    '
    ' Returns:
    '
    ' Date Developer Action
    '-------------------------------------------------------------------------------
    ' 2006-12-11 Jörg Aniol, PE-GB Erste Version
    '
    Sub SelectFromDB(sTable As String)
    Dim rsData As ADODB.Recordset
    Dim sSQL As String
    Dim sHeader As String

    Dim lParam As Long
    Dim lIndex As Long
    Dim lLastRow As Long
    Dim rngC As Range

    Const sSOURCE As String = "SelectFromDB()"

    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
    ' Letzte Parameterliste löschen
    lLastRow = wksParameters.UsedRange.Rows.Count
    If lLastRow = 1 Then lLastRow = 2
    wksParameters.Rows("2:" & lLastRow).ClearContents

    ' Array mit den verschiedenen Korrekturen definieren
    MGlobals.InitVariables

    ' Daten empfangen
    If gcnConnection Is Nothing Then
    If Not bCreateDBConnection() Then
    Err.Raise glHANDLED_ERROR, sSOURCE, _
    "Verbindung zur Datenbank ist fehlgeschlagen!"
    End If
    End If
    gcnConnection.Open

    ' Schleife durchlaufen, um alle Variationsparameter aus der Datenbank
    ' herauszufiltern
    For lIndex = LBound(gasIndex) To LBound(gasIndex)
    For lParam = LBound(gasParams) To LBound(gasParams)
    'sHeader = gasParams(lParam) & gasIndex(lIndex)
    sHeader = "MAN_AN"
    Set rngC = wksParameters.UsedRange.Find(What:=sHeader, _
    LookAt:=xlWhole)
    If rngC Is Nothing Then
    Err.Raise glHANDLED_ERROR, sSOURCE, _
    "Variationsparameter nicht in Zieltabelle hinterlegt!"
    End If

    ' SQL-Abfrage bauen
    sSQL = "SELECT " & sHeader & " " & _
    "FROM " & sTable & " " & _
    "GROUP BY " & sHeader & ";"
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, gcnConnection, adOpenForwardOnly, adLockReadOnly, _
    adCmdText

    If Not rsData.EOF Then
    rngC.Offset(1, 0).CopyFromRecordset rsData
    Else
    MsgBox "Keine Daten identifiziert!", vbCritical, "Error!"
    End If
    rsData.Close
    Set rsData = Nothing
    ErrorLoopBack:
    Next lParam
    Next lIndex
    gcnConnection.Close

    ErrorExit:

    ' ----> Aufräumarbeiten hier

    Exit Sub

    ErrorHandler:

    ' Wenn die Tabellenüberschrift nicht gefunden wird, muss mit der nächsten
    ' weitergemacht werden
    If Err.Number = glHANDLED_ORACLE_ERROR Then Resume ErrorLoopBack

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



    There are two For ... Next loops inside the code. They will be used when my problem is fixed, but for now, they are without any function. The parameter I want to analyse, is MAN_AN. The SQL-Statement is stored to sSQL, and used with a recordset. I would have expected the rng.CopyFromRecordset to copy the result from the SQL-statement completely into the sheet. Unfortunately, I only get one single number. This is part of the list, but the result should be ten numbers. I have used the same bit of code for an Access-database before, and there it delivered the expected results, but here I feel totally lost.
    Has someone experienced something like this before? My big problem is that I am new to databases and therefore have no idea how to debug this stuff.
    Any help will be highly appreciated!

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

    Re: Connecting to Oracle Database (XP SP2)

    The query will return only unique values of MAN_AN. Are you sure there are multiple unique values? If so, I have no idea what causes the problem.

    You could change the line

    rngC.Offset(1, 0).CopyFromRecordset rsData

    to

    MsgBox rngC.Offset(1, 0).CopyFromRecordset(rsData) & " record(s) inserted"

    Do you really get "1 record(s) inserted"?

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

    Re: Connecting to Oracle Database (XP SP2)

    Hello Hans,

    thanks very much for replying so late in a Sunday night. Yes, I really get "1 record(s) inserted". Refer to the pic to see what happens in Oracle sql developer when I have the same SQL-statement.
    I don't know, should I feel relieved now that it seems it's not my mistake??
    Attached Images Attached Images

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

    Re: Connecting to Oracle Database (XP SP2)

    Well, at least you know that the SQL is correct.

    Perhaps there is a Lounger who has experience with importing data from Oracle.

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

    Re: Connecting to Oracle Database (XP SP2)

    Hello Hans,

    thanks for the reply. Now I will try to get some help from Oracle. If I find a solution for this, I will post it here.

Posting Permissions

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