Supplemental BIM Database Porn Part 2, Reporting Multiple Categories Together

This is part 2 of the highly anticipated and exciting mini series on supplemental BIM database design!

This post will focus on how data from two entirely different tables can be UNION queried together to aggregate counts and cost SUM's in a single report. The really cool part about how this works is that the fields don't have to match names or even data types between the two tables... I know, SUPER EXCITING~!!
The examples in this mini series have been built in MS Access 2007 in case you haven't noticed but could very easily be used with minor adjustments on SQL or my new favorite PostgreSQL.

A quick snapshot showing the 5 tables that we'll be working with is shown below. The type properties are in the category tables prefixed with "type" and the instance properties in the tables prefixed with "inst"...

UNION queries can only be built in MS Access 2007 using pure SQL syntax. This shouldn't be any big deal at all to a seasoned SQL DB Admin. To create a UNION query in MS Access 2007, click the "UNION" button in the query Design tab of the ribbon.

Union queries will show up in your MS Access 2007 Object Browser with a Union icon as shown below.

It is important to note that while the data types and names of the fields in each table can be entirely different when used in a UNION query, the quantity selected between each table must be equal. Select 5 fields in one table, then you better select 5 in the other. There is no limit to the amount of UNION queries you can run together, just remember to select the same quantity of fields in each of your table selections.


Another super important key is the order of the fields in each of your selection queries. That is to say that the first field in the first selection will list directly with the first field of the second selection query and so on, make sense? Great!


So what would the query look like? The example below shows the selection of all "Furniture" and "Furniture Systems" elements from four tables named "inst Furniture", "type Furniture", "inst Furniture Systems" and "type Furniture Systems." Each category holds a relationship between two tables for instance and type properties respectively. Yet another relationship to complicate matters further but also show the flexibility of such a query is the "ProjectInfo" table that holds the project information useful in populating report headers and footers.

The resulting query results (partial) looks like this (Quantity and TotalCost are aggregated across the two UNION queries in totally separate table groups):



A BIM Based Facility and Workplace Management Web Site

If you want the details on how to build your own BIM Based Facility and Workplace Management Web Site, you'll have to attend my class in Autodesk University 2010 in Las Vegas.

Most notably, I will demonstrate how a 32 bit Microsoft Access database can be directly connected to and edited from a 64 bit session of Autodesk Revit Architecture using no special drivers or crazy configurations... The resulting database can then be used to serve the BIM Based Facility and Workplace Management Web Site.

I will be demonstrating how a BIM model can be configured and managed from a web environment. Notable page configurations will include:

• A Searchable Employee Locator
• 3D Floor Plan Device Query
• Inventory Management
• Energy Meter Management (Readings, etc), Graphical
• How to manage external data using rooms as the parent element

MS Access Recordset Handling

I showed how to create an instance of a 32 bit Microsoft Access database in a 64 bit Revit name space but I did not mention how to handle opening and updating recordset objects.

The dao.Recordset object provides the ability to open and update a recordset returned by a SQL select command.

''' <summary>
    ''' Returns a Recordset object from an Access Database
    ''' </summary>
    ''' <param name="tblName">Name of the table containing the record</param>
    ''' <param name="keyID">Field name</param>
    ''' <param name="keyValue">Field value to search for</param>
    ''' <returns>Recordset matching search criteria</returns>
    ''' <remarks></remarks>
    Public Function OpenRecordset(ByVal tblName As String, _
                                  ByVal keyID As String, _
                                  ByVal keyValue As String) As Access.Dao.Recordset
        Dim m_rs As Access.Dao.Recordset
        Dim SQL As String = "SELECT * FROM [" & tblName & "] WHERE ([" & keyID & "]='" & keyValue & "')"
        m_rs = m_DaoDB.OpenRecordset(SQL)
        Return m_rs
    End Function

Now with the m_rs object, the recordset can be modified using:

.Edit
m_rs.Fields("FieldName").Value = MyValue
.Update

You can get a value using:

MyValue = m_rs.Fields("FieldName").Value

Closing the MS Access Session with Brute Force!

It can be tricky to kill a Microsoft Access database session by simply just closing it. The code below demosntrates how to close the application instance and release it entirely from memory using the Marshal technique.

BRUTE FORCE:

''' <summary>
    ''' Close and destroy on exit
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CloseDB() As Boolean
        Try
            ' Close the database
            m_DaoDB.Close()
            ' Quit the application
            m_AccApp.Quit(Access.AcQuitOption.acQuitSaveAll)
            ' Marshal close just in case
            System.Runtime.InteropServices.Marshal.ReleaseComObject(m_AccApp)
            ' Destroy the main variables
            m_AccApp = Nothing
            m_DaoDB = Nothing
        Catch ex As Exception
            ' Close the database
            m_DaoDB.Close()
            ' Quit the application
            m_AccApp.Quit(Access.AcQuitOption.acQuitSaveAll)
            ' Marshal close just in case
            System.Runtime.InteropServices.Marshal.ReleaseComObject(m_AccApp)
            ' Destroy the main variables
            m_AccApp = Nothing
            m_DaoDB = Nothing
        End Try
    End Function

Programmatically Create a Relationship in Microsoft Access

Here is another of several Microsoft Access related posts I'll be sharing for handling a database interaction between 32 bit Microsoft Acces and a 64 bit session of Autodesk Revit 2011.

I've been asked several times recently how I am able to pragmatically create my table relationships in my Microsoft Access database setup utilities. As it turns out, the dao.Database object has a function built into it for doing just this but can be tricky to get it to actually work.

What you'll need to get this to work is an active dao.Database object, a primary table and primary key name, a foreign table and foreign key name, and a unique name for the relationship.

Sample code is provided below:

''' <summary>
    ''' Creates a relationship between two tables
    ''' </summary>
    ''' <param name="relName">Unique name for table relationship</param>
    ''' <param name="fkFieldName">Foreign Key Field Name</param>
    ''' <param name="pkFieldName">Primary Key Field Name</param>
    ''' <param name="TypeTableName">Top most table name</param>
    ''' <param name="InstTableName">Child table name</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateRelationship(ByVal relName As String, _
                           ByVal fkFieldName As String, _
                           ByVal pkFieldName As String, _
                           ByVal TypeTableName As String, _
                           ByVal InstTableName As String) As Boolean

        Dim rel As dao.Relation
        Dim fld As dao.Field

        Try
            rel = m_DaoDB.CreateRelation(relName, TypeTableName, InstTableName, dao.RelationAttributeEnum.dbRelationUpdateCascade)
            'GUID 'The field from the primary table.
            fld = rel.CreateField(pkFieldName)
            'xxGUID 'Matching field from the related table.
            fld.ForeignName = fkFieldName
            'primaryFieldName 'Add the field to the relation's Fields collection.
            rel.Fields.Append(fld)
            'Add the relation to the database.
            m_DaoDB.Relations.Append(rel)
            Return True
        Catch ex As Exception
            Return False
        End Try

    End Function

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
        Try
            ' 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
            MinimizeMsAccessApps()
        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