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
Access:
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:
CATALOG_NAME TEXT
SCHEMA_NAME
TEXT
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:
CATALOG_NAME SCHEMA_NAME
NULL d:\access\gran1
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.
TABLE_CATALOG TEXT
TABLE_SCHEMA TEXT
TABLE_NAME TEXT
TABLE_TYPE TEXT
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
Manager
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
dialog box.
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
Set Schema
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.
Default Catalog
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.
Default Schema
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:
"d:\access\gran1"
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.
Autocommit
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.
Timeout
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.....
Once linked:
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
value.
Map -> SQL -> Rows -> Key Value -> enter the id value -> press enter
-> click OK.