Microsoft ACE Driver

Porting an application irrespective of the hardware or an framework upgrade can teach us a lot, sometimes it can get painful to arrive at the solution that meets the recommendations from Microsoft and the clients. There are many aspects that drives the solution and for me this time it’s the “Time” and “Cost”.

I was porting an MVC app to the new server – Windows Server 2008 64bit. The application had an existing feature that reads and writes excel data to/from the database and to accomplish this JET driver were used.  An interesting fact is that the JET driver that can be used on the 32 bit server is not compatible with 64bit servers.

Here is the KB article for it - http://support.microsoft.com/kb/957570

Here are two options that can used to achieve the required functionality

• Use Open XML to read and write to the data from the new Excel files (.xlsx format).  In this scenario, you would then need to ensure your old Excel files (.xls files) are converted to the new .xlsx file format or you would need to continue to use Jet to read and write from the .xls files, since these files are not xml based.

          Open XML File Formats  and Open XML Developer Center

• Another option would be to continue to use Jet on the Windows 2008 server.  Since Jet is a Windows component, it is still found on Windows 2008 machines.  Jet itself is being deprecated but it can still be used.  In order to use it, your client would just need to ensure their run their web application as a 32 bit application.  The only caveat to this is that Jet won’t be able to read and write from the new Excel format (.xlsx files).

          Here is the “How To” link for it. I tried this #2 and it didn't work.

None of the above options suites my needs i.e., “Time” and “Cost”. The only solution is to use the ACE driver (Microsoft.ACE.OLEDB.12.0) which is compatible with 32 and 64bit servers.

The caveat for using the ACE driver is that it could hang the IIS worker process by throwing up a message box during exceptions. IIS is not well equipped to handle those scenarios (stateless behavior) which may result in memory or performance issues.  

Here is the Data Access Road Map which talks about the JET database engine - http://msdn.microsoft.com/en-us/library/ms810810.aspx

Here is the actual driver download link – http://www.microsoft.com/download/en/details.aspx?id=13255

The download link explains the scenarios where the ACE driver is not the super best fit. The #4 was the one that explains the stateless behavior that can cause the IIS worker process to be in hung state.

•  As a general replacement for Jet (If you need a general replacement for Jet you should use SQL Server Express Edition).
• As a replacement for the Jet OLEDB Provider in server-side applications.
• As a general word processing, spreadsheet or database management system -To be used as a way to create files. (You can use Microsoft Office or Office automation to create the files that Microsoft Office supports.)
• To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.

All said, it’s up to the us to decide and move on. Take the path/decision that well suites your project goals.

Happy Coding!!!!