 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / Database Access / May 200680004005 error with Oracle
I have a difficult problem with an Oracle connection that works in some environments and not others. I have developed a spreadsheet which uses VBA and ADO to get data from an Oracle database. Development and testing worked fine, Development is on a standalone PC. Testing and production usage is on Citrix Servers, and this is where the problem starts to come in. On citrix1, the connection works exactly as required. On citrix2 and citrix3, we get Run-time error 80004005. All three servers use the same spreadsheet from a network drive and they all connect to the same Oracle server. The only difference is the OS environment on each server, and I have located a difference in dll's. In development, I set up a Reference to the ADO 2.5 Library, which translates to "msado15.dll". This functions exactly the same way on citrix1, but when checking citrix2 and citrix 3, it translated to "msado25.tlb". Further checking showed that citrix2 and citrix3 had 2.5, 2.6, and 2.7 versions of the ADO Library. "msado15.dll" does exist on citrix2 and citrix3, but when I tried to reference it manually using the browse function, it would not connect because there was already a reference to 2.5 and the "msado15.dll" said it was 2.7! So much for backward compatibility! Has anybody had any experience with this where backward compatibility was not actually backwardly compatible? I can't make the reference to 2.7 as I can't guarantee that all the systems will have version installed. Any suggestions? Darryl > I have a difficult problem with an Oracle connection that works in some > environments and not others. [quoted text clipped - 25 lines] > > Darryl I believe the technical definition of your problem is called "Chewing on the wrong animal." The ADO Error 80004005 has many specific causes but they are all related to an inability to make a "Connection". If you have installed the correct MDAC for your platform then it is highly unlikely you are experiencing any "backward compatibility" issues. It is more likely you are experiencing a "permissions" problem either in accessing Oracle or in accessing the COM components. Stop worrying about MDAC and start looking at why your app can't "see" oracle. hth -ralph > > I have a difficult problem with an Oracle connection that works in some > > environments and not others. [quoted text clipped - 46 lines] > hth > -ralph Here is a website that provides a good summary of all the issues that can cause this error: http://www.aspfaq.com/show.asp?id=2009 Also while the Error Code is the same - there are quite different Error Descriptions. What is the one for your error? Also I suggest you download and use Oracle's OLE DB provider for the version of Oracle you are using. -ralph Ralph, no joy with any of your suggestions. I had already checked permissions and on all the files that I could see were relevant, the permissions were identical. The web site you referenced deals with SQL Server and Access, and I couldn't see anything relevant to the particular Oracle problem that we have. The text of the message is "Oracle error occurred, but error message could not be retrieved from Oracle." I have been working on Citrix 1 and 2 and ignoring 3 for the moment. On both servers, I can connect to the Oracle databases directly via sqlplus, which tells me that all the Oracle database and networking is functioning correctly. I have also found that the Citrix 2 VBA is not making the connection to the listener on the database server, where both Citrix 1 and sqlplus on Citrix 2 do connect. Somewhere between connection.open and the Oracle host listener is where the problem occurs. I have also swapped from ver 2.5 to 2.7 on the problem machine, and the situation is unchanged. Another curious piece of the puzzle i have just found out, if you run outside of debug mode and click on "End" when the error message comes up, the data is loaded anyway! I don't think it is a good idea though to get users used to ignoring error messages. > > > Darryl > > [quoted text clipped - 27 lines] > > -ralph > Ralph, > [quoted text clipped - 52 lines] > > > > -ralph I agree. Providing instructions to ignore error messages does usually install much customer convidence. <g> ADO is a data access library which works with an Oracle OLE DB provider to access the Oracle database. The fact that sqlplus works only indicates that Oracle is installed - not whether the ADO provider and Oracle are configured correctly. By "permissions" I was referring to the whole gambit of properties associated with a particular "Profile" which includes not only ACLs, but environ variables, registery entries, logical paths, etc. The fact you can still receive data leads me to suspect some kind of datatype issue (eg, the ToDate() fix mentioned in a link below.) http://www.intranetjournal.com/ix/msg/39969.html http://support.microsoft.com/kb/q264012/ http://www.bauerfoto.com/OracleConnectFix_EnvVariable.html Oracle OLE DB providers: http://www.oracle.com/technology/software/index.html And now for the silly question - you did install the Oracle Client on the Citrix 2/3 servers using /install mode, right? -ralph I have trouble believing that it is a data type problem because the exact same spreadsheet/VBA works 100% correctly on Citrix 1. Thus I believe the is external to the spreadsheet. I am fairly convinced that the problem lies in the configuration on Citrix 2 and is external to both the spreadsheet and to Oracle networking on the host system. I have then narrowed down the search to somewhere between "connection.Open" method in the VBA code and the Oracle listener on the host. Given that the only change between Citrix 1 and 2 is the machine and OS itself, it has to be an environmental problem somewhere. After sending the previous and searching the system further, there is some evidence that the Oracle clients may not have been cleanly installed. I have passed this on to the manager responsible for installing them and asked him to check it out. A corrupted registry or similar could explain the problem. > I agree. Providing instructions to ignore error messages does usually > install much customer convidence. <g> [quoted text clipped - 22 lines] > > -ralph > I have trouble believing that it is a data type problem because the exact > same spreadsheet/VBA works 100% correctly on Citrix 1. Thus I believe the is [quoted text clipped - 36 lines] > > > > -ralph If you ever find out, please post back. -ralph |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.