Re: Migrating from MSSQL to Oracle

Comments embedded
"Dan McLoughlin" <infoserve_at_indigo.ie> wrote in message news:P99B7.2022$8s4.7754_at_news.indigo.ie...
> Hi all,
>
> I am trying to connect to an Oracle database with a VB application that
> curreently uses a SQL database. I set up the connection for SQL as
follows:
>
> Public conMain As Connection
> ..
> Set conMain = New Connection
> .
I assume this is an ADO connection.

> ' get database settings from registry
> strDatabasePath = GetSetting("MyDB", "ConnectionDetails", "DatabasePath")
> strDatabaseUser = GetSetting("MyDB", "ConnectionDetails", "DatabaseUser")
> strDatabasePassword = GetSetting("MyDB", "ConnectionDetails",
> "DatabasePassword")
> strDatabase = GetSetting("MyDB", "ConnectionDetails", "Database")
> .
> .
> 'set the connection properties
> conMain.Properties("Data Source") = strDatabasePath ' machine name
> conMain.Properties("User ID") = strDatabaseUser ' user name
> conMain.Properties("Password") = strDatabasePassword ' password
> 'connect to SQL
> conMain.Open

My reading is that thus far this would work fine.

> 'specify db to use
> conMain.DefaultDatabase = g_Database '

There is no equivalent of SQL Servers default database in Oracle so this line is not needed.

I tend to build a connection string of the form

strConnect="Provider=OraOLEDB.Oracle;User ID=<username>;Password=<password>;Data Source=<tnsnames entry>;"

> Can anyone please tell me what is the equivalent connection string for an
> Oracle 8i database, plus any other problems to expect after I make the
> connection. I also assume that I need to install the Oracle client and
> create an Instance on each client PC. Any advice on how to do this, or
the
> source of more detailed information would be a great help. Any input
would
> be much appreciated.

Not quite sure what you mean here. Yes you do need to install the client (including the OLEDB Provider). You also need to ship a net8 configuration with your app. An oracle instance is the set of processes and memory structures that manage an oracle database. As such it sits on the database server(s).

--
Niall Litchfield
Oracle DBA
Audit Commission UK