Chapter 20 Working with External Databases

563

2.On the User DSN tab of the ODBC Data Source Administrator dialog box, choose Add.

3.In the Create New Data Source dialog box, choose Microsoft Access Driver. Choose Finish.

4.In the Data Source Name text box of the ODBC Microsoft Access Setup dialog box, type ab20-Access.

5.Click Select and navigate to your AutoCAD Bible\Databases folder (which you created in the previous exercise). Choose ab20-prt.mdb and click OK.

6.Click OK twice more to exit the ODBC Data Source Administrator.

7.Close the Control Panel.

8.Open AutoCAD. Open ab20-a.dwg from the CD-ROM. Save it as ab20-01.dwg in your

AutoCAD Bible folder.

9.Choose Tools dbConnect to open the dbConnect palette and display the dbConnect menu. From the menu, choose dbConnect Data Sources Configure. In the Configure a Data Source dialog box, type ab20-prt. Click OK.

10.On the Provider tab of the Data Link Properties dialog box (which opens automatically), choose Microsoft OLE DB Provider for ODBC Drivers. Click Next.

11.From the upper drop-down list, choose ab20-Access.

12.Click Test Connection. You see a message saying Test Connection Succeeded. Click OK.

13.Click OK in the Data Link Properties dialog box.

14.Keep ab20-01.dwg open for the next exercise.

Connecting to Your Database

After you configure the database connectivity feature, you’re ready to connect to your database. Here you actually start making connections between objects in your drawing and rows in your database.

Before connecting to your database, you should think about the relationship between the drawing and the database. For example, you should decide:

If the data is to be in one database with many tables or in several separate databases

Which data you want to link to which drawing objects

If several drawing objects will be linked to one row or only one object will be linked to a row

If you want a drawing object linked to more than one row

Which column(s) will identify unique records

You’re now ready to connect your database to your drawing.

564 Part III Working with Data

Connecting a database to a drawing

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 a toolbar. You can 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.

Note The dbConnect Manager is a palette, with the same features as the Properties and DesignCenter palettes. You can dock it or move it outside the AutoCAD application window. Rightclick the title bar and choose Auto-Hide to collapse the palette automatically when you move the cursor off the palette (and expand it again when you move the cursor onto the title bar).

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

1.Open the drawing you want to connect with a database.

2.Choose Tools dbConnect to open the dbConnect Manager. You can also press Ctrl+6.

3.Right-click the data source you want to connect to. (The names listed come from the names you entered when you configured the data source in AutoCAD.) Choose Connect.

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.

STEP-BY-STEP: Connecting a Database to a Drawing

1.You should have ab20-01.dwg open from the previous exercise.

2.Choose Tools dbConnect to open the dbConnect Manager.

3.Right-click ab20-prt and choose Connect. The dbConnect Manager lists the database table PART NO.

4.Save the drawing. Keep it open. Continue to the next exercise.

Chapter 20 Working with External Databases

565

Opening a data table

After your database is connected, you choose the database table you want to work with. If necessary, click the plus sign next to the desired database to see the actual database files 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 view or edit a table in the Data View window:

View the data when you have no need to edit it. To view your data, right-click the table you want to view and choose View Table.

Edit the data when you need to make changes to your database from within AutoCAD. To edit your data, right-click and choose Edit Table.

To view or edit a table, select the table. Choose View Table or Edit Table from the dbConnect 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.)

Record header

Grid header

Column header

Figure 20-5: The Data View window displays your data.

566 Part III Working with Data

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 scrollbars to scroll through the data. To the left of the horizontal scrollbar, 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 record, respectively.

When you open the Data View window, a new DataView item appears on the AutoCAD menu.

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

Resize a column: You may find that your data doesn’t completely display in a column or that columns don’t all appear in the window. Click the grid line to the right of any column header and drag it to the left or right. You can also resize the entire Data View window by dragging on its sides or bottom.

Move a column: You can change the order of the columns by moving a column to another location. Click the column’s header to select the column. Then click and drag the column to the location you want. A red vertical line indicates where the column will land.

Hide a column: You can hide a column you don’t need to work with. This is especially helpful if you have many columns and can’t fit them easily on the screen. Click the column’s header to select the column. Then right-click the column header and choose Hide. To redisplay the column, right-click any column header and choose Unhide All.

Sort records: You can sort records in ascending (low to high) or descending (high to low) order. Sorting helps you find the records you want more easily. Right-click any column header and choose Sort to open the Sort dialog box, shown in Figure 20-6. In the Sort By drop-down list, choose the column you want to sort by first. Then choose Ascending or Descending. This column may have duplicate records. If so, you may want to choose a secondary column for sorting in the Then By drop-down list. Choose Ascending or Descending for the secondary column. AutoCAD will then sort first by the first column and then by the secondary column. You can choose up to five columns to sort by.

Freeze one or more columns: Freezing one or more contiguous columns moves them to the left column. Select the columns by clicking their column headers. (Press Ctrl to select additional columns. You can also select one column, press Shift, and select another column to select all the columns in-between.) Then right-click a selected column and choose Freeze. To return the column to its original location, right-click and choose Unfreeze All.

Align text: You can align text in a column or columns. By default, columns use the Standard alignment, which right-aligns numbers and left-aligns everything else. Select a column or columns by clicking the column header(s), right-click any column header, and choose Align. Then choose Standard, Left, Center, or Right.

Format text: You can format the font, font style, font size, effects (strikethrough and underline), and color of the text in the Data View window. Note that, unlike the other changes previously listed, these changes continue to affect the formatting of the Data View window the next time you open a Data View window. Choose Data View Format to open the Format dialog box. Choose the formatting you want and click OK.

Chapter 20 Working with External Databases

567

Tip

Tip

Figure 20-6: Use the Sort dialog box to temporarily sort your data in the Data View window.

To get the Data View window out of the way, you can dock it. Right-click in the Data View window’s toolbar area and choose Allow Docking. Then drag the window to the right edge of your screen (assuming your dbConnect window is on the left side of your screen).

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:

1.Select any cell in the column that contains the record you want to find. If you want to search the entire column, choose the first or last cell in the column.

2.Choose Data View Find to open the Find dialog box, shown in Figure 20-7. (You can also right-click any cell in the column and choose Find.)

Figure 20-7: The Find dialog box.

3.In the Find What box, type a value (text, numbers, or a combination). Choose to search either Up (from the selected cell to the first record) or Down (from the selected cell to the last record). If desired, choose Match Case. Then click Find Next to find the next instance of the value. Continue to click Find Next to move to the next matching record.

If you want, you can copy your formatting, including sorting, to the Clipboard and paste it into your DBMS. To do this, select the records you want to export. To export the entire database, click the grid header. Then right-click any cell and choose Copy. Open your DBMS and click Paste from the Standard toolbar.

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

568 Part III Working with Data

STEP-BY-STEP: Working with the Data View Window

1.Ab20-01.dwg should still be open from the previous exercise. If there’s a plus sign next to ab20-prt, click it to display the “PART NO” table. The dbConnect window should look similar to Figure 20-8.

Figure 20-8: Displaying a database table in the dbConnect Manager window.

2.Right-click the table icon and choose Edit Table. The Data View window opens. (You may have to wait briefly.)

3.From the menu, choose Data View Format. (A new Data View menu item has appeared.) In the Format dialog box, choose Arial as the font and 10 as the font size. Click OK.

4.The columns are too wide and you can’t see all the columns. Drag the grid line to the right of the PART_NO column and drag it to the left so that the width of the column just fits the width of the part numbers. Do the same with the other columns. To access the right grid line of the last column (UNITS), expand the right side of the entire window. Then resize the UNITS column and shrink the Data View window to fit. Your Data View table should look like Figure 20-9.

5.Right-click the PART_NO column’s header and choose Align Right to right-align the first column.

6.To practice moving around the table, click the right-most arrow at the bottom of the Data View window (to the left of the horizontal scrollbar) to move to the last record. Use the vertical scrollbar to scroll through the database and get an idea of its contents. Click any cell. Click a row header to select an entire row. Click a column header to select an entire column.

7.To sort the records by description and help you find all the angles, right-click the DESCRIPTION column header and choose Sort. In the Sort dialog box, choose DESCRIPTION from the Sort By drop-down list and then click Ascending (if it’s not already selected). Because there are a number of duplicate records in the DESCRIPTION column, choose PART_NO in the Then By drop-down list, which should also be sorted in ascending order. Click OK. AutoCAD sorts the data by description, and then by part number.

8.Say that you want to find part number 9003-242-001. Right-click the top cell in the PART_NO column. Choose Find. In the Find dialog box, type 9003-242-001 and choose Down in the Direction section. Click Find Next. AutoCAD highlights the cell containing that part number.

9.Close the Find dialog box and save your drawing. Keep it open for the next exercise.

Chapter 20 Working with External Databases

569

Figure 20-9: The Data View table after some formatting.

Editing data in 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 twice in the dialog boxes that pop up.

Caution	You should not use the DBMS to edit a database separately while it is connected to
	AutoCAD. If you do edit the database table outside of AutoCAD during an AutoCAD connec-
	tion, you may get system crashes or corrupted data. If you need to edit the database using
	the DBMS, make sure you disconnect the table first in the dbConnect window.