Consulting

Results 1 to 19 of 19

Thread: Solved: Connect to Oracle

  1. #1

    Solved: Connect to Oracle

    Hi,

    i try to connect to oracle and also i add the references "Oracle In Proc Server 4.0 Type Library" but still i'm getting error - "The specified module could not be found"

    Sub ConnectToOracle() 
        Dim objSession As Object 
        Dim objDatabase As Object 
         
         ' Create a reference to the OO40 dll
        Set objSession = CreateObject("OracleInProcServer.XOraSession") 
         
         ' Create a reference to my database
        Set objDatabase = objSession.opendatabase("Oracle", "scott/tiger", 0&) 
    End Sub 
    
    
    Formatting tags added by mark007
    thanks..

  2. #2
    does anyone know how to connect to oracle from excel?
    thanks..

  3. #3
    After adding a reference to the latest MDAC, use this.

    Set gconConnection = New Connection 
    With gconConnection 
        .ConnectionString = "Provider=MSDAORA;" & _ 
        "Data Source=[i]Oracle database name[/i];" & _ 
        "User ID=scott;Password=tiger;" 
        .Open 
    End With 
    
    
    Formatting tags added by mark007

  4. #4
    Thanks Little PD for the reply..
    i'll try that code to connect to oracle..


    thanks,

  5. #5
    hi all,
    i wanna asking for connection with oracle and vba using this source but i got error object required. can anybody help me?
    Dim strConnection, conn, rs, strSQL 
    strConnection = "Provider=OraOleDb.Oracle.1;Password=123456;Persist Security Info=True;User ID=viola-0.5.0;Data Source=(DESCRIPTION =(ADDRESS=(PROTOCOL=TCP)(HOST=v-repo-01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE))" 
     
     
    Set conn = Server.CreateObject("ADODB.Connection") 
    conn.open strConnection 
     
    Set rs = Server.CreateObject("ADODB.recordset") 
    strSQL = "SELECT * FROM AIRLINE" 
    rs.open strSQL, conn, 3, 3 
     
    rs.MoveFirst 
    While Not rs.EOF 
        RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME") 
        rs.MoveNext 
    Wend 
     
    rs.Close 
    Set rs = Nothing 
     
    conn.Close 
    Set conn = Nothing 
    
    
    Formatting tags added by mark007
    please help me

  6. #6
    maybe it can help you with this.. i got the idea from LittlePD..
    pls add reference to Microsoft ActiveX Data Object..

    Sample:

    Sub test() 
        Dim conn, rs 
        Dim strSQL As String 
         
        Set conn = CreateObject("ADODB.Connection") 
        With conn 
            .ConnectionString = "Provider=MSDAORA;" & _ 
            "Data Source=ORCL10G;" & _ 
            "User ID=System;Password=oracle;" 
            .Open 
        End With 
         
        Set rs = CreateObject("ADODB.Recordset") 
        strSQL = "select * from scott.emp" 
        rs.Open strSQL, conn, 3, 3 
         
        Range("A1") = rs("ENAME") 
        Range("B1") = rs("JOB") 
         
        rs.Close 
        Set rs = Nothing 
        conn.Close 
        Set conn = Nothing 
    End Sub 
    
    
    Formatting tags added by mark007

  7. #7

    thank you very much for your response. thats source can make my vba and oracle connected

  8. #8
    kindly mark it solved., if your problem has been solve..


  9. #9
    opss i got eror message about ORA-12516: TNS:listener could not find available handler with matching. what that mean?
    it happen when i run it until 3 times (click run button)

  10. #10
    Did you change the data source in ConnectionString?

  11. #11
    Quote Originally Posted by gnod
    Did you change the data source in ConnectionString?
    yes i do. because if i'am not change it i've got the error ORA-12154:TNS:Could not resolved the conect identifier specified.

    so i change that Provider=MSDAORA with server=v-repo-01
    where is the name of listener host.

  12. #12
    hi,

    when i try the code here in my office, i also encounter ORA-12154: TNS: Could not resolve service name..

    i already change the TNSnames.ora in my PC

    pls

  13. #13
    hi,


    does anyone know how to slve my prblem?

    pls


    thanks,

  14. #14
    hello,
    Do you ever check local window in your vba window you can display that with click tab view and click local window.
    .ConnectionString = "where is database you save like server=v-repo-01" & _ 
    "Data Source=name of database;" & _ 
    "User ID=System;Password=oracle;" 
    .Open 
    
    
    Formatting tags added by mark007
    and then you can see this in local window in your connectionstring
    ConnectionString : "Provider=MSDASQL.1;Password=123456;User ID=viola-0.5.0;Data Source=viola-0.5.0;Extended Properties="DSN=viola-0.5.0;UID=viola-0.5.0;PWD=123456;SERVER=v-repo-01;" 
    
    
    Formatting tags added by mark007

  15. #15
    thanks for your reply..
    it was already solved.. i input a worng datasource which is currently setup in our office..



    thanks,

  16. #16
    did you use that littlePD source? and where you've got the mistake? can you describe me?

  17. #17
    i input a wrong datasource in the ConnectionString

  18. #18
    not in your provider that you input? what do you thing with my connectionstring with change in provider?because sometime there is an error i mentioned

  19. #19
    not in your provider that you input? what do you thing with my connectionstring with change in provider?because sometime there is an error i mentioned

Posting Permissions

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