Solved

establish a direct connection to a Oracle table - Urgent

Posted on 2006-06-12
9
385 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
 
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
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.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…

937 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now