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
  • 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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
How to debug this code 7 65
passing a value with stream reader AFTER a ";" 3 82
Set email body to html using vbscript 6 69
Set WorkSheet  not Working 9 50
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

680 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