Blue Flower

 

Sub GetEmployees()
  ' Use OO4O
  Set objSession = CreateObject("OracleInProcServer.XOraSession")
  Set objDatabase = objSession.OpenDatabase("", "scott/tiger", 0)
  
  Sql = "select * from emp"
  
  Set oraDynaSet = objDatabase.DBCreateDynaset(Sql, 0)
  
  If oraDynaSet.RecordCount > 0 Then
    oraDynaSet.MoveFirst
    For x = 0 To oraDynaSet.Fields.Count - 1
      Cells(1, x + 1) = oraDynaSet.Fields(x).Name
      Cells(1, x + 1).Format = Bold
    Next
    
    For y = 0 To oraDynaSet.RecordCount - 1
      For x = 0 To oraDynaSet.Fields.Count - 1
        Cells(y + 2, x + 1) = oraDynaSet.Fields(x).Value
      Next
      oraDynaSet.MoveNext
    Next
  End If
  
  Set objSession = Nothing
  Set objDatabase = Nothing
End Sub


'Funktioniert leider noch nicht

Sub Connect_to_oracle()

  Dim Cn ' As ADODB.Connection
  Dim CP ' As ADODB.Command
  Dim Rs ' As ADODB.Recordset
  Dim Conn As String
  Dim QSQL As String
  
  'Connect to Oracele server begin
  Conn = "DRIVER={ORACLE ODBC DRIVER};SERVER=Service name;UID=username;PWD=password;DBQ=Service name;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=O;"
  Set Cn = CreateObject("ADODB.Connection")
  Set Cn = New ADODB.Connection
  With Cn
    .ConnectionString = Conn
    ' .CursorLocation = adUseClient
    .Open
  End With
  
  If Cn.State = adStateOpen Then
    MsgBox "Connection successful."
  End If
  
  'Connect to Oracle server end
  'close connection begin
  Cn.Close
  Set Cn = Nothing
  Set CP = Nothing
 'close connection end
End Sub