We have an Oracle DB supplied by a third-party which is hosted internally but we know very little about. I need to connect to that DB with both MS Access and MS Excel, and I am having a very hard time getting things working. I am running Windows 7 64-bit, and have installed the win64_11gR2_client.zip file which gave me the "*OraClient11g_home1*" driver for my ODBC connection. I am trying to create the connection using the odbcad32.exe file found in the System32 folder. Have I missed any required steps so far?
I then attempted to create the ODBC connection (copying information found on another computer which had its ODBC connection set up by someone who is no longer here), but haven't been able to get it to work. When I test the connection, I get an error stating that it could not resolve the connection identifier (which I'm assuming refers to the TNS Service Name). Where is this name defined? Does it have to be set in a tnsnames.ora file before the ODBC connection will work? The other computer has a folder named "*dbhome_3*" which contains a tnsnames.ora file, but my computer doesn't have that folder. When is it created?
Once I have this connection working, is that all I will need in order to connect to the DB with Access and Excel? What if I want to install the SQL Developer application and modify the DB remotely? Is anything else required?
Any suggestions would be greatly appreciated. I haven't had much exposure to Oracle before, and this is proving more difficult than I hoped it would be.
Edited by: 1002458 on 25-Apr-2013 5:11 AM
Okay, there's a few potential issues here.
1. Yes, you need a tnsnames.ora file, as it has the server connection information. If you have a working system that already has a tnsnames.ora file, you can just copy it over and it should work (grab sqnet.ora too). Both of those files go in the \network\admin folder, which is inside the folder where you installed Oracle.
2. Once you get that working, get your ODBC connection set up. I've seen a bug in this version of Oracle where no servers appear in the dropdown int he ODBC administrator, but you can just type the name in if you know what it is. If you copied a tnsnames.ora file from another system, it'll be whatever name their ODBC uses. If not, you can look in the file for the names.
3. With all that being said... are you using 64 bit MS Office? If you're using 32 bit (and almost everyone is), you'll actually need a 32 bit Oracle client, and to use the ODBC administrator tool in c:\windows\syswow64\odbcad32.exe
The reason why is that 32 bit applications can't use a 64 bit ODBC connection, and you can't make a 32 bit ODBC connection using a 64 bit Oracle client. They all have to line up.