establish a direct connection to a Oracle table - Urgent

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 =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1534))
      (SID = P4S)

Thank You!
Who is Participating?
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
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};;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;"
DeliriousAuthor Commented:
If any other information is needed let me know.

DeliriousAuthor Commented:
I need this connection to establish itself when the database opens and remain connected until the database closes.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

DeliriousAuthor Commented:
I'm leaving for the day, be back tomorrow.
Leigh PurvisDatabase DeveloperCommented:
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.
DeliriousAuthor Commented:
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};;Uid=myUsername;Pwd=myPassword;"

But I'm not sure what this means.  
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

    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
        Debug.Print "Fetched"
    End If

    ExecuteSQL = True
    Exit Function
    ExecuteSQL = False
End Function

Public Function DisconnectOracleADO()

    On Error Resume Next
    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"
    End If
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

DeliriousAuthor Commented:
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"

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.