Accessing the Autodesk Database Link - Update - Level Names

-->

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.