Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.
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.


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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

604 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