Oracle to Access ODBC drivers on XP

Posted on 2006-03-20
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
    LVL 7

    Accepted Solution


    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:

    LVL 1

    Author Comment

    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?
    LVL 7

    Expert Comment


    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.

    LVL 1

    Author Comment

    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)
    LVL 7

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now