Converting from VBA to Excel-DNA with access database

Apr 1, 2015 at 9:43 AM
I'm planning on updating some old Excel VBA addins and Excel-DNA looks like it might be just the thing for it, however I have a query in relation to the retrieval of data from an access database.

The current VBA sets a database connection at the outset:
Dim db As Database, rs As Recordset
Set db = OpenDatabase("<filepath>\tables.mdb")
then calls data from it as required into recordsets and processes the recordsets as required:
Set rs = db.OpenRecordset("SELECT * from <table> WHERE Age = " & Int(Age), dbOpenDynaset)
Is this possible to replicate?
Apr 1, 2015 at 10:34 AM
Hi Mike,

Yes - you can use DAO from inside your Excel-DNA add-in.

I assume you have some version of Visual Studio installed. Then start with these steps:
  • Create a new "Class Library" VB.NET project.
  • Open the Tools-> NuGet Package Manager -> Package Manager Console.
  • Install the following three packages:
    PM> Install-Package Excel-DNA
    PM> Install-Package Excel-DNA.Interop
    PM> Install-Package Excel-DNA.Interop.DAO
  • Put this code into your .vb file
Imports System.IO
Imports Microsoft.Office.Interop.Access.Dao
Imports ExcelDna.Integration

Public Module MyAddIn
    ' This would not be needed in VBA - in .NET we must instantiate the root COM object
    Private MyDBEngine As New DBEngine()
    Private MyDatabase As Database

    Const MyDatabasePath As String = "C:\Temp\TestDB.mdb"

    Private Sub EnsureDatabaseIsConnected()
        If MyDatabase Is Nothing Then

            ' We've not set the database yet - try to open or create a new one
            If File.Exists(MyDatabasePath) Then
                MyDatabase = MyDBEngine.OpenDatabase(MyDatabasePath)
                ' Create new DB for testing - this could be OpenDatabase too
                MyDatabase = MyDBEngine.CreateDatabase(MyDatabasePath, Locale:=LanguageConstants.dbLangGeneral)

                ' Add the tables or give an error ....
                ' ...
            End If
        End If
    End Sub

    ' These will be UDF functions you can call from a sheet...
    ' Check that the DAO is working correctly
    <ExcelFunction(Description:="Returns the version of the loaded DAO DBEngine")>
    Public Function DBEngineVersion() As Object
        Return MyDBEngine.Version
    End Function

    ' Check that the database was opened 
    <ExcelFunction(Description:="Returns the name of the open database")>
    Public Function DatabaseName() As String
        Return MyDatabase.Name
    End Function

    ' This will be a macro added to the Add-Ins tab on the ribbon
    <ExcelCommand(MenuName:="Excel-DNA DAO Sample", MenuText:="Show Database Name")>
    Public Sub ShowDatabaseName()
        MsgBox(MyDatabase.Name, Title:="DAO Sample")
    End Sub

End Module
  • Press F5 to run - then check the functions and the menu button under the Add-Ins tab.
One issue is if you want your add-in to work with the 64-bit version of Excel (not common, since the 32-bit version is the default install even if Windows itself is 64-bit).
Then you'll have to switch to the AceDAO library instead of the regular JET one. All your code should stay the same, but you need to change references - I've not tried this myself.