Oracle to Access ODBC drivers on XP

Posted on 2006-03-20
Medium Priority
Last Modified: 2008-01-09
I'm trying to connect My database to an Oracle database, but don't fully understand what I'm doing. I have NO knowledge of Oracle (and no permissions) - I will need to ask a distant and unhelpful IT department if anthing needs to be checked/sorted at the Oracle end.  This is on XP with Access2003

I have downloaded and installed

This has given me
Microsoft ODBC for Oracle MSORCL32.dll
Then in the Systen DNS tab I have added the above driver and this has given me a set of options
Data Source Name
User Name

I don't know what to put here, or what to ask the IT department.

On an older version of Access on an older machine, we connected to Oracle using an Oracle73 Ver2.5 driver
This had a different set of options:
This had an Oracle connect string of TNS:CFDB
but no server name is mentioned.

Please could someone give me some advice, or if I am short of information, what specifically to ask the IT department?

Question by:susannahbond
  • 3
  • 2

Accepted Solution

IT-Schubertz earned 750 total points
ID: 16234412

I am not an Oracle expert myself, but I try to fill you in the basics.

"Data Source Name" is the name of the database you want to connect to. User Name and Description are self explaining (UserName and a description of the db, the description part can be left unfilled).
Server: this normally is the Oracle Server Name.
Normally, the setup of an Oracle instance is a bit different from MS SQL Server.
When using SQL Server, you enter the name of the Server directly (e.g. MyDBServer, or MyDBServer\SQL2005).
Oracle uses Net Service Names, also known as TNS alias. This is when a SQL* TCP/IP listener is active.
So you would have to ask your admins about the Name or TNS alias of your Oracle Server.
if you get back a Server name, you just fill that in, if you get a TNS alias from them, you fill into the "server" field : TNS:TheAliasYouGet

read also: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/network.901/a90154/gettings.htm#483215


Author Comment

ID: 16235430
On the old version it had a data source name of CFDB
And an Oracle connect string of TNS:CFDB
So I have put the name in and
TNS:CFDB in the Server box
I have attached Oracle tables in the database I have upgraded from the '97 version.
When I try to open one, I get ODBC call failed.
When I try to attach a new table I get:

ODBC--call failed.
[Microsoft][ODBC driver for Oracle][Oracle]ORA-03121:no interface driver connected - function not performed (#3121)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed IM006 0 [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (#0)

Any Idea what this might mean?

Expert Comment

ID: 16235684

hm, as I said, I am no oracle guy. Have you tried to just put the CFDB as Server?
What you could try too is: create a system DSN to the oracle database and access it from your code.


Author Comment

ID: 16236786
Tried CFDB, no joy

Have found this on the error

I don't know anything about an Oracle Client,
(I have one loaded on my machine but it states it is for NT not XP so I think I'll pop over to the Oracle area and ask another question)

Expert Comment

ID: 16237182
one more try:

in your connection string, remove the "server" part, and change the "data source=" to "data source = TNS:CFDB"
if that won't work, find out the oracle server name

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 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