Autocad Map -Excel 19:27:12  09 NOV 98
Downloaded from Autocad at:
http://www.autodesk.com/search97cgi/vtopic?action=view&VdkVgwKey=%2E%2E%2F%2E%2E%2F%2E%2E%2F%2E%2E%2F%2E%2E%2Fw3%2FAmericas%2FWWW%2FWebdocs%2Fsupport%2F%2Ftechdocs%2Ftd20%2Ftd205334%2Ehtm&doctype=raw&Collection=techasst2&

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.