Accessing the
Autodesk Database Link
*Update 09/03/2013
If you use the Revit DB Link, you might notices that in the Rooms table of Access, the value for Level is not reporting the Level Name. Rather it is reporting the the element id of the level, not the name. if you select level 1 in the model and go to Manage > Inquiry > Id of selection, you will see the ID for Level 1 is 311.
You can verify this by opening the Levels table in Access.
Thanks goes out to Chris Aquino of Autodesk Support Team for determining the issue.
*Original Post
If you have ever spent time trying to populate a schedule in
Revit, you quickly realized that Revit is a horrible data entry tool. Bad news
for us those, our projects require us to present a great deal of information,
and sometimes that information is in the form of schedules or we may want to
populate the model database with additional information for a COBie or similar
deliverable that provides data that can be useful for the operation and
maintenance of the facility at some point.
Autodesk has a database import export utility called Revit
DBLink that allows you to import and export data between an Autodesk® Revit®
2014, Autodesk® Revit® Architecture 2014, Autodesk® Revit® Structure 2014, or
Autodesk® Revit® MEP 2014 project and an external database, and then more
easily maintain relationships of linked data with Autodesk® Revit® DB Link.
(The software is also available for Revit 2012 and 2013).
Some key feature of the Revit DBLink tool are:
·
A grid view of the data enables you to make any
final edits before import.
·
Databases can be updated following internal or
external changes.
·
The grid control enables you to create Revit
shared parameters to add new fields to related tables in the database.
·
Changes to new fields within the database
automatically update Revit shared parameters upon future imports.
Workflow
The Revit DBLink workflow is very straightforward. You will:
1.
Create an external database from data within
your Revit project. In our example, we are going to use MS Access.
2.
Modify the external database. In our example, we
are going to use Excel to add additional data to select parameters. We could
also modify data as well.
3.
Update the Revit project with the data from our
external database.
Getting Started
In this example, we are going to populate some finish
information to the Room Finish Schedule shown in Figure 1. It is important to
note that we are not editing the schedule when we are exporting data using the
Revit DBLink tool. We are exporting the data associated with the various
element types in Revit, which is used to populate
schedules.
 |
| Figure 1 |
The Revit DBLink add-in should already be installed on your
workstation. It can be found on the Add-Ins tab in the RevitDBLink panel
(Figure 2).
 |
| Figure 2 |
Setting Up the DB
Link
Clicking
on the Revit DB Link button will launch the Link Revit Model with Database
dialog (Figure 3). This dialog is our interface to importing and exporting data
from Revit using the Revit DBLink tool.
 |
| Figure 3 |
Our first step is to
decide what format we will be exporting the data to. As shown in Figure 3, we
can export to MS Access 2000 – 2003, MS Access 2007 and ODBC. The first two
options are pretty straightforward. ODBC is stands for Open Database Connectivity
and allows you to connect to different types of databases, like Access, dBase,
Excel, etc. I have had limited success using the ODBC drivers, so we will focus
on using the MS Access 2007 option (Item 1 – Figure 3). If you are not familiar
with MS Access, don’t worry; we are not going to be doing our data entry work
in MS Excel. Exporting the data to MS Access is just a simpler process that
trying to create an ODBC export for MS Excel.
With the MS Access 2007 tab selected and active, click on
the [Select a new connection] text
(Item 2 – Figure 3) to highlight it, then click on the Export button (Item 3 – Figure 4). This will allow us to create a
database connection for exporting data. This will open the Select Database
dialog (Figure 4). Enter the location and name of the MS Access database file
you want to create and then click the OK
button to start the export (Figure 5).
 | |||
| Figure 4 |
 |
| Figure 5 |
During the export, Revit
DBLink will generate tables for data it has exported to MS Access. You don’t
have control (at least to my knowledge) of the data that is being exported. So
depending on the size of your project, the export can take a few seconds to
several minutes. When the export is complete, you can open the MS Access database
(Figure 6).
 |
| Figure 6 |
The Access database contains numerous tables representing
the different element and data types that have been exported from Revit. You
can open any of the tables in MS Access and explore the data or make
modifications. Data entry in MS Access is very similar to Revit, so I prefer to
export the data from MS Access to Excel, where I can cut / copy / paste more
efficiently.
In this example, we are going to be editing data for our
Room/Finish schedule. In the table listing, scroll down until you find the
“Rooms” table and double click on it to open it. As you explorer the Room
table, you can see that not all of the editable parameter data that is
available to us to edit for a Room in Revit is not available in the Room table
in MS Access.
Exporting Data from
MS Access to MS Excel
To export the MS Access Rooms
table to MS Excel, in MS Access click on the External Data tab (Item 1 – Figure 6), then click on the Excel button (Item 2, Figure 6) in the
Export panel. This will open the Export
– Excel Spreadsheet dialog (Figure 7).
Figure 7 - Export -
Excel Spreadsheet dialog
Set the destination file name and location for the exported
Excel spreadsheet (Item 1 – Figure 7).
Under the export options, you have a couple options. Checking the
“Export data with formatting and layout” (Item 2, Figure 7) will preserve as
much as the MS Access formatting as possible in Excel. “Open the destination
file after the export operation is complete” (Item 3, Figure 7) will open the
MS Excel file when the export is complete. Click the OK button to being the export process. This will open another
dialog box that will allow you to save the export steps you just completed. Click
the checkbox to save the settings if desired. Click the Close button to close the dialog.
Editing Data in MS
Excel
We can now open the Excel spreadsheet and begin editing our
data (Figure 8). As you start editing the data, be aware that there is some
data that cant and shouldn’t be edited. Notice that in Figure 8 we have data for
Level, Area, Volume. Figure 9 shows the Properties Palette for a Room. As a
general rule, if the Property Palette shows data in light gray, like the Level,
Area, Perimeter, etc, then you should not alter that data in Excel. The “Id”
field should never be altered.
 |
| Figure 8 |
 |
| Figure 9 |
For this example, I had added some
finish information to the schedule (Figure 10). Now that we have completed
editing the data in Excel, we can import that data back into MS Access (make
sure you have saved your work in Excel).
 |
| Figure 10 |
Importing MS Excel
Data into MS Access
The first thing we need to do in MS Access is to delete the
table that you are editing in MS Excel. The reason we are deleting the table in
MS Access is so we replace the data with our edits we completed in MS Access.
Highlight the table in Access you want to delete, then right-click your mouse
and select Delete (Figure 11). This will delete the table.
 |
| Figure 11 |
When you delete a
table in MS Access, a couple of warning dialog will be thrown up. One of these indicates
that you can’t delete the table until its relationships with other tables have
been deleted (Figure 12). Database relationships define how the data in one
table relates or connects to data in other tables. Allow access to delete the
relationship, as we are just using the MS Access database as a means to assist
with data entry.
 |
| Figure 12 |
From the External Data tab, click on the Excel button in the
Import panel (Item 3, Figure 5). This will open the
“Get External Data – Excel Spreadsheet” dialog (Figure 13). Specify the
location of the Filename and then select Link
to the data source by creating a linked table. This option will allow us to
maintain a link between the Excel file and the Access database. Use this option
is you know you will be updating the Excel spreadsheet regularly AND you will
not be making changes to the Room data in Revit.
 |
| Figure 13 |
Before the linking starts, the “Link Spreadsheet Wizard” dialog
(Figure 14) will open. This will allow you to preview the data that you will be
importing from MS Excel into MS Access.
 |
| Figure 14 |
If you will be making changes in Revit after you have
imported data MS Access, then use the Import
the source data into a new table in the current database.
Importing Data from
MS Access to Revit
Back in Revit, click
on the Revit DB Link button (Figure
2). In the “Link Revit Model with Database” dialog (Figure 15). Select the
export connection and then click on the Edit
and Import button. This will allow us to preview our data prior to
importing in the “Edit Database before Import” dialog (Figure 16).
 |
| Figure 15 |
 |
| Figure 16 |
The left side of the
dialog allows you to select a specific table to review. The right side of the
dialog will then display the data. If there are any issues with the data, a
dialog box will throw up an error message. Once you have reviewed the data,
click the OK button to continue. You
do not need to select a table and review the data, you can just click OK to
continue, but it is generally a good idea to review the errors.
When the import has completed, the Revit DB Link will
display and html Revit DB Link Import Report (Figure 17) listing any issues
with the files. It is very important to review the report to understand if the
issues will or will not impact what you are trying to accomplish.
 |
| Figure 17 |
Figure 18 then shows the Room Finish Schedule with the data
imported.
Figure 18
As I mentioned at the beginning of the paper, using the
Revit DB Link is not always the most efficient way to populate parameter data.
