This EXCEL file and the ACCESS documentation file (which has been summarized in 505\acad\accessmap.rtf; and in misc.ask 12:14:58 02 OCT 98) has been archived in D:\ACADMAP and iomega 350Y (gsmcad) ACADMAP
- Linking Microsoft Excel 7.0 to AutoCAD Release 14
Summary: This document explains how to link Microsoft Excel 7.0 to AutoCAD R14. It also includes instructions to install and configure the Windows ODBC Driver Manager.
Keywords:
DATABASE LINK ASE ASI ODBC CONNECT EXCEL 5334
Related documents:
None
Document: CH-GP-TD205334.DOC
Revision: A
Creation date: March 4, 1998
Last revised: September 3, 1998
Expires: September 30, 1999
The tips, tricks, examples
and suggestions outlined in Autodesk Product Support technical documents
are suggested for use at your own risk. Document contents are subject to
change without notice. Autodesk is not responsible or liable for damage
or events that may occur as a result of following suggestions from any
Autodesk Product Support technical document.
Table of Contents
Introduction
Requirements for AutoCAD R14 and Microsoft
Excel 7.0
Linking to Microsoft Excel 7.0 Spreadsheets
General Information
Create a Reference
Spreadsheet in Microsoft Excel
Installing and
Configuring the Windows ODBC Driver Manager
Setting Up the
Database Link
Testing Your
Environment
Specifying Additional Environment Settings
ODBC ASI Environment
Dialog Box (General Tab)
ODBC ASI Environment
Dialog Box (Schemas and Tables Tab)
Accessing Your Table Data in AutoCAD
Introduction
There are two types of database driver supplied
with AutoCAD R14 and AutoCAD Map R2.
Drivers that connect directly to specific
database applications.
For example, Oracle and dBASE drivers.
ODBC drivers that connect to database applications
which support the ODBC (Open DataBase Connectivity) interface.
For example, Microsoft and several other third-party
developers provide ODBC software packages that include interfaces for many
databases.
This document provides a general overview of how
to setup AutoCAD R14 ASE and link to Microsoft Excel 7.0 spreadsheets.
Requirements for AutoCAD R14 and Microsoft Excel
7.0
These are the requirements for linking ASE to Microsoft
Excel 7.0.
AutoCAD R14 or AutoCAD
Map R2.
Windows 95, Windows
NT 3.51 or Windows NT 4.0 operating system.
Microsoft Excel 7.0.
Microsoft ODBC Driver
Kit version 3.0 or higher.
Please be aware that other versions of the ODBC
driver kit are available from Microsoft. However, these are designed to
work with 16-bit Windows only. The only version of the MS ODBC driver kit
that works with
Windows 95, Windows NT 3.51 and Windows NT 4.0 is
version 3.0, which is a 32-bit Windows driver.
The examples used in this document are based on
the MS ODBC Driver Kit version 3.0.
Linking to Microsoft Excel 7.0 Spreadsheets
The following sections provide general information
for setting up and creating a link between AutoCAD R14 or AutoCAD Map 2,
and a Microsoft Excel 7.0 spreadsheet.
General Information
To create a link to Microsoft Excel 7.0 tables from
AutoCAD R14 or AutoCAD Map R2, you must use the ODBC driver. That’s because
there are no direct drivers provided for Microsoft Excel.
Create a reference table in Microsoft Excel
Microsoft Excel does not support ISO SQL2 database
catalogs. Therefore, you must create a spreadsheet that includes path information
to the Microsoft Excel table.
Obtain ODBC Driver Kit version 3.0
Install and configure the MS ODBC Driver Kit version
3.0. Note that you need to obtain updated ODBC drivers, if your existing
drivers are dated prior to April 23, 1996 or earlier than the ODBC Driver
Kit version 3.0
You can obtain updated ODBC drivers from the Microsoft
Web site.
http://www.microsoft.com/data/odbc/
[2 September, 1998]
Note Microsoft
released an updated ODBC driver on April 23, 1996. This update addressed
problems with ASI/ASE connections to Access 7 and other 32-bit DBMS using
ODBC 3.0 drivers. The main symptom, is that the connection works fine,
but data retrieved and/or inserted by AutoCAD is corrupt. Typically, the
value in the first column is repeated in the other columns of a row, and/or
numeric values are completely inaccurate.
Use the External Database Configuration Editor
AutoCAD R14 and AutoCAD Map R2 have an External Database Configuration
Editor that runs independently. This Editor provides a user interface that
makes it easy to setup your database configuration environment in AutoCAD
R14 or AutoCAD Map R2.
The External Database Configuration Editor is found in the AutoCAD R14 or AutoCAD Map R2 program group. If the Editor is not there, it probably wasn’t installed. The Editor is only installed if you select either the Full installation, or Custom Installation options during AutoCAD setup. Please refer to the AutoCAD R14 or AutoCAD Map R2 Installation Guide for more information about installing the External Database Configuration Editor.
Modifying or Deleting Database Configurations
The information you enter in the External Database Configuration Editor
is stored in the Windows Registry. If you need to remove or modify a database
configuration, make the changes in the External Database
Configuration Editor. This ensures the registry
is correctly updated.
Important! If you uninstall AutoCAD R14 or
AutoCAD Map R2, all information entered in the External Database Configuration
Editor is removed from the registry. It is recommended that you record
all information entered in the Editor, before performing an uninstall.
Create a Reference Spreadsheet in Microsoft Excel
You need to create a spreadsheet that emulates the
information in the Catalog and Schema. That’s because Microsoft Excel 7.0
does not support Catalogs and Schemas. 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 Excel spreadsheet tables you use with
AutoCAD.
Follow these steps to create a new table in Microsoft Excel
1. Create a new spreadsheet.
In this example, the spreadsheet is named
infsch.xls and placed in this directory.
C:\MSOFFICE\EXCEL
2. Create the first reference table by adding
this information in cells A1 through B2.
A
B
1
CATALOG_NAME
SCHEMA_NAME
2
NULL
<drive:\directory\spreadsheet>
For example, if the spreadsheet is named samples.xls
and is stored in
c:\msoffice\excel\examples
Cell B2 would contain
c:\msoffice\excel\examples\samples
Do not include the file extension (XLS) for
your spreadsheet. For this example, you would use sample not sample.xls.
3. Highlight cells A1 through B2 and name them
SCHEMATA.
Use the Name Box to the left of the formula
bar to do this.
4. Create the first reference table by adding
this information in cells A4 through D5.
A
B
C
D
1
CATALOG_NAME
SCHEMA_NAME
2
NULL
<drive:\directory\spreadsheet>
3
4
TABLE_CATALOG TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
5
NULL
<drive:\directory\spreadsheet> <name
of range of cells
Base Table
Cells B2 and B5 contain the same information;
namely the path and filename for the spreadsheet you wish to work with.
Cell C5 contains the name of the range of cells you supplied in your spreadsheet.
For example, sample.xls could contain a range
of cells named PARCELS; in this case, cell C5 will contain a named range
called PARCELS.
5. Select cells A4 through D5 and give them
the name TABLES.
6. Save the spreadsheet and exit Microsoft
Excel.
Installing and Configuring the Windows ODBC Driver
Manager
The installation process for the ODBC version 3.0
Driver Kit allows you to choose an ODBC driver for Microsoft Excel 7.0
from a list of commonly used databases.
To configure the Windows ODBC Driver Manager
1. From the Windows Start menu, choose Settings
> Control Panel.
2. From Control Panel, double-click the ODBC
icon.
3. In the ODBC Data Source Administration
dialog box, choose the User DSN (data source name) tab.
4. On the User DSN tab under User Data Sources,
select Excel Files. Then choose Configure.
5. In the ODBC SQL Server Setup dialog box,
enter the data source name and a description.
For example, enter ODBC_EXCEL. Then use the
same name when you specify the ODBC environment from the External Database
Configuration utility.
6. Under Database, choose Select Workbook
7. In the Select Workbook dialog box, select
the directory or files where the database files are located, then choose
OK.
In this example the location is
c:\msoffice\excel\infsch.xls
8. In the ODBC Microsoft Excel Setup dialog
box, choose OK.
9. In the ODBC Data Source Administrator dialog
box, choose OK
Setting Up the Database Link
To setup the database link, launch the External Database
Configuration Editor from the AutoCAD R14 or AutoCAD Map R2 program group.
The External Database Configuration dialog box is
displayed. Note that this configuration utility runs independently of AutoCAD.
Adding a New ODBC Environment
The Environments window on the Environments tab
of the External Database Configuration dialog box, displays a list of the
currently defined environments.
To add a new environment
1. On the Environments
tab of the External Database Configuration dialog box, choose Add.
2. In the Select DBMS
for new Environment dialog box, select ODBC from the DBMS List.
When you make this
selection, you are choosing to use the AutoCAD R14 ODBC database driver
for this environment.
3. In the Select DBMS
for new Environment dialog box, enter the Environment Name (the Environment
Description is optional). Then choose OK.
In this example, the
Environment Name is ODBC_EXCEL. This is the Data Source Name you specified
in the ODBC Microsoft Excel 7 Setup dialog box.
Information Schema
A Microsoft Excel connection to AutoCAD R14 or AutoCAD
Map R2 requires an information schema spreadsheet, which you have to create.
To specify the information schema in the External
Database Configuration Editor
1. On the General
tab of the ODBC ASI Environment dialog box, choose Browse.
Browse to the directory
that contains the information schema spreadsheet. In this example, it’s
the file infsch.xls. Note that the ODBC ASI Environment dialog box, is
shown in this document as the
Environment: ODBC_EXCEL(ODBCDRV)
dialog box.
2. Select the information schema spreadsheet (infsch.xls), then choose OK. The full path to the file is displayed in the Information Schema field. For example, c:\test\infsch.xls.
3. In the ODBC ASI Environment dialog box, choose Apply. This updates the Windows Registry.
Testing Your Environment
Once you have provided all the necessary information
in the ODBC ASI Environment dialog box, choose OK to save the environment
definition.
This returns you to the External Database Configuration
dialog box, where the environment definitions are listed. To test your
environment, select it (in this example ODBC_EXCEL), then choose Test.
You are 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 choose OK. A test is 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 are displayed.
The list details the functionality of your driver.
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 ODBC 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 in an ODBC environment
for Microsoft Excel, whereas they do not exist in other environments.
ODBC ASI Environment Dialog Box (General Tab)
There are several fields in the ODBC ASI Environment
dialog box. This section describes what the fields are used for, and the
values you can enter in them.
Set Schema
The are two possible values for Set Schema.
SUPPORTED
NOT SUPPORTED
You can select these values from the drop-down list
for the Set Schema field.
SUPPORTED means the target database has information
schema functionality. Microsoft Excel does not have this functionality.
That is why you must create an information schema manually in Microsoft
Excel.
You can either leave this field blank, or set the
value to NOT SUPPORTED.
Default Catalog
You cannot use the Default Catalog option for an
ODBC connection to Microsoft Excel. There is no equivalent to Catalog in
this type of environment. You should leave the Default Catalog field empty,
as anything entered will not be recognised.
Default Schema
If you want to make one of your defined schemas
a default schema, enter that schema name into this field. Schema, in the
case of an ODBC/Microsoft Excel environment, is the value from the TABLE_SCHEMA
cell in the SCHEMATA reference table, in the information schema spreadsheet.
This value appears in the table as a path to a Microsoft Excel file. To
specify a default Schema, enter the entire path and file name (without
the XLS extension), into this field. The value should be enclosed in quotation
marks. For example, if the value in your information schema table is test,
enter this value in the Default Schema field.
"c:\msoffice\excel\test"
When you set a default Schema and connect to this
environment.
• That schema is pre-selected
in the ASE Administration dialog box.
• A list of tables
associated with the Default Schema is displayed.
Autocommit
You can set Autocommit ON or OFF by selecting the
value from the drop-down list for the Autocommit field.
• Autocommit ON
Transactions are automatically
committed to the database after each SQL statement is executed.
• Autocommit OFF
You manually commit
SQL changes by choosing the Commit button in the ASE dialog box.
Timeout
The Timeout setting is in seconds. When you specify
a timeout value, the driver will wait that number of seconds for a database
lock to be released.
• Lock is released
before timeout period expires. You can access the database record.
• Lock is not released
before timeout period expires. You cannot access the database record.
Error: record is locked.
ODBC ASI Environment Dialog Box (Schemas and Tables
Tab)
If you define an ODBC environment that uses an information
schema, the values in the information schema override any values entered
on the Schemas and
Tables tab of the ODBC ASI Environment dialog box. It is acceptable to
leave all of the fields on this tab empty.
If you specify any settings on the Schemas and Tables
tab of the ODBC ASI Environment dialog box, you must choose Apply to update
the Windows Registry. You can then proceed in one
of two ways.
• Test your defined
environment, as described in the Testing Your Environment section.
• Close the configuration
editor by choosing OK. You can then launch AutoCAD and connect to your
Microsoft Excel data using the defined environment.
Accessing Your Table Data in AutoCAD
If you add an environment definition using the external
database configuration utility, you must reload the ASE portion of AutoCAD
before you see the environment. If you are running AutoCAD at the same
time as the configuration utility, you must either exit and re-launch AutoCAD,
or unload/reload the ASE kernel.
Note: The Administration
dialog box is displayed when you use the ASEADMIN command. After establishing
the connection in the Administration dialog box, there might be timeout
problems when selecting the Table. You can resolve this problem by
reselecting the Schema and the Table toggle box.
The ASE portion of AutoCAD is now an AutoCAD Runtime
Extension (ARX extension). ARX applications can be unloaded and reloaded
inside AutoCAD using this method.
1. Enter the ARX command
at the command line.
2. Enter UNLOAD at
the ARX sub-command prompt.
3. Enter ASE as the
name of the ARX application to be unloaded.
To reload the ase.arx kernel, enter any ASE command.
For example, ASEADMIN or ASEROWS.
Note: It is not possible
to unload the ASE ARX application in AutoCAD Map R2. To reinitialise the
ASE portion of AutoCAD Map with new entries made in the External Database
Configuration Editor, you must exit and re-start AutoCAD Map.