Solved

establish a direct connection to a Oracle table - Urgent

Posted on 2006-06-12
9
389 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

861 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