I recently had a support call where a desktop machine had been rebuilt from Windows 7 x86 to X64, but an Access Database which has VBA code was not running correctly on the new machine.
The error was a common ‘Automation Error’.
The Diagnostics, aka ‘Googling’.
I first checked the VBA references by using Alt + F11, and compared the loaded references to a working machine but there was no difference. The error doesn’t given an indication as to which line of the VBA is failing so initially I didn’t have a clue as to which library wasn’t registered. In the past I have seen something similar where dll or ocx files have failed to registered so I googled for the usual suspects which were:
regsvr32 “C:\Program Files (x86)\Common Files\System\ado\msadox.dll”
regsvr32 “C:\Program Files\Common Files\System\ado\msadox.dll”
After a reboot there was no change so I then tried to figure out by a process of elimination which line in the VBA module was failing.
Within the code there was an error handler set up to skip to the code “error handler” if an error occurred. There I used msgboxes throughout the code, and if a msgbox showed when I ran the skip I knew that all the code prior to it was working.
After a process of elimination a line of code which created a new Excel object was causing the error.
I then ran the command “excel.exe /regserver” which should re-register the Excel components but this didn’t resolve the issue. I then resorted to repairing the whole of Office 2010 and then finally uninstalling and reinstalling Office, but nothing thus far had fixed the issue.
The Long Awaited Fix
After some amazing googling skills and a lot of trial and error I found some references which involved deleting part of the registry:
This registry path may be slightly different depending on the faulty Office application, but it relates to the Primary Interop Assembly Name. Once deleted, I tried to re-run the VBA macro and Office Set-up kicked in to re-install the Interop Assembly which in turn fixed the issue!