Using MS Access Databases with Inventor 2014 VBA

Several people have had problems with existing VBA programs when they made the transition to Inventor 2014 from an earlier release. The problem they were having is that the portion of their program that tried to connect to an Access database was failing. Below is a description of what’s really going on.

History
For a long time Inventor was only available as a 32-bit application. It still is available as 32-bit but most people use the 64-bit version, primarily because of the ability to use much more memory.  Until fairly recently VBA was only available as a 32-bit component.  As a component it doesn’t run on its own but needs to be hosted within another process. In this case it runs within the Inventor process. To connect to an Access database you need to use the OLEDB Provider component. Just the same as the VBA component, it needs to run within another process, which in the case of Inventor VBA is running within the Inventor process. The picture below represents what’s happening with a 32-bit version of Inventor. When a component runs within another process it MUST be the same bitness.  That is 32 bit components MUST run within an 32 bit process and 64 bit components MUST run within a 64 bit process. There are no exceptions. By running in-process the speed of VBA is very good because the communication between VBA and Inventor is within the same process and is essentially a direct call.

 

When a 64-bit version of Inventor was being developed we had a problem; VBA was only available as a 32-bit component.  This meant that VBA could no longer run within Inventor. A lot of customers had written macros and were dependent on the functionality they provided so we didn’t want to abandon VBA. A workaround to the problem was invented, which is illustrated below. When you run VBA from a 64-bit installation of Inventor 2013 or earlier, a separate process is started called Inventor32bitHost.exe, (which you can see in Windows Task Manager). This is a small executable who’s purpose is to serve as the 32-bit host for the VBA component.  When the OLEDB Provider component is used by VBA it is also loaded within the Inventor32bitHost.exe process and everything works since it’s all 32-bit.

 

There are some definite drawbacks to the Inventor32bitHost.exe approach:

1. An extra process is being started. This is the reason for the slow start-up time when you first run VBA after starting Inventor. The Inventor32bitHost.exe process isn’t started until it’s needed but you pay the price then.
2. Running VBA in a separate process outside of Inventor means that the Inventor API calls are being made between two processes. Out of process API calls are much more expensive since each call needs to be wrapped to pass the call information between the two processes.
3. API calls that use certain argument types are not supported when using a COM Automation interface between two processes. For example, getting the thumbnail image of a document is not possible because it passes the image through the API.
4. There were also other issues and we had a list of reported issues that we really couldn’t do much about because of the architecture.

Initially we were told that there would only ever be a 32-bit version of VBA available and thought we would have to live with the Inventor32bitHost solution. We looked at possible alternatives, including VSTA (Visual Studio Tools for Applications) and Python but they didn’t have the same ease of use that VBA has and would have forced customers to migrate all of their existing programs. At some point Microsoft changed their minds and released a 64-bit version of VBA and we were able to incorporate this into the Inventor 2014 release.

Inventor 2014
The picture below illustrates what’s happening now. You’ll notice the architecture is exactly the same as with a 32-bit version of Inventor except everything is 64-bit. This solves all of the problems we were having with the 32-bit workaround; we now have fast VBA startup, fast program execution, and no limitations on API calls.

 

Issues
After the release of Inventor 2014 and thinking all of our problems were solved we started to get some reports from customers having problems with their VBA programs that use an Access database. The cause of the problem is that they were using the 32-bit version of the OLEDB Provider component. This probably should have been a fairly obvious cause of the problem but there are some side issues that made this a bit more of a difficult issue to diagnose.

The most common way to get the the 64-bit OLEDB Provider component is to install 64-bit Microsoft Office. I believe the first version of Office where you had the option of a native 64-bit install was Office 2010.

Issue 1
After some testing and some communication with Microsoft it’s been verified that when you install MS Office only one bitness (32 or 64 bit) of the OLEDB Provider is installed; the one that matches the bitness of Office that’s installed. Ideally, when installing Microsoft Office on a 64-bit machine it would be good if it installed both the 32 and 64 bit versions of the OLEDB Provider component whether you install the 32 or 64 bit version of Office.

The other thing we learned is that it’s not possible to have both the 32 and 64 bit versions of the component installed at the same time on a machine.

The following MSDN article discusses this.  Data Programming with Microsoft Access 2010: Using 32-bit and 64-bit ACE Providers

Where this can be a problem is if you have both 32 and 64 bit applications that use the OLEDB Provider component.  With this limitation it’s not possible that both of them can work.

Issue 2
As I said above, the typical way to install the OLEDB Provider component is to install MS Office. There’s a problem with this if you’re using MS Office 365. When it installs the OLEDB Provider component, it registers it in a way that it’s accessible from within the Office applications but not from other applications. So that means it’s not usable from Inventor’s VBA. Microsoft has verified this and is investigating the problem.

Possible Solutions

1. Install a 64-bit version of Office (but not Office 365 because of Issue 2 described above). Installing Office will also install the OLEDB Provide component.
2. If you’ve installed MS Office 365, you can install the Microsoft Access Database Engine 2010 Redistributable. Make sure you select the file “AccessDatabaseEngine_x64.exe” for download to get the 64-bit version.  Be aware that this still doesn’t solve the issue of having both the 32 and 64 bit versions of the component installed at the same time.

Issues
In all cases the solution is not compatible with a 32-bit version of Office since it has installed the 32 bit version of the OLEDB Provider component.  Because of issue 1 described above you have to have either 32 or 64 bit and can’t mix the two as far as the OLEDB Provider component. This means to use the OLEDB Provider component in Inventor 2014 you will need to have a 64-bit version of Inventor.