4 Replies Latest reply on Jun 21, 2010 2:03 AM by 716107

    Oracle 10g ODBC driver with Windows 7 32 bit connecting to Excel

    716107
      Hi Everyone,

      I'm having an issue connecting to our oracle 10g database (64 bit system) from a 32 bit windows 7 installation running excel 2010/2007 using odbc drivers. Our excel spreadsheets worked without issue in windows xp.

      In windows 7 I have been able to install the odbc driver via the following method:
      1. Download the oracle 'basic' client and oracle 'odbc' client from www.oracle.com and extract the contents:
      instantclient-basic-win32-10.2.0.3-20061115
      instantclient-odbc-win32-10.2.0.3-20061115
      2. Create a folder 'oracle' and place the instantclient_10_2 folder inside.
      3. Ensure all the basic and odbc files reside in this folder.
      4. Create a 'network' folder within instantclient_10_2
      5. Create an 'admin' folder within 'network'
      6. Create sqlnet.ora and tnsnames.ora files within the 'admin' folder:
      7. Run 'obdc_install' within the instantclient_10_2 folder
      8. Download and run the oracle odbc driver exe file from www.oracle.com (ORA10203.exe)
      9. Go to Control Panel -> Administrative Tools -> Data Sources and create a new System DN

      This allows excel spreadsheets connecting ONLY to oracle to function.

      Unfortunately some of our spreadsheets connect to both a MySQL database as well as oracle. Spreadsheets connecting only to MySQL also function correctly, however, as soon as the vb associated with a spreadsheet includes a mysql call followed by an oracle call the spreadsheets fail. In Excel 2007 excel crashes completely everytime. In Excel 2010 the mysql data is generated, then when the script hits to oracle odbc connection there is a long pause, the oracle ODBC driver connect pops up requesting a username/password (this didn't happen in xp), then the data source selection pops up, then the username/password connect screen pops up again, and finally it fails with a runtime error '1004' General ODBC error.

      If you look at the VB script it's failing at the Refresh BackgroundQuery statement in the ODBC connection block:

      With Worksheets("Oracle1").QueryTables.Add(Connection:= _
      "ODBC;DSN=Oracle_ODBC;UID=user;PWD=password;SERVER=ORACLE_LINUX;", _
      Destination:=Worksheets("Oracle1").Range("A1"))
      .CommandText = strSql
      .Name = "Oracle1_data"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlOverwriteCells
      .SavePassword = True
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .Refresh BackgroundQuery:=False
      End With


      Does anyone know how to get around this? It's very important!!

      Also if you try to edit the oracle ODBC connections in Control Panels -> Administrative Tools -> Data Sources, ODBC crashes 95% of the time.

      Advice greatly appreciated!!