Embed .Net dll into Microsoft Access Database

Question

1) Is it possible to embed a .net dll into a Microsoft Access database? Ideally I want the dll actually embedded into the access database, so it can't go missing. I need to access a few APIs from within my access database.

2) If it is possible, what are the limitations? I'm assuming the access database would have to be used on a machine that had .net installed. Are there any other issues that I should consider?

Answer 1

This actually seems to be 2 slightly different questions than the ones asked, if I'm reading it correctly: (1) Can you embed a binary in an JET/ACE database? and (2) Can you call methods from a .NET DLL from MS-Access?

1. Yes, you can embed a binary file in an JET/ACE database. My preferred way of doing this is by reading the file as binary and encoding as base64 in a Memo field.

First, you need to read in the binary file into a byte array. This can be done using Open, or by using an ADODB.Stream object. Then, you encode the byte array as a Base64 string, and finally, you write that string to a table. To export the binary, you do the opposite.

Here are functions to convert a byte array to Base 64 (Source):

'Requires reference to Microsoft XML v3.0
Private Function EncodeBase64(ByRef arrData() As Byte) As String

    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement

    ' help from MSXML
    Set objXML = New MSXML2.DOMDocument

    ' byte array to base64
    Set objNode = objXML.createElement("b64")
    objNode.dataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text

    ' thanks, bye
    Set objNode = Nothing
    Set objXML = Nothing

End Function

Private Function DecodeBase64(ByVal strData As String) As Byte()

    Dim objXML As MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement

    ' help from MSXML
    Set objXML = New MSXML2.DOMDocument
    Set objNode = objXML.createElement("b64")
    objNode.dataType = "bin.base64"
    objNode.Text = strData
    DecodeBase64 = objNode.nodeTypedValue

    ' thanks, bye
    Set objNode = Nothing
    Set objXML = Nothing

End Function

And, here are examples of how to use those Base64 functions to import and export a binary file into a Memo field using an ADODB.Stream object:

Function ImportBinary()
    'Requires a reference to ActiveX Data Objects 2.8 or higher
    Dim InputStream As ADODB.stream
    Dim FileBytes() As Byte

    Set InputStream = New ADODB.stream
    InputStream.Type = adTypeBinary
    InputStream.Open
    InputStream.LoadFromFile ("A:\Binary\File\To\Import.dll")
    FileBytes = InputStream.Read()

    With CurrentDb().TableDefs("TableWithMemoField").OpenRecordset
        .AddNew
        !MemoField.Value = EncodeBase64(FileBytes)
        .Update
        .Close
    End With

    InputStream.Close
End Function

Function ExportBinary()
    Dim OutputStream As ADODB.stream

    Set OutputStream = New ADODB.stream
    OutputStream.Type = adTypeBinary
    OutputStream.Open

    With CurrentDb().TableDefs("TableWithMemoField").OpenRecordset
        OutputStream.Write DecodeBase64(!MemoField.Value)
        .Close
    End With

    OutputStream.SaveToFile "A:\Binary\File\To\Export.dll"

    OutputStream.Close
End Function

Alternatively, you could also embed the file(s) into OLE fields, or, recent versions of Access also have an "Attachment" field. I believe it is possible to use this.

2. Yes, it's possible to call methods from a .NET DLL - see here. What you are essentially doing is hosting the .NET runtime in Access.

The limitation is that you can only host the .NET 2.0 or 3.5 runtime -- despite the fact that the linked page says you can use 4.0. As far as I know, there is no easy way to host the .NET 4.0+ runtime in VBA. If you try to call a 4.0+ DLL, you will get an error to the effect of "the assembly is newer than the runtime".

Yes, you'd also need to use it on a machine that had the 2.0 and/or 3.5 runtimes installed on it.

Also, it won't load a DLL directly from a network share. This is a limitation of the .NET framework which it is possible to overcome (link), but I'm not sure if that can be done when hosting the DLL in Access.

In order to use the "embedded" DLL, you need to export it from the database before you use it; a good place would be the user's temp folder.

EDIT: Regarding point (2)-- I had previously written that you do not need to expose your C# class as COM, however, after testing this I realized this was incorrect. You do need to declare your class with a [COMVisible(true)] attribute (or implement an IDispatch interface) in order for VBA to correctly instantiate your class-- otherwise CreateInstanceFrom will return Nothing.