fieldj
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!
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!
ASKER
What is the DSN?
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
Microsoft ODBC for Oracle
driver successfully.
I will try creating a new DSN using the Oracle Client.
OM Gang
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
OM Gang
...and is Office/Access 32-bit or 64-bit?
OM Gang
OM Gang
ASKER
Everything is 32bit
Well, that eliminates one possibility.
OM Gang
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
OM Gang
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)
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
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
OM Gang
pass-throughquery.jpg
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/
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
Hold please.
OM Gang
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
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
OM Gang
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
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
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?
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
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
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
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
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.
Do you think this could be making any difference?
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.
Do you think this could be making any difference?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am now unable to follow test this answer but appreciate all of your help
OM Gang