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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
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.

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)
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.