Avatar of fieldj
fieldjFlag for United Kingdom of Great Britain and Northern Ireland asked on

Problems connecting Access to Oracle - ORA-12514 TNS Listener does not currently know of service requested in connect descriptor

We have a user who currently has an XP machine with Access 2003 and Oracle 8.1.  

We are currently in the process of moving them to Windows 7, Access 2003 and Oracle 10.2g but have hit a snag with the Access > Oracle ODBC connection.

On the old XP pc this is still working perfectly fine.  On the Windows7 PC they receive the error;

"ORA-12514 TNS Listener does not currently know of service requested in connect descriptor when attempting to run the same query."

If I go to Control Panel > Administrative Tools > Data Sources and check the ODBC connection, it connect fine.

I have check the tnsnames.ora file countless times and it looks correct.  I have even tried to directly copy the tnsnames.ora file directly from the old computer to the new computer and the connection from Access still fails.

The user has SQL developer and did a basic connection and query check and this worked fine, so it seems to only be something that is affecting Access.

Might something have changed between Oracle 8.1 and 10.2 that is causing this?

Please help!
Microsoft AccessOracle DatabaseMicrosoft SQL Server

Avatar of undefined
Last Comment
fieldj

8/22/2022 - Mon
omgang

I don't know if anything has changed but have connected to Oracle 10g database from both WinXP/Access 2003 and Wn7/Access 2007.  Which driver have you specified in the DSN?
OM Gang
ASKER
fieldj

What is the DSN?
ASKER
fieldj

Oh - data source name - I can confirm it is the Oracle 10g driver that is specified
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
omgang

Looking at the DSNs on my Win7/Access 2007 machine I see I've used the
Microsoft ODBC for Oracle
driver successfully.

I will try creating a new DSN using the Oracle Client.
OM Gang
ASKER
fieldj

Oh, and just to confirm we have also tried using the MS Oracle driver and get the same error
omgang

Just to eliminate one possibility, is the Win7 machine 32-bit or 64-bit?  Is the Oracle client installed 32-bit or 64-bit?
OM Gang
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
omgang

...and is Office/Access 32-bit or 64-bit?
OM Gang
ASKER
fieldj

Everything is 32bit
omgang

Well, that eliminates one possibility.
OM Gang
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
omgang

What, specifically, causes the error to be generated by the user?  Does the Access app utilize linked tables to the Oracle DB or pass-through queries or ????
OM Gang
ASKER
fieldj

I am not an expert, but I believe the Access query just needs to pull data from the Oracle DB.  

When you run the query you are prompted to enter the username/password (which works fine on the XP machine) and this is when the error appears

(please see attached)
ASKER
fieldj

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
omgang

Open the query in design view, then click the Ribbon button to open the Property Sheet for the query.  Let us know what's in the ODBC Connect St property.  Here's what I have in a pass-through query to an Oracle DB.  Note that this is not specifying a DSN but, instead, using a DSNless connection.
OM Gang
pass-throughquery.jpg
ASKER
fieldj

Its showing blank (but the user receives a username/password pop-up request (with the correct DB name).

Presumably you dont get this because you have the username/password hardcoded into this property/
omgang

If the ODBC Connect Str property is blank then the query is not a pass-through query direct to the Oracle database.  The query must be querying tables that are linked to the Oracle db.
Hold please.
OM Gang
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
fieldj

ok thanks - apologies, I am not a DB guy!
omgang

Here's a function to get the connect string for linked tables in the current db.  It's set up to return a two-dimensional array but you can modify it easily to output the connect strings to the Immediate window (I've added commented code to do so).

OM Gang


Public Function GetTableLinks() 'As String()
'retrieves table name and connection string for linked tables
'returns two dimensional array of strings
' 08/27/2010
On Error GoTo Err_GetTableLinks

    Dim tdf As DAO.TableDef
    Dim intRow As Integer, intColumn As Integer
    Dim arrLinks(12, 2) As String
   
    intRow = 0
    intColumn = 0
   
        'enumerate table defs collection in current db
    For Each tdf In CurrentDb.TableDefs
            'we're only concerned with linked tables
        If tdf.Attributes = dbAttachedTable Then
                'print table name and connect string to Immediate window
            Debug.Print tdf.Name & " --- " & tdf.Connect

                'populate two dimensional array with table def name and connection string
            'arrLinks(intRow, intColumn) = tdf.Name
            'arrLinks(intRow, intColumn + 1) = tdf.Connect
            intRow = intRow + 1
        End If
    Next

Exit_GetTableLinks:
        'destroy object variables
    Set tdf = Nothing
        'function return value
    'GetTableLinks = arrLinks
    Exit Function

Err_GetTableLinks:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function GetTableLinks of Module basTableLinks"
    Resume Exit_GetTableLinks

End Function
omgang

Let me know if you need instructions on how to use the function, where to paste/save it to, how to execute, etc.
OM Gang
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
fieldj

I appreciate your help, but I am not sure if its going to be practical for me to do this on the users PC.

This query works fine on the XP PC so I just need to try and identify yhy its not working on the W7 pc
ASKER
fieldj

Something that might be of interest....

This computer did previously have Oracle 11g installed, but we found out it had problems connecting with our DB so it was uninstalled and 10g put on instead.

Is it possible an environmental variable or registry setting is confusing something?
ASKER
fieldj

I have noticed that the ORACLE_HOME environmental variable is blank.  Is this correct?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
omgang

I believe so.  The ORACLE_HOME environment variable on my Win7 system is blank as well.
OM Gang
omgang

The code routine I provided will enumerate the tables in the Access db and print out, to the Immediate window, the ODBC connection string for any tables that have one, e.g. linked tables.  You can also select a linked table and open it in design view.  Once in design view select Property Sheet from the Ribbon.  The ODBC Connect Str will be displayed just like with the query.

The reason we need to know what the ODBC Connect Str:
It really sounds as if you are/were using a DSN for the ODBC connection on the Win XP machine.  If so, when you move the Access app to the new machine it is looking for the exact same DSN.  If the DSN doesn't exist you'd expect to receive an error.  If I am correct, you either need to change the name of the DSN on the new machine or, better yet, change the ODBC Connect Str property for each linked table so that it refers to the new DSN on the new machine.

OM Gang
ASKER
fieldj

But the DSN's are the same on both the new and old pc.  If I test the connections (using the same criteria) they do both connect successfully.

One thing I noticed was that the sqlnet.ora files differed slightly.

If you look at the attached image, notice the extra space on the sqlnet.ora file on the new Windows7 pc.

sqlnet.ora
Do you think this could be making any difference?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
omgang

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
fieldj

I am now unable to follow test this answer but appreciate all of your help