ADO 2.5 compliant with Oracle ODBC Driver?

Posted on 2001-07-02
Last Modified: 2011-10-03
My ADODB.Connection object's underlying connection string points to a DSN using the Oracle ODBC Driver (Version

Has anyone been able to use ADO 2.5 to get rows back from
a connection object using this driver?

I'm unable to get any rows back in my ADODB.Recordset no matter how I execute a query against this connection.

Strangely enough, the same code works if I change the DSN to use the Microsoft ODBC for Oracle driver.

I thought all ODBC drivers would work with ADO. I'm using VB6 SP6.
Question by:jfairbairn1
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 3
  • +1

Expert Comment

ID: 6247165
The only thing that I can think of is that Oracle you have to make sure that you have an entry in the tnsnames.ora file (in the net80 directory).  The entry will have information on the server and database.

Expert Comment

ID: 6247378
ConnectObject.ConnectionString = "DSN=NameofDSN;UID=" & UID & ";" & "PWD" = PWD & """"
DSN should be created using Oracle ODBC Driver

WIth ConnectObject
 .ConnectionString = "SRVR=" & ServerName & _
   ";driver={Oracle ODBC Driver}" & _
   ";uid=" & UID & _
   ";pwd=" & PWD & _
   ";database=" & DBName
 .CursorLocation = adUseClient
 .Properties("prompt") = adPromptNever
 .ConnectionTimeout = 300
End With

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Rs.Open "TableName", ConnectObject, adOpenDynamic, adLockOptimistic, adCmdTable

Comments:If you are using SQL statement the you have to use adCmdText instead of adCmdTable.

I think this may Solve your Problem, if you need anything more please reply to Me

Good Luck


Expert Comment

ID: 6247676
Do you get an error message? If not, post the code which you use to open the connection and initialise the Rs.
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


Author Comment

ID: 6248540
Thanks for taking the time to write these comments.

I'm still getting no rows back however. I don't get any errors, just no rows.

Here's the code:

(1) I create my connection object:

 Set m_cnAvantis = New ADODB.Connection
 m_cnAvantis.ConnectionString = "DSN=" & Trim$(sMcDsn)  & ";UID=" & Trim$(sMcUid) & ";PWD=" & Trim$(sMcPwd)

where the DSN uses the Oracle ODBC Driver. The connection state = adStateOpen after this.

(2) I try to create my recordset object:

rsNodes.Open fsGetTopParent, g_connAvantis, adOpenDynamic, adLockOptimistic, adCmdText

where g_connAvantis is the connection object using the Oracle ODBC Driver...

rsNodes.EOF and rsNodes.BOF are always true after executing this statement...


Author Comment

ID: 6248544
Forgot to mention... in the code above, the fsGetTopParent function returns a string of SQL text...

Expert Comment

ID: 6248600

In your above code You are using a seperate connection object to connect to the ORACLE using DSN.
1. Please ensure the dsn created is using ORACLE ODBC Driver or Not.
Connection Object:"m_cnAvantis"
2. In your recordset opening you are using a seperate connection object please ensure is this same.
Connection Object:"g_connAvantis"

It is little confusing!!

Please see my previous comment for Code.


Expert Comment

ID: 6248643
Have you tried?...

Set rsNodes = m_cnAvantis.Execute(fsGetTopParent)

Author Comment

ID: 6248677
Sorry is a bit confusing...

But they both represent the same connection to the Oracle ODBC Driver.

Nigel... yes... I have tried getting a recordset back using the connection object but I get 0 rows back.

I have also tried using a command object but get the same result:

Dim cmdText as new ADODB.Command

With cmdText
  set .ActiveConnection = m_cnAvantis
  .CommandType = adCmdText
  .CommandText = sSQL
  set rsNodes = .Execute
End With

rsNodes.EOF = true and rsNodes.BOF = true

I'd hate to make the client use the Microsoft ODBC for Oracle Driver... even though it is free and comes with the

Expert Comment

ID: 6248797
Yes, in any case, the MS ODBC driver does not work too well. Don't you have Oracle OLEDB?

Might seem a daft question, but are you sure that the sql statement will return results in, for example, SQLPlus?

Author Comment

ID: 6250130
No, I don't have Oracle OLEDB.

I have been able to get records back if I execute the query within ODBC OpenLink (similar to SQLPlus) against the same DSN connection.


Expert Comment

ID: 6251155

I possible will you give the whole code how you are trying to call the records, so that I can help you out in this regard.

Because I am using ODBC ORACLE driver for my project.


Author Comment

ID: 6252738
Thanks... the populatetreeview subroutine calls the
fsGetTopParent function to return the SQL query string to be executed.

Private Sub PopulateTreeview()

    Dim rsNodes As New ADODB.Recordset  
    Dim objNode As clsNode
    Dim bDataAdded As Boolean
    Dim sKey As String      
    Dim sNodePath As String  

    On Local Error GoTo ErrHandler
    Screen.MousePointer = vbHourglass
    m_lCounter = 1          
    rsNodes.Open fsGetTopParent, g_connAvantis,  adopenKeyset, adLockOptimistic
    If rsNodes.EOF And rsNodes.BOF 'Always true here!!
        Screen.MousePointer = vbNormal
        MsgBox "No records!!!", App.Title
        Exit Sub
    End If
End Sub

Private Function fsGetTopParent() As String
Dim sSQL As String
    sSQL = "select distinct mc.mtntype.mtoi from  mc.mtntype where mc.mtntype.mtoi not in (select mc.mt_ptyp.owner_oi from mc.mt_ptyp) "
    fsGetTopParent = sSQL

End Function

Accepted Solution

nigelrowe earned 50 total points
ID: 6253021
Strange, if I launch the following type of sql (using adodb) ...

"select distinct logs.wfstatistic.idstatistic from  logs.wfstatistic"

it generates a Dr. Watson. Whilst, if its like this...

"select distinct idstatistic from wfstatistic"

It works.

I don't know if this has any bearing on your problem, probably not, but interesting anyway.

Apart from that, I don't see any wrong with your code. I imagine it must be something to do with the Oracle ODBC driver and ADODB. Personally, I use ADODB 2.6 and Oracle ODBC driver

So I don't think I can ever reproduce your error. However, I'll keep watching any progress in your question.

Author Comment

ID: 6253052
OK... thanks. Actually, you verified something I wanted to check. I wanted to see if the Oracle ODBC Driver 8.1.6 worked with ADO and it seems to work for you.

I just found a comment at this link:

which says that Oracle won't support direct OLE-DB until Release 8.1. You're using 8.1.6. It also mentions that ADO is built to use OLE-DB, but by default, it goes through an OLE-DB/ODBC bridge.

Since, I'm using version 8.0.5 of the Oracle ODBC Driver, I don't think this OLE-DB/ODBC bridge is supported by my driver. This is probably my problem.

Thanks alot for your help guys!

Author Comment

ID: 6253063
I want to see if you guys agree with me after reading my comment above before I send over the points..

Thanks again for your time.

Expert Comment

ID: 6253096
Can't you download the new ODBC driver from somewhere? That's how we got it, but I don't know if was free or where it came from.
Yes, I forgot to mention that we use Oracle 8.1i and use OLEDB, but I use Oracle ODBC DSN's to connect with Interdev and sometimes for testing in ActiveX dll's instanced from VB.

Best of luck!!

Author Comment

ID: 6253118
Thanks for your time.. I'm pretty much convinced it's an issue with the version of the driver I'm using (8.0.5).

I'll have to surf around to see if there's downloads available with the more recent Oracle Drivers (8.1 or later).

Expert Comment

ID: 6253177
Thanks for the points, though I don't feel as I've been much help to you.

Check out the Oracle web site (ODBC drivers) at...


Author Comment

ID: 6253358
Thanks... I was having a devil of a time trying to find out where the page to download these drivers.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

691 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