AutoCAD offers a way to access an external database from within AutoCAD through the dbConnect Manager. With the dbConnect Manager, you can read and manipulate data from external database files. You can also use dbConnect Manager to link parts of your drawing to an external database.
There are numerous reasons for doing this. The most obvious is to keep inventory on parts of your drawing. If you are an interior designer doing office planning, you can link inventory data from a database to your drawing, with a resulting decrease in the size of your drawing file. If you are a facilities manager, you can.track the movement of people and facilities using AutoCAD linked to a database file.
This section will avoid the more complex programming issues of database management systems, and it does not discuss the SQL language, which can be used to query and edit your database files through dbConnect. Still, you should be able to.make good use of the dbConnect Manager with the information provided here. You’ll also be departing from the studio apartment example to make use of an office plan example that has already been created by Autodesk.
Setting Up Your System for Database Access
The dbConnect Manager doesn’t create new database files. You must use existing files or create them yourself before you use this tool. In addition, you will need to set up a Data Link file that will direct AutoCAD to the database file you want to work with. You can create a Data Link file through the Microsoft Data Link dialog box. The Data Link dialog box lets you create and manage Data Link files. Once you’ve created and set up a Data Link file, you can begin to access and link databases to AutoCAD drawings.
In the first set of exercises below, you’ll learn how to create a new Data Link file that tells AutoCAD where to look for database information, Then for the rest of the tutorial, you will use a Microsoft Access file provided to you from the CD-ROM included with this book. The file is called db-Master sample. mdband it is installed when you install the sample figures from the CD-ROM. Look for it among those sample figures.
The db-mastersample. mdbfile contains three tables: Computer, Employee, and Inventory. These are based on the sample dB.lse files offered with earlier versions of AutoCAD. The contents of Employee table are shown.
Creating a Data Link File
A Data Link file is like a switch board that connects applications to database files. The application can be anything that requires Data Link files for database connections, not just AutoCAD. You can have as many Data Link files as you need for your application, and as you’ll see, you’ll be able to access all of them from the AutoCAD
1. Using Windows Explorer, locate the Data Link folder under the main Auto- CAD folder. Typically, this would be C:\Program Fi 1es\AutoCAD2000 \Data Link.
2. Right-click a blank area in the folder listing, then in the popup menu, choose New >- Microsoft Data Link. A new file appears called New Microsoft Data Link.UDL.
3. Rename this file My A cad Data Link. UDL.
You’ve just created a Data Link file. You may have noticed two other UDL files in the Data Link folder. These are sample files provided by Autodesk that allow . you to explore AutoCAD’s dbConnect feature on your own.
Configuring Data Links
Next, you’ll configure your new Data Link file to locate the sample database files.
1. With the Data Link folder open, double-click MyAcad Data Link. UDL.The Data Link Properties dialog box appears.
You have two basic options in this dialog box. You can connect to a database through the Data Source Name (DSN) setting that is controlled through the ODBC Data Source Administrator in the Windows Control Panel, or you can create a link to a database directly from this dialog box. In this exercise, you’ll use the Build option to create a database directly through this dialog box.
2. Click the Use Connection String radio button.
3. Click the Build button next to’ the Connection String input box, The Select Data Source dialog box appears.
4. Click New next to the DSN Name input box. The Create New Data Source wizard appears.
5. Select Microsoft Access Driver (*.mdb), then click Next.
6. On the next page, click the Browse button.
7. In the Save As dialog box, enter My Acad Access Database, then click Save. The previous dialog box reappears.
8. Click Next, then in the last dialog box, click Finish. The OOBC Microsoft Access 97 Setup dialog box appears.
You’ve just created a file DSN that will direct AutoCAD to the database of your choice. But once a file DSN is created, it needs to be configured. This.is where you indicate the exact location of the database file you want to use with this setup.
1. Click the Select button in the Database group. The Select Database dialog box appears. This is a standard file dialog box.
2. Locate db-Mastersample .mdband select it. If you followed the installation instructions in Appendix C for installing the figures, db-Mastersample. mdb will be located in the \Program Files\AutoCAD2000\ Figures folder.
3. Click OK in the ODBC Microsoft Access 97 Setup dialog box. The Select Data Source dialog box reappears .
4. Select MyAcad Access Database. dns from the list, then click OK.
5. Click OK in the ODBCMicrosoft Access 97 Setup dialog box.
6. Click OK in the Data Link Properties dialog box.
Most of the steps you took in this exercise are the same steps you would take to create a file DSN through the ODBC Data Source Administrator in the Windows Control Panel. The Build button is a convenient way to access the ODBC Data Source Administrator through the Data Link Properties dialog box. See Appendix B for more on how to use the ODBC Data Source Administrator.
Opening a Database from AutoCAD
Now you’re ready to access your database files directly from AutoCAD. In the following exercise, you’ll take the first step by making a connection between a database table and AutoCAD.
1. Open the dbSample. dwg file from \Program Files\AutoCAD2000\Figures directory. This is the directory where the sample files from the Mastering AutoCAD CD are stored.
2. Choose Tools> db Connect or click the dbConnect tool on the Standard toolbar.You can also type dbc↵.
The dbConnect dialog box appears.
3. Right-click My Acad Data Link. Then in the popup menu, select Connect. The directory listing of the tables in the Access database file appears below by Acad Data Link.
4. Click the Edit Table tool in the dbConnect dialog box, or right-click the Employee listing, then select Edit Table from the popup menu .
The Data View dialog box appears. Your view of the Data View dialog box may be wider that the one shown here.
You are now connected to the Employee table of the db-Mastersampl e .mdb database.
Take a moment to look at the dbConnect dialog box. You may need to move the Data View dialog box out of the way for the moment. There is a set of buttons along the top. These buttons duplicate the options you saw in step 4 in the popup menu. Also notice that you now have two more options in the menu bar: dbConnect and Data View. Most of the options in these two pull-down menus are duplicated in other parts of the dbConnect and Data View dialog boxes.
You’ve already seen that dbConnect’s Edit Table tool opens the Data View dialog box to allow you to edit a database table. The View tool opens a view of the database, without allowing you to edit anything. You’ll learn about the functions of the New Link and New Label template options later in this tutorial.
Finding a Record in the Database
Now that you are connected to the database, suppose you want to find the record for a specific individual. You might already know that the individual you’re looking for is in the Accounting department.
1. Click the Query icon in the Data View toolbar.
The New Query dialog box appears.
2. Type My first query in the New Query Name input box. You can save queries under different names in case you need to repeat a query later.
3. Click Continue. The Query Editor dialog box appears.
‘The Query Editor dialog box lets you set the criteria for your query. As you can see from the numerous tabs, you can use several methods to query the database. Try using the Quick Query method, which is the tab already selected.
4. Highlight DEPT in the Field list box.
5. Click the Look Up Values button just below the Value input box. A listing of the DEPT categories appears.
6. Select Accounting from the list, then click OK.
7. Click Store. This saves the current query under the name that you entered in the New Query dialog box. The name “My first query’: appears in the dbConnect dialog box, just under the drawing name.
8. Click Execute. The Data View dialog box changes to show only the Accounting department records .
9. After reviewing the results of this exercise, right-click the Employee listing in the dbConnect dialog box•.then select Edit Table to restore the view of the entire table.
You’ve just seen how you can locate and view a set of records in a database. If you want to edit any of those records, you only need to highlight the field of a record you want to change, then enter a new value. If you open the Data View window using the View option in the dbConnect dialog box.you are locked out of making changes to your database. In some cases, the View option is the only option available to access a database. You may be limited to the View option if the database is locked or if the ODBC driver for a database does not allow editing.
You used the Store option to store your query. By storing a query, you can quickly re-execute the query from the dbConnect dialog box. To do this, right-click the query name “My first query” in the dbConnect dialog box, then select Execute. You can also make modifications to a stored query. Right-dick the query name in the dbConnect dialog box, then select Edit Query from the popup menu. The Query Editor dialog box appears, allowing you to make changes and re-execute your query. Other options in the popup menu allow you to rename the query or delete it altogether.
The ability to access databases in this way can help you connect AutoCAD graphic data with database information. For example, you may want to keep track of tenant information in your studio apartment building. As you will see later, you can actually link graphics to database records so you can quickly access data regarding a .particular tenant. Another application might be generating a bill of materials for a mechanical project, where records in the database relate to parts o. in a mechanical assembly.
Adding a Row to a Database Table
Now let’s get back to our office example. Suppose’ you have a new employee who needs to be set up in an office. The first thing you need to do is add his or her name to the database. Here’s how it’s done.
1. In the Data View dialog box, right-click any button to the far left of the table, then select New Record from the popup menu. A blank row appears and the cursor appears in the first field of the row for entering data.
2. Enter the following data in the blank row. To add an item, click the appropriate field, and then enter the new data. After you type in the new data, press the Tab key to move to the next field.
3. Press ↵ after entering the last entry. You’ve just added a new record to the database.