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

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?
Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>. 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike MillerSoftware EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Double check you downloaded the Basic and not the BasicLite.
0
Mike MillerSoftware EngineerAuthor Commented:
Yes, it's basic-not lite
0
slightwv (䄆 Netminder) Commented:
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
Mike MillerSoftware EngineerAuthor Commented:
Forcing the directory worked. Unfortunately, nothing has changed with regards to the error.
0
slightwv (䄆 Netminder) Commented:
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
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
Mike MillerSoftware EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Did you create the DSN with the 64Bit admin tool?
0
Mike MillerSoftware EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike MillerSoftware EngineerAuthor Commented:
Got it! Thanks for sticking with me today. Lifesaver!
0
slightwv (䄆 Netminder) Commented:
No problem.  Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.