Consulting

Results 1 to 19 of 19

Thread: Solved: Connect to Oracle

  1. #1
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location

    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"

    [VBA]
    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
    [/VBA]

    thanks..

  2. #2
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    does anyone know how to connect to oracle from excel?
    thanks..

  3. #3
    VBAX Newbie
    Joined
    Feb 2008
    Posts
    1
    Location
    After adding a reference to the latest MDAC, use this.

        Set gconConnection = New Connection
        With gconConnection
            .ConnectionString = "Provider=MSDAORA;" & _
                                "Data Source=Oracle database name;" & _
                                "User ID=scott;Password=tiger;"
            .Open
        End With

  4. #4
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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?
    [VBA]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
    [/VBA]
    please help me

  6. #6
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    maybe it can help you with this.. i got the idea from LittlePD..
    pls add reference to Microsoft ActiveX Data Object..

    Sample:

    [VBA]
    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
    [/VBA]

  7. #7

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

  8. #8
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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.
    [vba].ConnectionString = "where is database you save like server=v-repo-01" & _
    "Data Source=name of database;" & _
    "User ID=System;Password=oracle;"
    .Open [/vba] and then you can see this in local window in your connectionstring
    [vba]
    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;"
    [/vba]

  15. #15
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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
    VBAX Tutor gnod's Avatar
    Joined
    Apr 2006
    Posts
    257
    Location
    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
  •