Re: How to connect oracle using ADO

From: Kenneth C Stahl <BlueSax_at_Unforgettable.com>
Date: Mon, 15 Nov 1999 09:27:34 -0500
Message-ID: <38301856.A0D3E27C@Unforgettable.com>

ps2cheung_at_yahoo.com wrote:
>
> All,
>
> I am working on a VB application where I need to access some data on an
> ORACLE database. ADO provides a Connection object that will allow me
> to perform this activity. The Connection object has a ConnectionString
> property that provides ADO the necessary information to make the
> connection
> to the database. The arguments needed within the string to make a
> connection to an ORACLE database are as follows:
>
> anADOConnection.ConnectionString = "Driver={Microsoft ODBC for
> Oracle};" _
> & "Server=name of server;UID= user name;Password=password"
>
> For the Server argument, the only value that worked for me was the name
> of a
> service that I had previously established using the ORACLE Net8
> Assistant
> interface. This interface creates a service that establishes the
> hostname,
> the port number, the SID, the username, and password of an ORACLE
> database.
>
> My question: Is there a way to create an ORACLE service(server) within
> my VB
> code so that my application will not have to depend on an existing
> service?
>
> The ADO documentation from Microsoft seems only to have examples using
> Microsoft Access or SQL Server. Any help or advice will be appreciated.
> Thanks
>
> Please reply to dfong_at_esri.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

1. Do you have the latest MDAC distribution? If you are not sure, go to the following URL and download the lateste version and install it:

http://www.microsoft.com/data/download.htm

2. Go to Project->References on the main VB menu. Ensure that the item "Microsoft ActiveX Data Objects 2.1 Library" is checked. If you have "Microsoft ActiveX Data Objects 2.0 Library" checked, uncheck it.

3. If you really want to use ODBC, make your connection like this (this assumes a tnsnames.ora entry of xyzzy for the Oracle database with which you wish to connect):

Dim Ora_Conn as ADODB.Connection
Dim Ora_ConnStr as String

Set Ora_Conn = New ADODB.Connection

Ora_ConnStr = "UID=scott;" & _
              "PWD=tiger;" & _
              "DRIVER={Microsoft ODBC for Oracle};" & _
              "SERVER=xyzzy;"

Ora_Connection.ConnectionString = Ora_ConnStr Ora_Connection.Open

However, ODBC may not be the best choice. There is a native OLE DB service which should be faster in most cases. To use it, make the connection like this:

Dim Ora_Conn as ADODB.Connection
Dim Ora_ConnStr as String

Set Ora_Conn = New ADODB.Connection

Ora_ConnStr = "Data Source=pluto;" & _
              "User Id=scott" & _
              "Password=tiger;"

Ora_Connection.ConnectionString = strConnect Call Ora_Connection.Open

Notice that the ODDB connection uses "UID" and "PWD" while the OLE DB connection uses "User Id" and "Password". The distinction is crucial. You cannot intermix them. Received on Mon Nov 15 1999 - 08:27:34 CST