Link to home
Start Free TrialLog in
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!
Avatar of omgang
omgang
Flag of United States of America image

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
Avatar of fieldj

ASKER

What is the DSN?
Avatar of fieldj

ASKER

Oh - data source name - I can confirm it is the Oracle 10g driver that is specified
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
Avatar of fieldj

ASKER

Oh, and just to confirm we have also tried using the MS Oracle driver and get the same error
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
...and is Office/Access 32-bit or 64-bit?
OM Gang
Avatar of fieldj

ASKER

Everything is 32bit
Well, that eliminates one possibility.
OM Gang
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
Avatar of fieldj

ASKER

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)
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
Avatar of fieldj

ASKER

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/
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
Avatar of fieldj

ASKER

ok thanks - apologies, I am not a DB guy!
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
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
Avatar of fieldj

ASKER

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
Avatar of fieldj

ASKER

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?
Avatar of fieldj

ASKER

I have noticed that the ORACLE_HOME environmental variable is blank.  Is this correct?
I believe so.  The ORACLE_HOME environment variable on my Win7 system is blank as well.
OM Gang
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
Avatar of fieldj

ASKER

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.

User generated image
Do you think this could be making any difference?
ASKER CERTIFIED SOLUTION
Avatar of omgang
omgang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fieldj

ASKER

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