I'm attempting to set up an Oracle database connection in Excel 2007. To do this, I'm selecting "From Other Sources" from the "Data" menu. I then select "From Data Connection Wizard". In the wizard, I select "Other/Advanced". The Data Link Properties dialog opens. I select "Oracle Provider for OLE DB" as the Provider. On the "Connection" tab, I enter the name of the database server into the "Data Source" field, the User Name and the Password. I then test the connection. I was able to get this working once, and I then saved the connection as an ".odc" file. However, since that one time of getting it to work, I have been unable to get it working again, even though I'm entering the exact same connection information. I consistently get this error:
"Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified"
I get the same behavior if I select "Microsoft OLE DB Provider for Oracle" as the provider. What gives? Is there some setting in Excel that I need to set to get this to work? It worked once, so it should work again, right?
On a related issue, when I got it working the first time, it gave me a list of tables from the database to which I could connect as the data source. However, I want to pull data from a VIEW, not a TABLE. I thought I could do this through VBA code, but I couldn't get the Oracle connection to work there, either.