Microsoft needs to redress problems in Microsoft Access

So we fired Microsoft Access here at work. Access was fired for cause, without severance. What do I mean by that? Let me explain.

Most all of our work here revolves around Microsoft SQL Server 2005 & 2008. We use Access only as an export format, or a quarantine cleanup space for extremely dirty data. Some of our clients do like their data transmitted to them in Access format. We're going to stop doing that... almost immediately.

Microsoft has effectively ruined Access. Once upon a time, it the moral equivalent of a very useful Victorinox Swiss Army Knife for data. Now, it's pretty well worthless.

Now why would I say a thing like that? Two expert reasons:

1. At the moment Microsoft Access has no 64 bit API for database creation and manipulation.
2. Microsoft has destroyed the application itself with the Trust Center setting.

The first point spells out our primary affliction. We run a collection of websites that provide data to the 7,000-8,000 financial institutions of these United States of America. Some of those institution like their data served up on a platter of Access MDBs. We have supported this in the past, but no more.

Since we made the conversion to 64 bit mode, a conversion we found necessary due to the volume and complexity of our data, we've been experiencing serious issues supporting Access MDBs. We have re-discovered the following tangle of faux-solutions and problems.

1. The ADOX code we wrote years ago cannot be upped to 64 bit mode. ADOX is an ActiveX library. It is 32 bit. There is no such thing as a 64 bit ADOX library. Our old 32 bit code will run, but it breaks when upped to 64 bit mode. The COM library cannot be found and loaded. That is because a 64 bit ADOX does not exist.
2. If you are running SQL Server in 64 bit mode, you can forget about exporting Access MDB files. The 64 bit SSIS engine will attempt to lock and load the JET 4.0 engine, which is just a 32 bit COM library. Naturally, this fails. Tough shit.
3. We attempted to use SSIS packages in SQL Server (32 bit) to export the data in an Access MDB. We found this extremely cumbersome. The SQL machines and our web boxes are different boxes. Getting a SQL machine to deposit an MDB on a web server, or a mutually shared directory is a mater of opening more holes in the firewall, exposing more things to the DMZ, and there are timing issues beyond that. We also dislike the notion of keeping a 32 bit SQL Server in town.
4. We explored the possibility of writing .ACCDB XML files using XML Literals and compression. We were working on this very complex task when our customers signaled that they were not using Access 2007, and had no plans to go to 2010. The old MDB was demanded, not the new format. This effectively terminated our work.
5. There is currently no managed code (that is, C# .NET) library for manipulating Microsoft Access schemas in either the old or the new formats. Not even third parties such as the marvelous Syncfusion make such a library available.
6. Of course, we could continue running our 32 bit app, but this precisely the thing we want to retire.

This left us in a quandary. At the moment, there is no working solution.

COM is evil, and Microsoft is quite correct in killing it off. We don't want to see Microsoft up ADOX to 64 bit mode so our old code can recompile and run with success. That is not what we are after.

It is difficult to reason with clients about Microsoft upgrades. If they don't want to upgrade, we aren't really going to talk them into it.

I think I am going to finish the code that creates new ACCDB export, but I will do so privately. My company isn't interested. Neither are our clients. I do see a future value for this product. It will be worthwhile in the end. That should be the first Open XML OOXML managed code solution for the .NET framework.

As you can see, this leaves us hanging. We want to be comprehensively 64 bit from end to end. We want to flush the COM. We do not want ADOX, as it is both 32 bit and COM. We do not have a managed solution for producing the binary MDB. Neither does anyone else, for that matter. Our clients do not want .ACCDB.

Where do we go from here? The logical solution is to simply state that will not be supporting MDB anymore. We are simply phasing out that service. It has become to problematic.

I suppose this fits Microsoft's objectives of marginalizing the non-OOXML formats. This serves their end objective of applying upgrade pressure to their clients. I really don't like it. There aught to be a fully managed code solution for MDB construction. The net effect is that Access is going to marginalized by our firm and many others.

The second issue revolves around the utterly obnoxious Trust Center. The mere existence of the Trust Center--in this context--is proof positive that the lawyers are now running the software design process in Redmond. This is why we are seeing so many bad changes lately.

So what is the problem with the Trust Center in Microsoft Access? Aside from the fact that it utterly breaks any Access MDB/ACCDB solution, there is nothing wrong with it. Your app won't run until (under advisement that he/she should not) a user clicks "Enable Dangerous Macros". I say that breaks the application. The application does not work anymore. Further, breaking the app in this manner does nothing to protect the system. The very moment the user clicks "Enable Dangerous Macros", and the user must do this, all protection goes out the window. Microsoft would tell you this is a feature, not a design flaw. Stupid. Very stupid.

More stupid than selecting a QB #1 overall in the NFL Draft. Believe me, that's saying an awful lot.

The trust center effectively kills Access, shutting it down such that it cannot be used. If the application is so dangerous that it must be shipped in a lockdown state, it is too dangerous for public consumption, and should be removed from the market. If it is not that dangerous, the application should not be shipped in a castrated state for the sake of your legal department. Get a clue, Microsoft!

Frankly folks, this thing is utterly baffling. It's existence can only be understood as a stunt foisted upon Redmond software developers by the Microsoft legal department, who insisted on a disclaimer built into the package.