-
Title
ODBC Mcrosoft Access Driver error when trying to run heterogeneous query in Query Builder using 1 -
Description
When Trying to link an access table to an oracle table in the query bulder, I get the following error message...
Error 9/9/2009 3:35:48 PM 0:00:00.421 [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error. 1 0
When I send the code to the Editor this is the message I get...
Error 9/9/2009 3:54:56 PM 0:00:00.328 Toad for Data Analysts: ORA-00972: identifier is too long 1 0 -
Resolution
When TDA creates a heterogeneous query that contains an Oracle table, an ODBC connection string is created for the Oracle database and the query is executed using the Access connection. Access uses the ODBC connection string to get the data from the Oracle table which is then merged with the data from the Access table.
The ODBC connection to Oracle defaults to using the "Microsoft ODBC for Oracle" driver. The Microsoft driver can not connect to Oracle 11g. If the user is using TDA v2.5, they must open the Connection Properties dialog for the Oracle connection, click on the Advanced tab, and select the appropriate Oracle 11g ODBC driver that came with their Oracle client installation media.
It must be said here that the Oracle 11g ODBC driver sometimes does not work with heterogeneous queries. We dont know why but we are looking into it.
If the user has an older version of TDA, they must open the registry editor and rename the "Microsoft ODBC for Oracle" driver to some other name so TDA will search for another driver or ask the user to specify an ODBC driver.
Run regedit.exe
Go to:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle
Rename "Microsoft ODBC for Oracle" to "Microsoft ODBC for Oracle_old" -
Additional Information
A user was able to get it to work by connecting through ODBC, instead of TNSnames, but it was so slow it was unusable. I informed development of this and they said, �??If using an ODBC connection works then that is what the customer needs to use. I know it is slow but it would not be any faster using a normal Oracle connection because the connection is converted to ODBC in the background�??.