Consulting

Results 1 to 6 of 6

Thread: Solved: Connect to Oracle Database using Excel VBA

  1. #1

    Solved: Connect to Oracle Database using Excel VBA

    Hi Everybody


    I am trying to connect to an Oracle database using Excel VBA (using the ADO objects).

    Does anyone know the connection string syntax to connect to Oracle database, preferably prompting the user to enter the user id and password.

    I could do the same to connect to SQL Server database.


    Best regards


    Deepak

  2. #2
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location

  3. #3
    Hi Everybody


    I found something along these lines on the net somewhere.

    I am putting this here so that if anyone else is looking for a solution for this kind of connection issues, they may be able to use this solution.

    Thanks for your link as well - although I didn't need to use it but I do like your knowledge base article of how to get the pdf file information in Excel or a text file - that is great! I have been looking for this for sometime!


    [vba]
    Sub GetData()

    Dim SQL_String As String
    Dim dbConnectStr As String
    Set con = New ADODB.Connection
    Set recset = New ADODB.Recordset
    Dim recordCount As Long
    dbConnectStr = "Provider=msdaora;Data Source=" & "Oracle_Database_Name;"


    User Id=userId" & "; Password=" & "password"
    'The statement above has been commented out. I use the statement below to prompt the user for the userId and password - which is what I prefer!

    con.ConnectionString = dbConnectStr
    con.Properties("Prompt") = adPromptAlways
    con.Open dbConnectStr 'ConnectionString

    'This is an example SQL code that you might want to run
    'Select * From MyTable

    'SQL_String = "Select count(*) from adm_user"
    recset.Open SQL_String, con
    recset.MoveLast
    recordCount = recset.recordCount
    recset.MoveFirst
    Do While Not recset.EOF = True
    'Have a loop here to go through all the fields
    recset.MoveNext
    Loop
    recset.Close
    End Sub
    [/vba]



    Best regards



    Deepak

  4. #4
    Hello all, hi Deepak,

    I tried to built documented solution.
    My realized following steps:

    (1) Successfully installation of 18 XE Express
    (2) Successfully connect with "User DSN" (ODBC-Datasource-Adminstration/Oracle ODBC Driver Connect): Input of "Service Name" / "User Name" / "Password"

    My questions:

    (1) Is the discribed ODBC-Connection needed, like discribed under (2)
    (2) Concerning your information >>> Data Source=" & "Oracle_Database_Name;"
    What is the substitution of "Oracle_Database_Name". Is this the first string in tnsnames.ora: XE?


    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = RENEDI-HP)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    Thanks for an answer.

    Regards JuMathias

  5. #5
    Hello all, hi Deepak,

    my further question:

    Using EXCEL 2016) and the link "Microsoft Active Data Objects 6.1 Libary" (msado 15.dll), I get an error in the fouth line:


    Sub Option4()

    Dim SQL_String As String
    Dim dbConnectStr As String
    Set con = New ADODB.Connection < Information of EXCEL: Variable is not defined. What's to do?

    Thanks for infromation

    Regards

    JuMathias

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,883
    Location
    JuMathias: Kindly don't resurrect ancient threads just to hijack them with a different question. Start a new thread instead. Thread closed.
    Cheers
    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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