<p>
I cannot make an ODBC connection from Exce-2007 to Oracle work. I am an expert Excel and VBA user (since 1994) and I have frequently used Excel to access ODBC databases, including Oracle (I have done this with Excel-2003 both with worksheet queries and have written VBA ADO-connection routines). And even though in Excel-2007 a worksheet ODBC query is supposed to be easier to create than in previous version of Excel, my connection fails. Any suggestions and all help are welcome and much appreciated.
DETAILS
-------------
</p>
<p>
1) <strong>What is my system?</strong> I am using Excel-2007 on Windows Vista x64 and Oracle server v.11g on my computer (all this is on my computer, no network issues).
2)<strong> Why use Excel with Oracle at all?</strong> I use Excel-2007 to access Oracle rather than Access-2007 (or any other application like TOAD, etc.) because I do engineering calculations with the data stored in Oracle. These calculations are easier done in Excel (I suppose that one alternative to this could be to use some sql or Access to get the data from the database, then store it as plain vanilla CSV file, then open this file in Excel, then do the math (the math involves complex optimisation algorithms), then save the results as CSV, then use some sql or Access to put the data back into the database. Howwever this does not strike me as a quick or neat solution. And after all Excel has been designed to access ODBC databases, so why not use it?)
3) <strong>What do I do in Excel-2007 that won't work?</strong> I create an ODBC link to Oracle that does not work. In Excel-2007 this is straightforward:
</p>
<ul>
<li>define an ODBC connection (Data tab --> From other sources --> From data connection wizard);</li>
<li>define a query on the worksheet -- that's it, this is all!</li>
</ul>
<p>
I start with creating an ODBC connection:
a) I choose an ODBC data source type: <strong><em>ODBC DSN</em></strong>
b) Excel-2007 displays the list of the available ODBC data sources. I see in it <strong><em>my Oracle database name</em></strong> and I select it.
c) Excel-2007 displays the Data Link Properties:
- the "Provider" has a list of the OLE DB drivers with preselected "<strong><em>Microsoft OLE DB Provider for ODBC Drivers</em></strong>". I keep this default selection.
- the "Connection" tab has a connection string "<strong><em>DSN=<my database name></em></strong>" which I keep, it also has fields for the <strong><em>user name</em></strong> and the <strong><em>password</em></strong>, which I fill with the correct credentials.
- Finally there is a button "Test Connection", which when I click produces the following error message:
<strong><font color="#ff0000">"Test connection failed because of an error in initializing provider.</font></strong><strong><br />
</strong><strong><font color="#ff0000">Unespecified error"</font></strong><strong>
</strong>
4) <strong>Additional food for thought:</strong>
a) In the above walk-through the only data, which I type, are the user name and password, everything else is selected from lists offered by Excel-2007, hence any possibility of typos being the cause of the problem can safely be discarded.
b) I can test the ODBC driver in the Control Panel and it shows that it can connect to the Oracle database:
- in <strong><em>Control Panel --> Admin Tools --> Data Sources (ODBC)</em></strong> on the "User DSN" tab I can see the list of the available ODBC data sources (same list as in Excel-2007, point 3b above) with the name of my database in it;
- selecting the name of my database from the list of the sources and clicking "Configure" button opens a tab with <strong><em>Data Source Name</em></strong> (same as in Excel-2007), TNS Service Name and User ID. I enter <strong><em><user name>/<password></em></strong> and click "Test Connection" button. A message "<strong><em>Connection successful</em></strong>" appears (just for a test I enter <u>incorrect user credentials</u> and "<strong><em>Unable to connect</em></strong>" message appears)
BOTTOM LINE
---------------------
</p>
<p>
The procedure for using an ODBC connection from Excel is very simple, in the past I have created and used such connections numerous times with Excel-2003 and earlier on Win-XP and earlier. But now on Excel-2007 and Vista-x64 I cannot make it work.
Also, testing an ODBC connection driver is really easy and simple to be done in the Control Panel. There testing the same ODBC connection, which fails in Excel-2007, results in success.
I am frustrated by the simplicity of the problem and yet the persistant error. I have lost now two full days in failed attempts to make the simple procedure work and in searching the internet for answers.
All help is highly appreciated <img class="emoticon" src="images/emoticons/happy.gif" border="0" alt="" width="16" height="16" />,
Plamen
</p>