DSN-less connection to an Oracle database.

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)
Me.Cls
While Not rs.EOF
    Me.Print rs.Fields(1).Value
    rs.MoveNext
Wend
rs.Close
db.Close
End Sub

I am using Visual Basic 6.0 SP3.
LVL 1
smithdesignsAsked:
Who is Participating?
 
vujosConnect With a Mentor Commented:


Hi,

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
Next
 
It should print more info about the error.

You also can read following article.

http://www.oracle.it/support/alert_tech/sup-al3.htm

Hope this helps
0
 
mdouganCommented:
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")
0
 
smithdesignsAuthor Commented:
Adjusted points from 150 to 175
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
smithdesignsAuthor Commented:
Sorry, but I still get the same error on the "Set db = ..." line.
0
 
mdouganCommented:
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 = 170.66.34.1 (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.
0
 
gcs001Commented:
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")

HTH

Regards,
Grant.
0
 
smithdesignsAuthor Commented:
Adjusted points from 175 to 250
0
 
smithdesignsAuthor Commented:
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.)

Brad
0
 
mdouganCommented:
Can you clue us (and anyone else who will read your question in the future) in to what your final solution was?  Thanks!
0
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.

All Courses

From novice to tech pro — start learning today.