Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

VB6 - Oracle 11g connection not working in app


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

  • 5
  • 4
1 Solution
slightwv (䄆 Netminder) Commented:
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.
Wilder1626Author Commented:
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;"
slightwv (䄆 Netminder) Commented:
>>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?
Industry Leaders: 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!

Wilder1626Author Commented:
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?
slightwv (䄆 Netminder) Commented:
>>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.
Wilder1626Author Commented:
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.
slightwv (䄆 Netminder) Commented:
>> 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.
Wilder1626Author Commented:

I will look at it and let you know shortly if i found something
Wilder1626Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now