DSN-less connection to an Oracle database.

Posted on 2000-03-27
Medium Priority
Last Modified: 2013-12-25
I need to open a read-only, DAO recordset from Oracle to pass to Crystal Reports, and I don't want to have to use a DSN.  When I try to use the code listed below to connect to my oracle database, I get the following error:

Run-time error '3146':

ODBC--call failed.

What is wrong with my code?

Option Explicit
Dim db As DAO.Database
Dim rs As DAO.Recordset
Private Sub Command1_Click()
Set db = OpenDatabase("", dbDriverNoPrompt, False, "DRIVER={Oracle ODBC Driver};UID=youruserid;PWD=yourpassword;DBQ=yourinstance.WORLD;DBA=W")
Set rs = db.OpenRecordset("Select * from DEPARTMENT", dbOpenSnapshot, 64)
While Not rs.EOF
    Me.Print rs.Fields(1).Value
End Sub

I am using Visual Basic 6.0 SP3.
Question by:smithdesigns
LVL 18

Expert Comment

ID: 2660895
Try adding ODBC; to the front of your connect string:

Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DRIVER={Oracle ODBC Driver};UID=youruserid;PWD=yourpassword;DBQ=yourinstance.WORLD;DBA=W")

Author Comment

ID: 2661155
Adjusted points from 150 to 175

Author Comment

ID: 2661156
Sorry, but I still get the same error on the "Set db = ..." line.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.


Accepted Solution

vujos earned 1000 total points
ID: 2661422


Error 3146 ODBC--call failed.
  This doesn't tell much. However, there is an Errors collection off of the DbEngine object that gives more information:

Try to do following code in some kind of OnError mode
For Each e In DBEngine.Errors
  Debug.Print e.number, e.description
It should print more info about the error.

You also can read following article.


Hope this helps
LVL 18

Expert Comment

ID: 2662125
OK, I copied and pasted your example, changed the obvious table names etc. and it runs fine.  So, your DAO syntax and connect string syntax is good.

I got a 3146 when I specified the name of a table that didn't exist.  Check to make sure that there is a table called DEPARTMENT in the instance of the database that you are logged into, and if it is not in the schema of the user id that you've logged in as, then it has to have a public synonym on it.  Also, the user id that you're logging into Oracle with, has to have Select privileges on that table.

Make sure that you are using the right name for your instance.  YourInstance.World has to be defined in your TNSNAMES.ORA.  Do a search of your hard drive to make sure that you don't have more than one copy of this file in your path, as you might be picking up the wrong copy of it.  Make sure that if the TNSNAMES.ORA file has HOST = myservername that you are able to ping the server by name and have it resolve to the I.P. address, other wise you have to change it to HOST = (whatever your Oracle Instance's I.P. address is).

Also, you may have a column in your table that the Oracle ODBC Driver is having trouble translating the data type for.  Try selecting some basic columns first (some varchar columns for example), and keep expanding the list until you hit one (maybe a NUMBER column) that is causing the blowup.  For example, maybe you have a large text/memo type of field that is blowing ODBC out.

Let me know if any of these ideas get you any further.

Expert Comment

ID: 2665636
The problem seems to be because of the driver you're referring to.
I assume the Run-time error '3146': ODBC--call failed error is occurring on the OpenDatabase method and not on the
OpenRecordset method.

Try the following:
Set db = OpenDatabase("", dbDriverNoPrompt, False, "DSN=datasourcename;UID=youruserid;PWD=yourpassword;DBQ=yourinstance.WORLD;DBA=W")

Allow the Provider to default, or if you want to specify a Provider do the following:
Set db = OpenDatabase("", dbDriverNoPrompt, False, "Provider=MSDAORA.1;DSN=datasourcename;UID=youruserid;PWD=yourpassword;Persist Security Info=False")



Author Comment

ID: 2670596
Adjusted points from 175 to 250

Author Comment

ID: 2670607
After much playing and perusing of the Oracle article and some other reading, I finally got it working.  Thank you.  I wish there was a button that would let me give everyone points.  I really appreciate all for your help.  (And I'm sorry it took me so long to get it.)

LVL 18

Expert Comment

ID: 2670898
Can you clue us (and anyone else who will read your question in the future) in to what your final solution was?  Thanks!

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

621 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