Apache/Perl Cannot Find MDAC without CommonProgramFiles(x86)

Question

I am having a problem with using Apache/Perl to get access to Excel files using Microsoft Data Access Component (MDAC). Somehow I must set the "CommonProgramFiles(x86)" system environment variable in order to get this to work. Otherwise, I keep getting this error message:

System.InvalidOperationException: The .Net Framework Data Providers require Microsoft Data Access Components(MDAC). Please install Microsoft Data Access Components(MDAC) version 2.6 or later. ---> System.IO.FileNotFoundException: Retrieving the COM class factory for component with CLSID {2206CDB2-19C1-11D1-89E0-00C04FD7A829} failed due to the following error: 8007007e.

The server configuration is:

• Windows Server 2008 R2 in 64-bit

• Server is installed with Microsoft Access Database Engine 2010

• Apache 2.2.25 (that is 32-bit)

• Perl 5.12.3 v5 (that is also in 32-bit)

• I have my Perl CGI script to call my C# program (that is built for "Any CPU").

• The C# program uses MDAC to open and read Excel files (not trying to launch Excel, only try to read data from the Excel files).

I have verified that the server has the latest MDAC available in these 2 folders:

C:\Program Files\Common Files\System\Ole DB
C:\Program Files (x86)\Common Files\System\Ole DB

I have also checked the registries and they look fine. Anyway, I don't have any problem running my C# program directly at the command prompt (it can use MDAC to get access to Excel files). I only have the problem when I use Apache/Perl to use my Perl CGI script to call my C# program (that is when I get that error with MDAC).

I can work around this problem by specifying CommonProgramFiles(x86) in my Perl CGI script, like this:

$ENV{ "CommonProgramFiles(x86)" } = "C:\\Program Files (x86)\\Common Files";

I have this question:

• Why do I have this problem? And why setting that CommonProgramFiles(x86) system environment variable can workaround this problem? Why that system environment variable is empty before I set it? Does this have to do with the fact that I am running 32-bit Apache/Perl in a Windows operating system that is 64-bit?

Please help me to understand this issue. Thanks in advance.

(The original version of this post had a question about a second problem. Turned out that problem had to do with an extra double quote in the string. I fixed this, and that problem has gone away. That's why I have removed that second question from the post)

Jay Chan

Answer 1

I did some more research and the issue is the following: until release 2.4.9, the Apache startup routines have mapped "commonprogramfiles(x86)" to "commonprogramfiles_x86_" and that variable does not exists in the environment unless you create it... I have not tested it, but creating that environment variable and making it point to the same location as commonprogramfiles(x86) would probably fix the issue too.

Since the compiled Apache distributions are only using up to version 2.4.46 as we speak, they don't have the fix that allows the parenthesis in environment variables. That's why you still need the PassEnv directive to ensure that Apache passes the correct values to 32-bit CGI scripts.

The following post has some useful details about this: https://bz.apache.org/bugzilla/show_bug.cgi?id=46751

Answer 2

I used to have the same problem in with Apache 2.4 with dBase compiled apps using ADO-32 bit as dBase is 32-bit. Something has recently changed, possibly with Windows 10 2004 20H2. I needed this fix in July-Aug 2020 but now I don't, the environment variable already exists. Since my Apache version is dated April 2020, that cannot be the reason for the change.

I tried to do some research about this but all I could find is that those environment variables are system ones existing at least since 2017, so why I needed to set this var is a mystery to me, but I would like to understand this, so if you find something, post a follow-up...

https://learn.microsoft.com/en-us/windows/deployment/usmt/usmt-recognized-environment-variables