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.