To connect to a database, you need to know the type of database-management system that created the database and the structure of the database itself, along with the folders that contain that structure. In the following exercise, you create a simple structure for a Microsoft Access database table.

STEPS: Creating the Structure for the Database

To start working with external databases, you must tell AutoCAD how to communicate with your database, called a data source. AutoCAD uses ODBC and OLE DB for this communication. AutoCAD provides a sample Microsoft Access file, called db_samples.mdb in AutoCAD's Sample folder, that you can work with to get started.

First, you must install and configure the ODBC Data Source Administrator. To check if the ODBC Data Source Administrator is installed on your computer, open the Control Panel by choosing Start

Figure 20.1. The Microsoft Access database.

To check if you have the required drivers installed, double-click the ODBC item. In the ODBC Data Source Administrator dialog box, you'll see a list of database application drivers. (A database driver contains information about how to connect to your database.) Check to see that your database application is listed. Click OK to close the dialog box.

In most cases, your operating system will include the ODBC Administrator. If you don't have the ODBC 32-bit Administrator, you need to install the Microsoft ODBC Driver Pack. The ODBC Driver Pack 3.0 (or later) is free from the Microsoft Web site at msdn2.microsoft.com/en-us/data/aa937730.aspx. The MDAC (Microsoft Data Access Components) 2.8 download should provide you with everything you need.

The instructions to set up the database vary according to the drivers that you use. The AutoCAD online help contains help on all supported databases. Look in Help (press F1) and click the Contents tab. Double-click Driver and Peripheral Guide, then click Configure External Databases and select Configure External Databases. Click the Procedures tab and choose the link for your database.

To set up your database by using ODBC, follow these steps:

You're now ready to configure your database from within AutoCAD. Follow these steps:

You're now ready to establish a connection between a database and an AutoCAD drawing.

STEPS: Configuring a Microsoft Access Database

You use the dbConnect Manager, shown in Figure 20.4, to perform all the connectivity functions. The dbConnect Manager has its own toolbar, which becomes active when you choose a connected data source. All configured data sources are listed.

Figure 20.4. The dbConnect Manager.

You can dock and undock the dbConnect Manager like any palette. You can also resize it by dragging its right border left or right. After you open the dbConnect Manager, you see a list of open drawings and configured data sources.

Follow these steps to connect an external database to a drawing:

AutoCAD lists the database tables associated with the data source. The entire database structure is now connected, and you can view and work with the database data within AutoCAD. In order to do the following exercise, you must have completed the previous two exercises.

STEPS: Connecting a Database to a Drawing

After your database is connected, you choose the database table with which you want to work. If necessary, click the plus sign next to the desired database to see the actual database files that are available. The database file also displays a plus sign. If necessary, click it to display the database tables within the database file. (A database file can contain more than one table.)

You can view or edit a table in the Data View window:

To view or edit a table, select the table. Choose View Table or Edit Table from the dbConnect Manager's toolbar. You can also right-click the table and choose View Table or Edit Table from the shortcut menu.

Figure 20.5 shows the Data View window when you choose Edit Table. (When you choose View Table, you see the same view but the data cells are gray.)

The Data View window presents your data in a grid, with each piece of data in a cell, like a spreadsheet. You can use the scroll bars to scroll through the data. To the left of the horizontal scroll bar, you can use the arrows to move from record to record. The far left and right arrows with a vertical bar move you to the first and last records, respectively.

Figure 20.5. The Data View window displays your data.

You can temporarily change the way the data is displayed. These changes are discarded after you close the Data View window; they don't affect the data file in your database. Here are your options:

Although you can scroll through your data's records, if you have many records, this can be time-consuming. You can search for a particular record by specifying a desired value. Follow these steps:

In the following exercise, you practice using the Data View window.

STEPS: Working with the Data View Window

After you open your data in Edit mode, you can easily edit the data. You can change the value of any record. You can add or delete records to reflect new or deleted objects in your drawing. Changing a record is as simple as selecting a cell and typing a new value.

To add a new record, right-click any record (row) header and choose Add New Record. AutoCAD opens up a space for a new record at the end of the list of records. Type the data for the new record, tabbing from column to column. (You can also use the right-arrow key to move to the next column.)

To delete a record, right-click the record header of the record that you want to delete, selecting the entire record. Choose Delete Record. You need to confirm the deletion in the dialog box that pops up.

AutoCAD doesn't save your changes until you commit them. To save your changes, right-click the grid header — the cell at the top-left corner of the Data View window — and choose Commit. To discard your changes, choose Restore, which restores the original values of the database when you opened the Data View window.

STEPS: Editing Data

When AutoCAD creates a link, it associates an object or objects with a row in your database. To do so, AutoCAD needs to know which field (column) to look in to identify the row. For example, let's say you want to link price information to some objects. However, several rows may contain the same price information. If you provide a field that contains no duplicate data, AutoCAD can always locate the required row. If AutoCAD finds two rows with the same data, it accesses the first row. It makes sense, therefore, to be careful to choose a column that contains no duplicate values. If your data doesn't contain such a column, most DBMSs can create an index field that ensures that each row is unique.

A link template identifies which fields are associated with a link between the data and a drawing object. A link template also identifies your database. After you create a link template, you can open your data directly from the template, which is listed in the dbConnect window.

If you want to associate data from more than one database table to a single object, you may need to create more than one link template for an object.

To create a link template, follow these steps:

Figure 20.6. The New Link Template dialog box.

You're now ready to link data with drawing objects.

If the structure of your database changes dramatically, you may need to edit a link template. For example, a field that contained no duplicate entries might now contain some. Other changes requiring a change in the template would be a change in the name or length of a field. To edit a link template:

You can delete a link template by right-clicking over the link template and choosing Delete. You can also rename or duplicate a link template by right-clicking the link template and choosing Rename or Duplicate.

This exercise requires that you have completed the previous exercises in this chapter.

STEPS: Creating a Link Template

You're now ready to link drawing objects to your database.

You can link a drawing object to as many records in a database as you want, and you can link one database record to several drawing objects. For example, you may want to link a record containing part-number information to the part in the drawing. However, that part may be made up of a number of objects, such as lines, arcs, and circles. You can link the record to all those objects in your drawing that make up the part. On the other hand, if you have a database of office equipment, you may attach a row representing telephone numbers and telephones to an object in your drawing representing a phone. However, if someone has a two-line phone, you may need to attach two rows to that one telephone object in your drawing.

Here's how to create a link:

AutoCAD provides a message on the command line, for example: 1 Record(s) linked with 1 Object(s). You now have a link between your data and your drawing.

You can delete a link by selecting a linked object and right-clicking in the drawing area. From the shortcut menu, choose Links

Viewing linked objects and rows

After you create a link, you need to be able to see which rows are linked to which objects before you can make decisions regarding either the data or the drawing objects. You can view the link from either side. That is, you can select an object and see which row or rows it is linked to or you can select a row and have AutoCAD select the object or objects to which it's linked. In both cases, you need to have a Data View table displayed.

• 

  Note

  To restore the full data in the Data View window, close the Data View window and then re-open it from the dbConnect Manager palette.

• 

You'll find that the Data View window obscures your drawing. You can drag it to the bottom of your screen, resize it, or dock it.

Tip

When you use View Linked Objects, the selected objects form a selection set. You can then use the results with other commands that allow prior selection of objects. For commands that you must execute before selecting objects, type p

If you want to move from object to object or row to row to view your links one after another, try Auto-View. The AutoView feature automatically highlights either objects, when you choose a row in the Data View window, or rows, when you choose objects in your drawing. However, you can work from only one side of the equation — either rows or objects — at a time. Here's how it works:

• 
• 

When you're in Auto-View, you can't select records. If you click another record's row header to create a link, AutoCAD just tells you 0 Object(s) found matching 0 selected Record(s). Be sure to turn off Auto-View when you go on to another task.

Exporting link information

You can export a list of links and the handle of the associated links' objects. A handle is a unique name that AutoCAD gives to each object in the drawing. (To view an object's handle, at the command line, type list

Follow these steps to export links:

1. Select a linked object, right-click in the drawing area, and choose Link

   

   Tip

   You can select the entire drawing (type all

   

2. In the Include Fields section of the Export Links dialog box, select the fields that you want to include.

3. In the Save In drop-down list, navigate to the folder where you want to save the file.

4. In the Save as Type drop-down list, choose one of the three options: comma-delimited format, space-delimited format, or native database format.

5. In the File Name text box, type a name for the file.

6. Click Save.

   AutoCAD creates the file.

You can use this file to create a schedule of objects with links — but it takes some work. The comma-delimited format seems to work better than the space-delimited format. You'll probably want to edit the file first to remove the object handles. You can open the Multiline In-Place Text Editor and click Import Text to import the output file into your drawing. You can also open the output file, copy it to the Clipboard, and paste it into your drawing. The Import Text method enables you to format the text as you would any multiline text but takes some experimenting to set up in the proper columns. You can also import the file into a spreadsheet and format it before importing it into your drawing. If you used the native format, you can open it in your DBMS, format it, and import it into your drawing. (See Chapter 27 for more information on importing files.)

STEPS: Creating and Viewing Links

1. You should have ab20-01.dwg open from the previous exercise. The Data View window is open with the database visible. The _PART_NO_Link1 link template is current. You can see the entire drawing in Figure 20.8.

   

   Figure 20.8. This drawing of a base assembly for a commercial washing machine has objects that can be linked to the database of parts.

2. To locate the record that you need, in the Data View window click the leftmost arrow next to the horizontal scroll bar to move to the first record. Right-click in the PART_NO column of the Data View window and choose Find. In the Find dialog box, type 9003-242-001. The direction should be down. Click Find Next. AutoCAD finds the record. Close the Find dialog box by clicking its Cancel button.

3. Click the row's header (the arrow to the left of the row) to select the entire row.

4. 

5. Click Link! on the Data View's toolbar.

6. AutoCAD returns you to your drawing. Use a Zoom window to zoom into the area marked

   

7. Select all the objects that make up the angle bracket indicated by

   
8. 
   

   Figure 20.9. The horizontal angle.

9. 

10. Save your drawing and keep it open for the next exercise.

Before creating a label, you need to create a label template. A label template specifies which fields will be included in the label, as well as text formatting. Here's how to create a label template:

After you create the label template, you can create attached and free-standing labels. The settings on the Label Offset tab apply only to attached labels.

You can edit a label template to change the included fields or the formatting. To edit a label template, right-click the label template on the dbConnect Manager and choose Edit. Use the Label Template dialog box to edit the label template by using the same steps that you used to create it. Click OK after you're done.

You create a link between a row and an object and an associated attached label at the same time. You need to have a table open and to have defined both a link and a label template. If you have defined more than one link or label template, choose the desired template(s) from the drop-down list at the top of the Data View window. Follow these steps:

Of course, you can then move the labels and leaders to a more suitable location.

If you've already created a link to an object, follow the same procedure. The link is re-created along with the attached label.

To create a free-standing label, you need to have a table open with at least a link and a label template. If you have more than one template defined, choose the one that you want to use in the drop-down list at the top of the Data View window. Follow these steps:

When you create a free-standing label, the row and the label are linked. If you select a row linked to a free-standing label and click View Linked Objects in Drawing on the Data View's toolbar, AutoCAD selects the label.

If the data in the database is changed from within the DBMS, the labels in your drawing may become outdated. To ensure that your labels are always accurate, you should periodically update them. To update labels, on the dbConnect Manager, right-click the current drawing file and choose Reload Labels (or right-click a label template and choose Reload).

STEPS: Creating a Label Template and a Label

Use the Quick Query tab to create simple queries based on one field, an operator, and a single value. For example, you can create a query that finds all records from the current table where the field DWG_SIZE equals E. You can also find all records where DWG_SIZE does not equal E.

From the Field box, choose a field. In the Operator drop-down list, choose an operator, such as equal (=) or greater than (>). In the Value box, type a value for the field. If you're not sure what values are available, click Look Up Values. (This can be slow in a large database or when you're working with a database across a busy network.)

Choose Indicate Records in Data View and/or Indicate Objects in Drawing. By default, both are checked. To save the query for future use, click Store. Click Execute to see the results. To return to the Query Editor, choose Return to Query on the Data View window toolbar. Table 20.2 lists the available operators.

The Range Query tab finds records based on one field and a range of values. For example, you can find all records where the field DWG_SIZE ranges from B to D. The range can be either textual or numeric.

To construct a range query, choose a field from the Fields list. Then type the beginning value of the range in the From box and the ending value of the range in the Through box. In both cases, you can click Look Up Values to choose from a list of available values.

Click Store to save the query. Click Execute to execute the query.

The Query Builder enables you to create multiple criteria. For example, you can create a query that finds records where the field DWG_SIZE ranges from B to D and the field PART_NO is greater than 9029-072-001. In order to build the criteria, you use Boolean operators and parenthetical grouping. This tab also enables you to specify which fields will appear in the Data View window and to sort the records. Using all these options offers a great deal of flexibility to create a complex query without knowing SQL. Here's how it works:

Here's where the fun is. Try creating a query in one of the first three tabs and then click the SQL Query tab. Lo and behold, there is your query in SQL language. This is a great way to learn SQL.

The SQL Query tab is the only tab you can use to create a query to search more than one database table. Choose a table in the Table section and click Add. You can build the query by specifying fields, an operator, and values. As you work, you see the results in the top box, in SQL language.

The Check button enables you to check the SQL query before executing it. Click Store to save the query. Click Execute to execute the Query.

Although complete coverage of SQL syntax is beyond the scope of this book, you may find a few rules helpful:

The SELECT statement is probably the most common SQL statement. The SELECT statement can retrieve a subset of the rows in one or more tables, retrieve a subset of the columns in one or more tables, or link rows in two or more tables to retrieve common data to both tables.

The following shows the syntax of the SQL SELECT statement, including modifying statements that instruct the DBMS exactly which rows to select:

SELECT <select list>
FROM <table name> [{,<table name>}...]
[WHERE <search condition>]
[GROUP BY <column spec>[{,<column spec>}...]
[HAVING <search condition>]
[ORDER BY <sort spec> [{,<sort spec>}...]

In the preceding syntax, square brackets ([]) indicate optional elements, an ellipsis (...) indicates that the statement may be repeated, and curly brackets ({}) mean that the elements are listed in sequence.

Here is the meaning of the statement functions:

Link Select enables you to create combined selection sets of objects. You can define the selection sets either by using the Query Editor or by selecting objects directly in your drawing, or you can use a combination of these two methods. You first define a selection set called A. Then you define a selection set called B. Then you combine the two selection sets by using logical operators.

You need to have your links already set up so that AutoCAD knows the relationship between your records and objects in the drawing.

As you work, the status area at the bottom of the dialog box displays the results of the running Link Select operation, both in terms of the number of linked objects and the number of records that currently meet your specifications.

Table 20.3 shows how the logical operators work.

The Link Select dialog box contains its own version of the Query Editor so that you can build queries without leaving the dialog box.

To use Link Select, follow these steps:

Figure 20.13. Use the Link Select dialog box to create combined selection sets of objects from queries and/or direct selection in your drawing.

As I discussed previously, you can store a query by clicking Store on any tab. Stored queries are displayed under the drawing in the dbConnect Manager. To execute a stored query, on the dbConnect Manager, right-click over a stored query and choose Execute. It helps if the queries have names that relate meaningfully to the query.

You can edit stored queries by right-clicking over a stored query in the dbConnect Manager and choosing Edit. In the Query Editor, make the desired changes and choose Store.

To rename a query, click it in the dbConnect Manager. Click it again to display an edit box. You can now edit the query's name. Remember that you have to name the query upfront. If you're not sure what the query will consist of, use the default name, construct your query, and then rename it to something more meaningful afterward.

To delete a query, right-click over a stored query in the dbConnect Manager and choose Delete.

To export a query that has been saved (by clicking Store in the Query Editor), right-click the drawing that contains stored queries in the dbConnect Manager and choose Export Query Set. In the Export Query Set dialog box, navigate to the desired folder and choose a name. Click Save. Queries have a DBQ filename extension.

Note

To find the location of your queries, choose Application Menu

To import a query that has been exported and named, right-click over the drawing that you want to import queries into in the dbConnect Manager and choose Import Query Set. You need to know the name and location of the .dbq file. In the Import Query Set dialog box, locate the file and click Open. AutoCAD assigns default names to queries.