XL2000: Template Wizard Writes a Record to All Tables in the Database (214367)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214367

SYMPTOMS

When you save a workbook that was created by using the Microsoft Excel Template Wizard, Excel writes records to tables in the database that is linked to the workbook.

CAUSE

When you use the Template Wizard to create a template that is linked to a database, Excel saves a record to every table in the database. Therefore, when you save a workbook that is linked to a database, a new record is written to all tables in the database. This behavior occurs even if you do not link worksheet cells to fields in all tables in the database.

MORE INFORMATION

Creating the Database

  1. Create a new Microsoft Access database called Test.mdb with two tables called Table1 and Table2.
  2. Add one Name field to Table1 and one Amount field to Table2.
  3. Do not create a primary key for either table. Close the database.

Creating the Template

  1. Create a new workbook in Microsoft Excel, and then type Name in cell A1 of Sheet1.
  2. Save the workbook as Test.xls.
  3. On the Data menu, click Template Wizard.
  4. In the Template Wizard - Step 1 of 5 dialog box, don't change any of the default values; click Next.
  5. In the Template Wizard - Step 2 of 5 dialog box, click Access Database as the database type.
  6. Click Browse, locate and select the Access database from step 1 (in the "Creating the Database" section). Click Open, and then click Next.
  7. Click cell B1 on Sheet1 to link that cell to the Name field in Table1, and then click Finish.

    NOTE: Do not link any cell to the Amount field in Table2.

Creating the Workbook Based on the Template

  1. On the File menu, click New, and then double-click Test.xlt.

    A copy of the template is opened.
  2. If a macro warning dialog box appears, click Enable Macros.
  3. Type any text into cell B1 of Sheet1, and then click Save on the File menu.
  4. Click Create a new record, and then click OK.

    This step writes a record to both Table1 and Table2 in the database.
  5. In the Save As dialog box, specify a location for Test1.xls, and then click Save.

    This step saves a copy of the Excel template.
  6. Open the Microsoft Access database. The text that you typed in cell B1 of Sheet1 is displayed in the Name field of Table1. Table2 also contains a record, but the Amount field is empty.

REFERENCES

For more information about the Template Wizard, click Microsoft Excel Help on the Help menu, type what is the template wizard with data tracking? in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbprb KB214367
©2003 Microsoft Corporation. All rights reserved.