How can I capture a Microsoft Access VBA debug error from my C# code?

Question

I have a C# program that opens several Microsoft Access files, and executes functions from within each one.

Essentially, the code looks something like this:

Microsoft.Office.Interop.Access.Application app =
    new Microsoft.Office.Interop.Access.Application();

app.Visible = true;
app.OpenCurrentDatabase(accessFileFullPath, false, "");

//Call the function
app.Eval(function);

However, when a debug error occurs in the VBA code, I would like to trap it in my C# program.

Please don't answer: "trap the error in your VBA program". For reasons that I will not get into, this is not possible.

A method that I have used in the past is to have a thread intermittently monitor for a handle to any Visual Basic Debug window (the FindWindowEx Win32 function returns a nonzero value). I do not like this method, and don't want to continue to using it.

I found this thread, which applies to Microsoft Excel. In essence, it uses the Microsoft.VisualBasic.CallByName() function, which apparently can be trapped in a try/catch block, without user interaction. However, I have not been able to get this to work with Microsoft Access-- primarily because I cannot figure out how to call the function/sub using this command.

Any suggestions would be sincerely appreciated!

Edit: As I mentioned in one of the answers below, I have tried wrapping the Eval() in a try/catch block and my C# program seems to ignore it, until a user hits the "End" button on the "Microsoft Visual Basic" error dialog. I do not want any user interaction, but rather want to trap the VBA error for handling in my C# program.

Answer 1

Update: For some reason, the previous code I had posted had only worked when the Access file format was 2000. I have confirmed that this new code also works with Access 2002 and 2010 files.

The code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using VBA = Microsoft.Vbe.Interop;

namespace CaptureVBAErrorsTest
{
    class CaptureVBAErrors
    {
        public void runApp(string databaseName, string function)
        {
            VBA.VBComponent f = null;
            VBA.VBComponent f2 = null;
            Microsoft.Office.Interop.Access.Application app = null;
            object Missing = System.Reflection.Missing.Value;
            Object tempObject = null;

            try
            {
                app = new Microsoft.Office.Interop.Access.Application();
                app.Visible = true;
                app.OpenCurrentDatabase(databaseName, false, "");

                //Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database

                //Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
                //the temp objects won't interfere with other objects each other (if there are multiples).
                string tempGuid = Guid.NewGuid().ToString("N");

                f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);

                //We must set the Instancing to 2-PublicNotCreatable
                f.Properties.Item("Instancing").Value = 2;
                f.Name = "TEMP_CLASS_" + tempGuid;
                f.CodeModule.AddFromString(
                    "Public Sub TempClassCall()\r\n" +
                    "   Call " + function + "\r\n" +
                    "End Sub\r\n");

                //Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
                f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
                f2.Name = "TEMP_MODULE_" + tempGuid
                f2.CodeModule.AddFromString(string.Format(
                    "Public Function instantiateTempClass_{0}() As Object\r\n" +
                    "    Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
                    "End Function"
                    ,tempGuid));

                //Step 3: Get a reference to a new TEMP_CLASS_* object
                tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);

                //Step 4: Call the method on the TEMP_CLASS_* object.
                Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
            }
            catch (COMException e)
            {
                MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
            }
            catch (Exception e)
            {
                MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
            }
            finally
            {
                //Clean up
                if (f != null)
                {
                    app.VBE.ActiveVBProject.VBComponents.Remove(f);
                    Marshal.FinalReleaseComObject(f);
                }

                if (f2 != null)
                {
                    app.VBE.ActiveVBProject.VBComponents.Remove(f2);
                    Marshal.FinalReleaseComObject(f2);
                }

                if (tempObject != null) Marshal.FinalReleaseComObject(tempObject);

                if (app != null)
                {
                    //Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
                    app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
                    Marshal.FinalReleaseComObject(app);
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}

The Details:

According to the thread I had linked to by Mike Rosenblum, CallByName() can execute Office code in C#, and can trap VBA exceptions (Application.Run() and Application.Eval() seem to only get caught after the user interacts with the debug window). The problem is that CallByName() requires an [instantiated] object to call a method on. By default, Excel has the ThisWorkbook object, which is instantiated upon opening a workbook. Access does not have a similar object that is accessible, as far as I know.

A subsequent post on the same thread suggests adding code dynamically to the Excel workbook to allow calling of methods in standard modules. Doing so on ThisWorkbook is relatively trivial, because ThisWorkbook has a code-behind and is automatically instantiated. But how can we do this in Access?

The solution combines the two techniques above in the following way:

1. Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database. Keep in mind that the Instancing property of the class must be set to 2 - PublicNotCreatable. This means that the class is not creatable outside of that project, but it is accessible publicly.
2. Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
3. Get a reference to the object in your C# code, by calling the VBA code in step (2). This can be done using Access interop's Application.Run().
4. Call the method on the object from (3) using CallByName-- which calls the method in the standard module, and is trappable.
5. When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.

To get the VBA error description, use "e.Message", where "e" is the COMException object.

Make sure you add the following .NET references to your C# project:

Microsoft.Office.Interop.Access
Microsoft.Vbe.Interop
Microsoft.VisualBasic