[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel macro connecting to remote Oracle DB on 64 bit server problem / connection error

Posted on 2014-08-05
16
Medium Priority
?
4,005 Views
1 Endorsement
Last Modified: 2016-03-16
I have an Excel (97-2003) spreadsheet with a macro that connects to an Oracle database using the following code.

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=shaft)(PORT=1521))" & _
"(CONNECT_DATA=(SID=WM))); uid=ball; pwd=sack;"
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = strCon
conn.Open

Open in new window


The Oracle DB is on a Windows Server 2008 R2 machine (64 bit). The Excel spreadsheet, I'd like to run remotely from any machine. Right now I'm testing on a Windows 7 (64 bit) PC.

When I run this spreadsheet (either directly on the server or from any remote machine) I receive the following errors:

The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation are part of the Oracle Version 7.3 (or greater) client software installation.

You will be unable to use this driver until these components have been installed.


and...

Run-time error '-2147467259 (80004005)':

[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed


The errors occur on this line:

conn.Open

Open in new window


I have tried installing several things based on advice from other forums, none of which have changed the resulting error.

What do I need to do here?
1
Comment
Question by:Mike Miller
  • 8
  • 7
16 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241217
What bit version of Excel?

You'll need the same bit version of the Oracle ODBC drivers.

I would also suggest you NOT use the Microsoft drivers and migrate to the Oracle native drivers.

The Instant Client should work.  you'll need two downloads:  The basic and the ODBC add-on.

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
0
 

Author Comment

by:Mike Miller
ID: 40241345
64-bit Excel

I followed the instructions for installation provided by Oracle.

Deployed the files in the basic installation, set the environment variable, then ran the exe for the add-on. When running the exe, a command window just pops up then closes. Not long enough for me to see the command. Still receiving the error though.

Is there something additional I need to do because it's a 32-bit driver being installed on a 64-bit server?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241456
>>. Not long enough for me to see the command.

Run from a CMD prompt not by clicking on it.

>>Is there something additional I need to do because it's a 32-bit driver being installed on a 64-bit server?

32Bit driver?  If Excel is 64Bit, you need the 64Bit drivers.

MSoft also has two different ODBC admin tools a 32Bit and 64Bit.  You need to run the correct one:
http://support.microsoft.com/kb/942976

•The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
•The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Mike Miller
ID: 40241667
Ok, I downloaded Instant Client Package - Basic (x64) and deployed all of the files to C:\oracle\instantclient
I revised the environment variable to reflect this directory
I downloaded *Instant Client Package - ODBC (x64) and deployed all of those files to C:\oracle\instantclient
From a command window I ran C:\oracle\instantclient\odbc_install.exe and received:
"Couldn't find Oracle Instant Client in present directory."

Any idea what it's looking for specifically?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241804
Double check you downloaded the Basic and not the BasicLite.
0
 

Author Comment

by:Mike Miller
ID: 40241810
Yes, it's basic-not lite
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241824
I've never had a problem.  Unzip everything to the same folder, run the install and it works.

Tripple check everything is in the same folder.

You might also force the PATH just in case:
cd C:\oracle\instantclient
.\odbc_install.exe
0
 

Author Comment

by:Mike Miller
ID: 40241857
Forcing the directory worked. Unfortunately, nothing has changed with regards to the error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241865
Did you set up the DSN?

Did you change the connect string?

Take a look at examples here:
http://www.connectionstrings.com/oracle-in-oraclient11g_home1/
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 40241954
if still you are getting the same error i.e

>>[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed<<

These error may be because its not finding the correct oracle libraries, so can you check your ORACLE_HOME set in  environment variable path.
0
 

Author Comment

by:Mike Miller
ID: 40241991
Here is my code:

 strCon = "Driver={Oracle in instantclient_12_1x64};Dbq=ParetoOracle;Uid=ball;Pwd=sack;"
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.ConnectionString = strCon
    conn.Open

Open in new window


Here is what my DSN looks like:
http://bit.ly/1kl0uxY

And the error I'm getting now is:

Run-time error '-2147467259 (80004005)':

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified


Making progress...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40241995
Did you create the DSN with the 64Bit admin tool?
0
 

Author Comment

by:Mike Miller
ID: 40242012
If this line from the article is correct, then yes.

The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40242022
The docs are always great for reference...

http://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#BABEFBFB

DBQ is the tnsnames.ora entry.
DSN is the attribute for, well, the DSN.

Try this:
strCon = "Driver={Oracle in instantclient_12_1x64};DSN=ParetoOracle;Uid=ball;Pwd=sack;"

Of keep it DBQ and provide the tns alias.
0
 

Author Closing Comment

by:Mike Miller
ID: 40242319
Got it! Thanks for sticking with me today. Lifesaver!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40242421
No problem.  Glad to help.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question