Oracle to Access ODBC drivers on XP

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
MDAC 2.8RTM

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
Description
User Name
Server

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

History:
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?


LVL 1
susannahbondAsked:
Who is Participating?
 
IT-SchubertzCommented:
Hi,

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
HTH

Andy
0
 
susannahbondAuthor Commented:
Hi
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?
0
 
IT-SchubertzCommented:
hi,

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.

0
 
susannahbondAuthor Commented:
Tried CFDB, no joy

Have found this on the error
http://www.cryer.co.uk/brian/oracle/ORA03121.htm

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)
0
 
IT-SchubertzCommented:
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
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.