Call Routine in Access Module From .Net?

Question

I am having trouble automating Access 2007 from .Net, either VB or C#. All I want to do is call a subroutine in an Access module from .Net.

Ultimately, I have to make it work in VB, but I've been trying both with the same results.

Below is the code in my VB test form. It results in the error:

System.Runtime.InteropServices.COMException (0x800A9D9F): Exception from HRESULT: 0x800A9D9F at Microsoft.Office.Interop.Access.ApplicationClass.Run(String Procedure, Object& Arg1, Object &Arg2, ..., Object &Arg30)

My test Sub in Access is named "MyTest" and is module named "Module1". All it does is insert a single record in a table. It runs fine from within Access. I have tried various permutations of "Module1.MyTest", "MyTest()", "Call MyTest", etc., with no luck.

I have found some other examples on the net of automating Access (and other Office applications) but can't seem to get any to work. If anyone could point me to a working example I would be grateful.

Sample code:

Imports Access = Microsoft.Office.Interop.Access
Imports Microsoft.Office.Core

Public Class FormTest
    Private Sub cmdTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTest.Click

        Dim aa As New Access.Application()

        Try
            aa.OpenCurrentDatabase("c:\Test.accdb")
            aa.Run("MyTest")
        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            If aa IsNot Nothing Then
                aa.Quit(Access.AcQuitOption.acQuitSaveNone)
            End If
            Me.Close()
        End Try

    End Sub
End Class

Answer 1

Try to see the InnerException (the actual reason of the error).

Also, if you know VB6 - try writing the same code in VB6 & see if that works.
If you don't face any error, it should work the same in VB.net

EDIT: Is MyTest a public method?

Answer 2

It was security. In Access, I had to turn off all security checking. Once I did that, it worked fine.

Answer 3

If the objective of your access proc is to manipulate a table in the access file, why don't you open an ODBC or OLEDB connection to the .mdb file and then do the work directly on the table, either by opening a recordset and manipulating it, or by sending the corresponding SQL instructions?

You will at least save all the time needed to create the new instance of Access.