In order to create a link to Microsoft Access 7.0
tables from within AutoCAD, you must use the ODBC driver because there
are no direct drivers provided in R14 for MS Access.
The following sections provide general information for setting up and creating a link between AutoCAD R14 and a Microsoft Access database table.
Create a Reference Table in MS Access
Microsoft Access 7.0 does not support catalogs, therefore, you will need to create a new database that contains two tables which will emulate a catalog. When any of the AutoCAD ASE commands send requests through ODBC for Catalog and Schema data, they refer to this reference table to locate the actual database tables you use with AutoCAD.
Follow the steps below to create a new table in MS
1. Load ACCESS and click the ‘Blank Database’ button in the upper Microsoft Access dialog box.
In the ‘File New Database’ dialog box, select ‘d:\access’ as the directory in which to save the file; name the file ‘infsch7.mdb’, and click the Create button. An infsch7:database dialog box will appear.
In this dialog box, click the New button and select Design View in the New Table dialog box. Click OK.
The cursor should now appear in row 1 of the Field Name column. Enter ‘CATALOG_NAME’ (no quotes) as the field name. Accept ‘text’ as the data type in Data Type column. Repeat this procedure to enter a ‘SCHEMA_NAME’ field.
The field structure should look like:
2. Exit by clicking File and Close, and when prompted to save the table, name it SCHEMATA.
3. When asked whether to create a primary key, choose No.
4. Open the table SCHEMATA and insert the text NULL in the CATALOG_NAME field, and enter the full path to the database you wish to use with AutoCAD in the SCHEMA_NAME field. You can include as many databases as you need in this table that adhere to this format.
For example, the table should appear as:
Where access is the name of the directory containg the database file gran1.mdbyou are going to create.
Note: Do not include the file extension .MDB in the SCHEMA_NAME field.
5. Create another new table with the following field names and specify the data type TEXT for each one.
6. When prompted to save the table, enter the name TABLES.
7. When asked whether to create a primary key, choose No.
8. Open the table named TABLES and insert the following data for each field. Insert the text NULL for the TABLE_CATALOG field, and enter the full path to the database in the TABLE_SCHEMA field, the table you wish to use under the TABLE_NAME field, and the text BASE TABLE for the TABLE_TYPE. Continue to the next row for the next table you plan to use in AutoCAD.
For example, to use the tables stat1 and samples in the sample database gran1.mdb, the values to enter would be as follows:
Table_Catalog Table _Schema Table_Name Table_Type
NULL c:\access\gran1 stat1 Base Table
NULL c:\access\gran1 samples Base Table
Note: Do not include the extension .MDB for the database file listed in the TABLE_SCHEMA field.
9. Save and exit Microsoft Access 7.0.
Load ACCESS and click the ‘Blank Database’ button
in the upper Microsoft Access dialog box.
In the ‘File New Database’ dialog box, select ‘d:\access’ as the directory to save the file, name the file ‘gran1.mdb’, and click the Create button. A gran1:database dialog box will appear.
In this dialog box, ,click the New button and select Design View in the New Table dialog box. Click OK.
The cursor should now appear in row 1 of the Field Name column. Enter ‘id’ (no quotes) as the field name.
Click the cell beneath the Data Type column, click the scroll box button at the right side of the entry field, and select number as the data type. Repeat this procedure to enter an ‘area number’ and ‘perimeter number’ field in rows 2 and 3, respectively.
Exit by clicking the X button (top right hand corner) or clicking File > Close, and ‘Save as’ the table with the name ‘stat1’ (do not add the quotes).
Decline the invitation to create a primary key, because you will use the ‘id’ field as the primary key.
Open the stat1 table by clicking Open in the gran1:database dialog box, and enter three rows of numeric data, e.g.
1 24 300, etc.
Close the table with File > Close, and the file with File > Close second time. Exit ACCESS with File > Access.
Installing and Configuring the Windows ODBC Driver
The installation process for the ODBC v3.0 Driver Kit allows you to choose an ODBC driver for MS Access 7.0 from a list of commonly used databases.
Click START, Settings, Control Panel to bring the Control Panel to the screen. Click the ODBC icon to get the ODBC dialog box. Click the Add button, select the Microsoft Access driver, and click the Finish button. In the ODBC Microsoft Access 97 Setup box, supply a descriptive data source name in the 'Data Source Name' field for the Access driver, such as ODBC95_ACCESS.
Next, choose the Select button in the database section of this dialog box and specify the database reference file created in the preceding section. For this example, the database name is d:\access\infsch7.mdb. Click the OK button twice.
Setting the Database Link
Launch the External Database Configuration editor by double-clicking on the External Database Configuration icon in the AutoCAD Release 14 program group (Start .> Programs >Acad Map R2 > External Database Configuration). (This configuration utility runs independently of AutoCAD.) The External Database Configuration dialog box will appear
1. Press the Add button to create a new ODBC environment definition
with a name you will use for your ODBC Environment. Refer to the Database
Source Name you specified in the ODBC Microsoft Access 7.0 Driver Setup
2. Select ODBC from the DBMS List. When you make this selection, you are choosing to use the AutoCAD ODBC database driver for this environment.
3. Enter the environment name you set up in the ODBC section Data Source Name. Then choose OK. The Environment dialog box will appear, in which you will define the Information Schema.
Optional: You can enter an environment description in the Environment Description window, although you can leave this window empty if you do not want to provide a description.
An MS Access connection to AutoCAD Release 14 requires you to create information schema tables within an MS Access .mdb file. This document contains an example of information schema tables that are stored in one central MS Access database file.
The following are the steps for specifying the information schema in the External Database Configuration Editor:
1. Press the Browse button next to the Information Schema window in the General Tab of the ASI Environment dialog box. Use the file and directory browser to navigate to the directory that contains the information schema database. In this example, it is the infsch7.mdb file.
2. Select the information schema database file and choose OK. The full path to this file should appear in the Information Schema window.
3. Press the Apply button at the bottom of the ASI Environment dialog to commit this entry to the Windows Registry.
You can either choose the OK button to return to the External Database Configuration dialog box to test your connection, or you can repeat the steps described above to create additional environment settings.
Testing your Environment
Once you have provided all of the necessary information in the ASI Environment dialog box, press the OK button to save the environment definition. The first dialog box, the External Database Configuration, will re-appear where you can see the environment definitions in the Environments list.
To test your environment, highlight your environment name and press the Test button. You are then prompted to enter a user name and password for the database files. If you have not defined a user name and password, you can leave these fields blank and press the OK button. A test will be performed to determine if the database driver is present and can be loaded into memory. If the test is successful, a list of driver characteristics will appear. This list describes the functionality your driver provides.
Note: This test does not verify the location or the contents of the information schema tables; it is only a test of the ODBC driver itself.
Specifying Additional Environment Settings
The ASI Environment dialog box for an ODBC environment contains a number of choices you can make when you define your environment. Some of these choices are available for an ODBC environment for MS Access, whereas these choices do not exist for other databases.
Explanation of Choices Available in the ODBC ASI Environment dialog box
The Set Schema values are supported or not supported. Supported means the target database contains information schema functionality. MS Access does not contain this functionality, which is why you must create an information schema manually in MS Access. You can either leave this window blank or set the value to not supported.
The Default Catalog option cannot be used for an ODBC connection to MS Access. There is no equivalent for Catalog in this type of environment. Therefore, anything you enter into the Default Catalog window will not be re cognized. It is acceptable to leave this window empty.
If you wish to make one of your defined schemas a default schema, you can enter that schema name into this window. Schema, in the case of an ODBC / MS Access environment, is the value from the TABLE_SCHEMA column in the SCHEMATA table in the information schema. This value appears in the table as a path to an MS Access database file. To specify a default Schema, enter the entire path and file name (without the .mdb extension), into this window. This value should be enclosed in quotation marks. For example, if the value in your information schema table is gran1, you would enter the following into the Default Schema window:
When you set a default Schema as described above, that schema is pre-selected in the ASE Administration dialog when you connect to this environment. That is, at the time you connect to this environment, the Catalog and Schema will be pre-selected, and you will be shown the list of tables associated with the Default Schema.
The Autocommit variable has two settings: ON and OFF. (These are selectable from the drop-down list next to the Autocommit window.) When Autocommit is ON, transactions will automatically be committed to the database after each SQL statement is executed. When Autocommit is OFF, you must manually commit SQL changes by pressing the Commit button in the ASE dialog box.
The Timeout setting is a variable in seconds. When you specify a timeout value, the driver will wait that number of seconds for a database lock to be released. If the lock is released before the timeout value expires, you will access the database record. If the timeout value passes and the lock is still in place, you will be informed that the record is locked.
Schemas and Tables Tab of ODBC ASI Environment dialog box
If you define an ODBC environment that uses an information schema, the values in the information schema will override any values entered into the windows in the Schemas and Tables tab. It is acceptable to leave all of the windows in this tab empty.
If you specify any of the optional settings in these dialog boxes, press the Apply to commit the settings to the Windows Registry. Next, you can either test your defined environment, as described in Testing Your Environment section, or you can close the c onfiguration editor by pressing the OK buttons, and launch AutoCAD and connect to your MS Access data using this defined environment.
Accessing your Table Data in AutoCAD
If you add an environment definition using the external database configuration tool, you must reload the ASE portion of AutoCAD before you will see this environment. This means that if you have AutoCAD running at the same time you are using the configuration utility, you must either exit and re-launch AutoCAD, or you must unload and reload the ASE kernel.
The ASE portion of AutoCAD is now an AutoCAD Runtime Extension, or ARX, extension. ARX applications can be unloaded and reloaded inside AutoCAD by using one of the following methods:
• Enter the ARX command at the command line.
• Enter UNLOAD at the ARX sub-command prompt.
• Enter ASE as the name of the ARX application to be unloaded.
To reload the ase.arx kernel, enter any of the ASE commands, such as ASEADMIN, ASEROWS, etc.
Note: It is not possible to unload the ASE ARX application in AutoCAD Map. In order to reinitialize the ASE portion of AutoCAD Map with any new entries made in the configuration editor, you must close and re-open AutoCAD Map.
To export an object data table to an external database
From the Map menu choose Map Tools > Convert Object
data to ASE.
In the Object Data to ASE Conversion dialog box, under source Object Data Table, select an object data table.
e.g. TPMCNTR_gran2top for topological centroid data stored on layer gran2top.
Click the Define button in Target Line Path Name. Select the environment, e.g. ODBCNT_ACCESS, that links Access to Autocad Map, select the Schema, e.g d:\access\gran2, type in the Table Name, select the Key Column, and provide a Link Path Name, e.g. cntgran2. Click OK. The Object Data to ASE Conversion Dialog box should reappear.
Click the select button in the Object Data to ASE Conversion Dialog box, and click each centroid on the diagram whose object data is to be transferred to the external database. Press ENTER when finished, followed by clicking the Proceed button. IF an ‘SQL table stat1 exists. Overwrite table?’ message appears, click the YES button to complete the transfer. (Note: the environment parameters e.g. schema name in the SCHEMATA table and SCHEMA-NAME and TABLE NAME in the TABLES table, must still be set up in the ‘infsch7.mdb’ file, and the relevant environment must be set in the SQL Administration dialog box.
To check that the database has been created correctly, choose Map > SQL(ASE) > SQL Editor. Insert the SQL statement ‘Select * from stat1’ and Execute the command. The SQL Cursor dialog box should appear showing the first record in the database file. Your can scroll through the database record using the Next button.
Question: how does one append object data to a database.
To view the data in the database corresponding
to a given centroid, choose Map SQL (AE) > Links. Click the centroid whose
object data is to be examined. The Links dialog box will appear. Select
the Environment and the Link Path Name, and click the Rows button.
To carry out the reverse process, that is to find an object corresponding to a given database record, choose Map > SQL > Rows. In the Rows dialog box, select the environment parameters (clicking the correct Link Path Name automatically selects the other parameters) and click the Scrollable Cursor State button. Enter the statement, e.g. id=5, to select the record, and then the Open Cursor button. Finally click the select button. The corresponding map object will be highlighted.
Linking a drawing to an ACCESS database
File - :\aacrse\acad\accessam\readme.txt
To link a record in an external data base to an object
on the map.
Map -> SQL -> Rows -> click scrollable button -> Open Cursor -> Make Link -> select the centroid to be linked -> click the Next button to select the next record and repeat.....
To see which database record is linked to an object on the map.
Map -> SQL -> Rows -> click scrollable button -> Graphical -> select object by clicking it.
To highlight an object on the map corresponding to
a record in the database.
Map -> SQL -> Rows -> click scrollable button -> Open cursor -> Select -> press Enter -> click OK. The object will be highlighted in blue. Use next to choose another record.
To find an object corresponding to a specific ID
Map -> SQL -> Rows -> Key Value -> enter the id value -> press enter -> click OK.