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!
fieldjAsked:
Who is Participating?
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.

omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
What is the DSN?
0
fieldjAuthor Commented:
Oh - data source name - I can confirm it is the Oracle 10g driver that is specified
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
Oh, and just to confirm we have also tried using the MS Oracle driver and get the same error
0
omgangIT ManagerCommented:
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
0
omgangIT ManagerCommented:
...and is Office/Access 32-bit or 64-bit?
OM Gang
0
fieldjAuthor Commented:
Everything is 32bit
0
omgangIT ManagerCommented:
Well, that eliminates one possibility.
OM Gang
0
omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
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)
0
fieldjAuthor Commented:
0
omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
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/
0
omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
ok thanks - apologies, I am not a DB guy!
0
omgangIT ManagerCommented:
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
0
omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
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
0
fieldjAuthor Commented:
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?
0
fieldjAuthor Commented:
I have noticed that the ORACLE_HOME environmental variable is blank.  Is this correct?
0
omgangIT ManagerCommented:
I believe so.  The ORACLE_HOME environment variable on my Win7 system is blank as well.
OM Gang
0
omgangIT ManagerCommented:
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
0
fieldjAuthor Commented:
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?
0
omgangIT ManagerCommented:
I don't believe the extra space makes a difference.  Even the sample SQLNet.ora file from Oracle's FAQ page here http://www.orafaq.com/wiki/Sqlnet.ora has entries both ways.

If at all possible please try to confirm, exactly, what the ODBC Connect Str property is from the Win XP machine

OM Gang

ODBC Linked Table using DSN
0

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
fieldjAuthor Commented:
I am now unable to follow test this answer but appreciate all of your help
0
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.