Wednesday, October 13, 2010

64 Bit Revit and 32 Bit MS Access...

I've been told by several confused people that it is impossible to link a session of 64 bit Revit with a 32 bit session of a Microsoft Access database... this couldn't be further from the truth.

This does take a bit of monkeying around, but is totally possible even without some goofy fake 64 bit ODBC driver. s it turns out, Microsoft XP Pro 64 as well as Windows 7 64 ship and install with a file named "C:\Program Files (x86)\Common Files\Microsoft Shared\DAO\dao360.dll". The problem is that this file is not properly registered on the client machine when the operating system installs. Microsoft didn't even know this until I pointed it out to them in a service call. But wait, registering this file using the following REG script is not all that you need to do to get this to work:

There are three key references that need to be added to your project in order to take a Microsoft Access Database and connect to it as a "DAO" database object in a 64 bit name space that can support all the normal SQL commands and recordset functionality.

The bottom three references shown in the iamge above should be familiar for you, but the top three are specific to gaining access to the Microsoft Access database via the dao360.dll file! This functionality has been possible all along!

Here's how you generate the class to communicate with the database:

Imports Microsoft.Office.Interop
Imports Autodesk.Revit
Imports System.Diagnostics
Imports System.Reflection

''' <summary>
''' A Class to create and interact with a session of Microsoft Access through a 64 bit namespace
''' </summary>
''' <remarks></remarks>
Public Class MSAccess64

    Private m_DaoDB As Access.Dao.Database
    Private m_AccApp As Access.ApplicationClass
    Private m_rs As Access.Dao.Recordset

    ''' <summary>
    ''' Connect to a Microsoft Access Database
    ''' </summary>
    ''' <param name="dbName">Valid file name and path to a Microsoft Access database</param>
    ''' <param name="settings">A generic settings class</param>
    ''' <remarks></remarks>
    Public Sub New(ByVal dbName As String, ByVal settings As clsSettings)
        ' Test for valid filename
        If Dir$(dbName, FileAttribute.Directory) = "" Then
            MsgBox("Database File Not Found... ", MsgBoxStyle.Information, "Error")
            Exit Sub
        End If
        ' Creates a new Access session
        m_AccApp = New Access.ApplicationClass
            ' Opens the database filename in Access Session
            m_AccApp.OpenCurrentDatabase(dbName, Exclusive:=True)
            ' Requires access to dao
            ' Sets variable to new database object
            m_DaoDB = m_AccApp.CurrentDb
            ' Minimize app to tray
        Catch ex As Exception
            MsgBox(Err.Description, MsgBoxStyle.Exclamation, Err.Source)
            If Dir$("C:\Program Files (x86)\Common Files\microsoft shared\DAO\dao360.dll", FileAttribute.Normal) = "" Then
                'Missing file error...
                MsgBox("64 bit ODBC interop file not found:" & vbCr & _
                       "C:\Program Files (x86)\Common Files\microsoft shared\DAO\dao360.dll" & vbCr & vbCr & _
                       "Error!", _
                       MsgBoxStyle.Exclamation, _
                       "Error Connecting to Database!")
            End If
            MsgBox("Error in attempting to open database interopabilty..." & vbCr & _
                   "Possibly due to a 64 bit limitation with ODBC..." & vbCr & vbCr & _
                   "Verify that dao360.dll has been properly registered and try again...", _
                   MsgBoxStyle.Exclamation, _
                   "Error Connecting to Database!")
        End Try

    End Sub

End Class