Solved

DSN-less connection to an Oracle database.

Posted on 2000-03-27
9
780 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
Comment Utility
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
Comment Utility
Adjusted points from 150 to 175
0
 
LVL 1

Author Comment

by:smithdesigns
Comment Utility
Sorry, but I still get the same error on the "Set db = ..." line.
0
 
LVL 1

Accepted Solution

by:
vujos earned 250 total points
Comment Utility


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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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
Comment Utility
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
Comment Utility
Adjusted points from 175 to 250
0
 
LVL 1

Author Comment

by:smithdesigns
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now