?
Solved

establish a direct connection to a Oracle table - Urgent

Posted on 2006-06-12
9
Medium Priority
?
398 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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