VB6 - Oracle 11g connection not working in app

Posted on 2014-10-22
Medium Priority
Last Modified: 2014-10-25

I have this below code i'm using under Oracle 9i to connect my VB6 to an oracle table.

Now that i'm on Oracle OraClient11g_home1, i can't anymore.

How should I fix the code to connect the same way as i used to, but using OraClient11g_home1?

Thanks again

Private Sub Command1_Click()
    MSHFlexGrid1.Rows = 2
    MSHFlexGrid1.Cols = 2

    On Error Resume Next
    Dim oconn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer

     'SQL query detail:
    strSQL = "SELECT * FROM tmmgr.location"

    Set oconn = New ADODB.Connection
   ' oconn.Open "Provider=OraOLEDB.Oracle.1;Data Source=TEST_ENVIRONMENT;User Id=XXXXXXX;Password=XXXXXXXX;"
    RS.CursorType = adOpenStatic
    RS.CursorLocation = adUseClient
    RS.LockType = adLockOptimistic
    RS.Open strSQL, oconn, adCmdText
    Set MSHFlexGrid1.DataSource = RS
End Sub

Open in new window

Question by:Wilder1626
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
  • 5
  • 4
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397125
OleDB connections haven't changed since I stopped using them years ago.

Make sure you have the 11g OleDB drivers installed.

The '.1' seems odd to me.  Try just OraOLEDB.Oracle.
LVL 11

Author Comment

ID: 40397344
How can i validate for the 11g OleDB drivers?

I actually tried like this but it does not pull anything. and no error

oconn.Open "Provider=OraOLEDB.Oracle;Data Source=TEST.ENVIRONMENT;User ID=XXXXXXX;Password=XXXXXXX;"
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397359
>>How can i validate for the 11g OleDB drivers?

If you are using the regular Oracle client, run the installer and look at the installed options.  Look for OleDB

If you are using the Intant Cloent, look for the DLLs.  Not sure where in the registry to look to see that they have been properly installed.

>> actually tried like this but it does not pull anything. and no error

Well, no error means to me it is finding the drivers.

The first post had: TEST_ENVIRONMENT.  The latest one has TEST.ENVIRONMENT.

What is in the tnsnames.ora file?
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 11

Author Comment

ID: 40397463
well actually, the true name is G.ENVIRONMENT. Sorry for the confusion.

oconn.Open "Provider=OraOLEDB.Oracle;Data Source=G.ENVIRONMENT;User ID=XXXXX;Password=XXXXX;"

So normally, no errors = driver installed. Correct?
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397472
>>So normally, no errors = driver installed. Correct?

Cannot say for sure but it seems like a reasonable assumption to me.

You didn't say what the error was that you were receiving but if it was something like "driver not found" and that error went away, it mush have found something.
LVL 11

Author Comment

ID: 40397551
i don't have any errors. It just don't populate the data into the grid.

Is there a way to add into the connection string those below details:
Host Name
Port Number
Service Naming
User ID

Normally, with all those details, i should connect.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 40397564
>> It just don't populate the data into the grid.

Then one of two things:
You aren't connecting to the database you think you are.
The query you are running isn't returning any rows.

>>Is there a way to add into the connection string those below details:

I believe you can use EZConnect with OleDB connections.  I've seen references to it when I've Googled around.

Typically you rely on the tnsnames.ora file to provide all that information (well the server info, not the username and password).

Check out:

Connection Setup Quick Start
 There are a number of methods to connect Oracle client to a database server. Two of the most common include EZCONNECT and TNSNAMES. EZCONNECT is the easiest to setup. TNSNAMES is much more maintainable in the long term. If you are new to Oracle, we recommend you use EZCONNECT. You only have to choose one or the other to connect.  
These quick start instructions assume you have a valid username and password for the database server.
LVL 11

Author Comment

ID: 40397723

I will look at it and let you know shortly if i found something
LVL 11

Author Closing Comment

ID: 40403888
Thank you so much. I found out that i had some dll issue with oracle. I re-installed Oracle and now it work.

Thanks again for your end.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article will show, step by step, how to integrate R code into a R Sweave document
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

752 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