Solved

establish a direct connection to a Oracle table - Urgent

Posted on 2006-06-12
9
388 Views
Last Modified: 2012-05-05
I need help to establish a direct connection to Oracle.

I have no idea what I need to do.  I took this information from the tnsnames.ora on my computer.  I also need to pass the User Name (Name) and Password (Pass).  I think my main problem is in building the connection string.  Any help would be appreciated.


# SAP P4S Environment
P4S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p4s-dbdl.sikorsky.com )(PORT = 1534))
    )
    (CONNECT_DATA =
      (SID = P4S)
    )
  )


Thank You!
0
Comment
Question by:Delirious
  • 5
  • 2
  • 2
9 Comments
 

Author Comment

by:Delirious
ID: 16888333
If any other information is needed let me know.

Thanks
0
 

Author Comment

by:Delirious
ID: 16888742
I need this connection to establish itself when the database opens and remain connected until the database closes.
0
 

Author Comment

by:Delirious
ID: 16888751
I'm leaving for the day, be back tomorrow.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16889507
What do you mean by a direct connection?
In a table definition - or perhaps an ADO connection object?

Have a look here (the usual source of connection strings) - to see if this helps.
http://www.connectionstrings.com/
0
 

Author Comment

by:Delirious
ID: 16889708
Yes the ADO object is what I need.  

So, using the link you supplied, I guess I need the following.

"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

But I'm not sure what this means.  
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 16889944
What it means?
It's the connection object's connection string?
You'd substitute into it values for your server, username and password of course :-)


Dim cnn as New ADODB.Connection

cnn.ConnectionString = "Driver={Microsoft ODBC Driver for Oracle};Server=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

Of course - if you have the OLE DB provider for Oracle installed then perhaps you could use that (for better performance).

"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16893166
I have done connections direct to Oracle before using ADO

Here are some simplified snippets of my code
All code to be placed in a module


Public Enum ConnectPrompts
    adPromptAlways = 1
    adPromptComplete = 2
    adPromptCompleteRequired = 3
    adPromptNever = 4
End Enum

Dim m_cnOracle As ADODB.Connection
Dim m_rsOracle As ADODB.Recordset

Public Function ConnectOracleADO(ByVal sSID As String, _
                                 ByVal sUserID As String, _
                                 ByVal sPassword As String) As Boolean

    On Error GoTo ConnectFailure
   
    If m_cnOracle Is Nothing Then
        Set m_cnOracle = New ADODB.Connection
    End If
   
    If m_cnOracle Is Nothing Then
        MsgBox "Cannot initialise Oracle ADO Object", vbCritical, "Ooops"
        Exit Function
    End If
   
    Debug.Print "Connecting..."
    'Connect to database
    m_cnOracle.Provider = "MSDAORA"
    m_cnOracle.Properties("Prompt") = ConnectPrompts.adPromptComplete
    m_cnOracle.Open sSID, sUserID, sPassword

    ConnectOracleADO = True
    Exit Function

ConnectFailure:
    ConnectOracleADO = False

End Function

Public Function ExecuteSQL(ByVal sSql As String) As Boolean

    On Error GoTo ExecuteFailure
   
    If sSql <> "" Then
        Debug.Print "Fetching data..."
        Set m_rsOracle = New ADODB.Recordset
        m_rsOracle.CursorLocation = adUseClient
        m_rsOracle.Open sSql, m_cnOracle, adOpenStatic, adLockOptimistic, adAsyncFetch
   
        'Loop thru
        Do While m_rsOracle.EOF = False
            Debug.Print m_rsOracle.Fields(0).Name, m_rsOracle.Fields(0).Value
            m_rsOracle.MoveNext
        Loop
        Debug.Print "Fetched"
    End If

    ExecuteSQL = True
    Exit Function
   
ExecuteFailure:
    ExecuteSQL = False
End Function

Public Function DisconnectOracleADO()

    On Error Resume Next
   
    m_rsOracle.Close
    m_cnOracle.Close
   
    Set m_rsOracle = Nothing
    Set m_cnOracle = Nothing

End Function



Do note, this is simplified. Not complete validation is there. I have other stuff and its all part of a class, but Ive taken samples to be used in a module.


'Your TNS Names SID is PS4

    If ConnectOracleADO("PS4", "myuserid", "mypswd") = True Then
        ExecuteSQL "SELECT * FROM MYTABLE"
        DisconnectOracleADO
    End If
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16893173
I have set the connection and recordset to be a module level variable. You could make it global by putting in public
But one call to Connect means your database connection is active. Simply make use of m_cnOracle

0
 

Author Comment

by:Delirious
ID: 16893513
Here's what I did.  Thanks for both your help.


Public Function ConnectOracle()
On Error GoTo Err_ConnectOracle

Dim cn As New ADODB.Connection
   
    cn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=user;Data Source=P4S"
    cn.Open

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question