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

x
?
Solved

DSN-less connection to an Oracle database.

Posted on 2000-03-27
9
Medium Priority
?
798 Views
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)
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.
0
Comment
Question by:smithdesigns
9 Comments
 
LVL 18

Expert Comment

by:mdougan
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")
0
 
LVL 1

Author Comment

by:smithdesigns
ID: 2661155
Adjusted points from 150 to 175
0
 
LVL 1

Author Comment

by:smithdesigns
ID: 2661156
Sorry, but I still get the same error on the "Set db = ..." line.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Accepted Solution

by:
vujos earned 1000 total points
ID: 2661422


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
 
LVL 18

Expert Comment

by:mdougan
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 = 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
 
LVL 4

Expert Comment

by:gcs001
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")

HTH

Regards,
Grant.
0
 
LVL 1

Author Comment

by:smithdesigns
ID: 2670596
Adjusted points from 175 to 250
0
 
LVL 1

Author Comment

by:smithdesigns
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.)

Brad
0
 
LVL 18

Expert Comment

by:mdougan
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!
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

578 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