Chapter 20 Working with External Databases

557

Some database systems use environments, catalogs, schemas, and tables to create a hierarchy of database objects. A database object is simply the term used to specify any of the following SQL2 objects: Environment, Catalog, Schema, or Table.

The environment is the entire database system — the DBMS, the databases it can access, the users, and the programs that can access those databases.

A catalog is a collection of schemas and has the same name as the folder where the database is located.

A schema is a set of tables and other database components and has the same name as the catalog subfolder where the database tables reside.

You don’t need to work with these concepts if your database system does not require or specify it. AutoCAD can connect to an individual table or to a collection of tables stored in an environment, catalog, or schema.

Preparing for Database Connectivity

Database connectivity involves several components that you need to prepare in advance. When they are in place, the connection should go smoothly. In this section, I explain the necessary preparation steps.

The basic steps for starting to work with database connectivity are as follows:

1.Make sure you have installed the Database feature of AutoCAD.

2.Make sure you have the ODBC Data Source program from Microsoft.

3.Arrange your database tables into catalogs (folders) and schemas (subfolders) appropriate for your application, if necessary.

4.Configure the appropriate database driver using Microsoft’s ODBC (Open Database Connectivity) or OLE DB programs.

5.Configure your data source from within AutoCAD.

6.Start the dbCONNECT command.

7.Establish a user access name and password, if required by the database system.

8.Connect to your data source.

9.Open the Data View window containing your data table.

10.Edit the data, if desired.

11.Link database rows to objects in your drawing.

12.Create labels based on your data in your drawing, if desired.

Installing AutoCAD’s database feature

Database connectivity is not part of the Typical AutoCAD installation. To use database connectivity, you must either do a full installation or a custom installation that includes database connectivity. If you aren’t sure whether you installed the database feature, open the Tools menu. If you see a dbConnect menu item, you installed it.

558 Part III Working with Data

If you didn’t install database connectivity and want to, follow these steps:

1.Choose Start (Settings) Control Panel to open the Control Panel. (The steps may vary slightly depending on your version of Windows.)

2.Choose Add or Remove Programs.

3.Select AutoCAD 2005 and click Change.

4.In the AutoCAD 2005 Setup dialog box, choose Add or Remove Features. Click Next.

5.In the next window, click the Database arrow and choose one of the options to install this feature on the hard drive. Click Next.

6.Continue to follow the on-screen instructions to install database connectivity.

Organizing the database structure

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.

Note In this chapter, you cannot do the later exercises without doing the previous ones. The later exercises depend on the setup and configuration you create in the earlier exercises. You should, therefore, leave enough time to do all the exercises in this chapter at one sitting — perhaps an hour or two.

On the	The file used in the following Step-by-Step exercise on creating the structure for the
CD-ROM	database, ab20-prt.mdb, is in the Drawings folder on the CD-ROM.

STEP-BY-STEP: Creating the Structure for the Database

1.Right-click Start on the task bar. Choose Explore to open Windows Explorer.

2.If your AutoCAD Bible folder is not displayed in the Folders window, click the plus sign (+) next to the drive containing the AutoCAD Bible folder.

3.Click the AutoCAD Bible folder and choose File New Folder from the Explorer menu. A new folder appears in the right window, called New Folder. Type Databases to rename the folder.

4.If necessary, in the Folders window, click the plus sign to open the AutoCAD Bible folder. You should see the new Databases folder. (If not, press F5 to refresh the Explorer view.)

5.From the CD-ROM, copy ab20-prt.mdb to the Databases folder you just created. Be sure to choose the ab20-prt.mdb file, not the ab20-prt.xls file. The .mdb file is a database of parts. Figure 20-1 shows this database as it appears when opened in Microsoft Access 2002.

6.Because this file is coming from a CD-ROM, you may need to change its read-only property. Still in Explorer, right-click ab20-prt.mdb and choose Properties from the menu. Uncheck Read-Only and click OK.

7.Click the Close button of Explorer to close it.

Chapter 20 Working with External Databases

559

Figure 20-1: The Access database.

Thanks to Gary Morris of the Dexter Company, Fairfield, Iowa, for this database.

Configuring a data source

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 Control Panel (or as required by your operating system) and look for one of the following items:

ODBC Data Sources (32-bit) or Data Sources (ODBC)

Administrative Tools Data Sources (ODBC)

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. Check to see that your database application is listed. A database driver contains information about how to connect to your database.

560 Part III Working with Data

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 http://msdn.microsoft.com/data/ downloads/default.aspx.

Click the Product and Update Downloads link and then the MDAC Downloads (Microsoft Data Access Components) link should lead you to everything you need.

The instructions to set up the database vary according to the drivers you use. The AutoCAD online help contains help on all supported databases. Look in Help Help and click the Contents tab. Double-click Driver and Peripheral Guide and then Configure External Databases.

Click the Procedures tab and choose the link for your database.

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

1.From the Windows task bar, choose Start Settings Control Panel.

2.Double-click the ODBC Data Sources icon. (You may have to double-click Administrative Tools first.) Windows opens the ODBC Data Source Administrator dialog box, shown in Figure 20-2.

3.Click the User DSN tab if it isn’t already displayed. Choose Add.

4.In the Create New Data Source dialog box, choose the driver appropriate for your database and click Finish.

Figure 20-2: Use the ODBC Data Source Administrator dialog box, accessed from the Windows Control Panel, to choose a database driver to connect to your database.

5.In the ODBC Setup dialog box, which is now titled with the name of the driver you chose (for example, ODBC Microsoft Access Setup), type a name for your data source in the Data Source Name text box. You can also add a description. In general, this name refers to your database program, not the individual database file.

Chapter 20 Working with External Databases

561

6.Click Select and navigate to the folder containing your database. Choose the database file and click OK.

7.Click OK again in the Setup dialog box.

8.In the ODBC Administrator dialog box, your data source is listed with its appropriate driver. Click OK. Close the Control Panel.

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

1.Choose Tools dbConnect to open the dbConnect palette and display the dbConnect menu.

2.From the menu, choose dbConnect Data Sources Configure. In the Configure a Data Source dialog box, type a name representing your database file. Click OK.

3.On the Provider tab of the Data Link Properties dialog box, choose Microsoft OLE DB Provider for ODBC drivers. Click Next.

4.From the drop-down list on the Connection tab, choose the name of the data source you used in the ODBC Setup dialog box, as shown in Figure 20-3.

Figure 20-3: Use the Data Link Properties dialog box to configure your data source within AutoCAD.

5.For server-based databases, enter the user name and password.

6.Click Test Connection. You should see a message saying Test Connection Succeeded. (If not, check your settings as well as the spelling and case of the name of the data source.) Click OK.

7.Click OK in the Data Link dialog box.

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

562 Part III Working with Data

Connecting to an Excel spreadsheet

If you have a database in Excel and want to practice working with Excel, you may use the Excel spreadsheet database on the CD-ROM. Here’s how:

1.Follow the steps in the exercise “Creating the Structure for the Database.” In Step 5, copy the ab20-prt.xls file instead of the .mdb file. If necessary, uncheck the read-only property as described in Step 6.

2.Double-click the ODBC item in the Control Panel to open the ODBC Data Source Administrator dialog box. (You may have to choose Administrative Tools in the Control Panel and then ODBC Data Source.)

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

4.In the Create New Data Source dialog box, choose the Microsoft Excel driver and choose Finish.

5.In the ODBC Microsoft Excel Setup dialog box, type ab Excel as the Data Source Name. (If you want, type a description.)

6.Click Select Workbook. Use the dialog box to choose the AutoCAD Bible\databases\ ab20-prt.xls worksheet. Click OK three times and close the Control Panel.

7.Open AutoCAD. Open ab20-a.dwg from the CD-ROM. Save it as ab20-01.dwg in your AutoCAD Bible folder. Choose Tools dbConnect.

8.Choose dbConnect Data Sources Configure. In the Configure a Data Source dialog box, type ab Excel. Click OK.

9.On the Provider tab of the Data Link Properties dialog box, choose Microsoft OLE DB Provider for ODBC drivers. Click Next.

10.From the upper drop-down list, choose ab Excel.

11.Click Test Connection. At the Test Connection Succeeded message, click OK.

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

You can continue the rest of the exercises without change.

The equivalent of a table in Excel is a named range. You need to open your worksheet, select all the data, and type a range name in the Name box at the left end of the Formula bar. To set up the database, put a field name in the first row of each column.

On the	The drawing used in the following Step-by-Step exercise on configuring a Microsoft Access
CD-ROM	database, ab20-a.dwg, is in the Drawings folder on the CD-ROM. This exercise requires
	that you have completed the steps in the previous exercises.

STEP-BY-STEP: Configuring a Microsoft Access Database

1.From the task bar, choose Start Settings Control Panel. Double-click the ODBC Data Sources icon. (Your item may have a slightly different name.) If you don’t have this item, double-click the Administrative Tools icon and look for the ODBC item there.