Re: Interfacing Oracle and Visual Basic

Charles Newman wrote:

> *** post for free via your newsreader at post.newsfeeds.com ***
>
> How do I set up the Oracle Data Control to use Oracle Listener? My
> problems can be solved by using ADODB for writes and Oracle Data control for
> reads. However, I need to be able to get the Oracle Data Control to talk to
> the Oracle Listender.
>
> In the default "SCOTT" account, I am using
>
> ORADC1.Connect ="scott / tiger"
> ORADC1.RecordSource = "select * from item"
> ORADC1.DatabaseName = "po8"
>
> "po8" is the service name (what you would type in, in the "Connect" field
> in
> SQL Plus) in TNSNAMES. However, I can the error telling me the service name
> cannot be resolved.
>
> which works OK in SQL Plus, and I have no problem with ADO or ADODB, but
> I cannot get ORADC to use it correctly.

One doesn't "use" the listener. It is merely a black box that monitors a port for activity. If you have the listener configured correctly and also have tnsnames.ora (and possibly sqlnet.ora) configured properly on the client, then you should be able to establish a connection via the service name (sometimes called a "database alias") that is defined in tnsnames.ora.

If you really start digging into the ADO documentation you'll eventually discover that you cannot treat an Oracle database in the same manner as you would an Access database because you cannot create an updatable recordset since the cursor is read-only in the current implementations of OLE-DB for Oracle. That means that if you want to update a record you must use DML to effect the change and then either refresh the cursor to show the new values or else move the new values into the appropriate location in the recordset. When I first started working with ADO I thought that I could use the recordset as a bi-directional pipe to the database, but the more I read and studied the better I understood why Oracle differs from Access in this regard. Received on Wed Nov 29 2000 - 11:24:42 CST