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

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.