Template Wizard Writes a Record to All Tables in Database (175702)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
This article was previously published under Q175702

SYMPTOMS

When you save a workbook that was created by using the Microsoft Excel Template Wizard, Microsoft 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, Microsoft 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 did not link worksheet cells to fields in all tables in the database.

This behavior is by design of Microsoft Excel.

MORE INFORMATION

To see an example of the behavior, follow these steps.

Creating the Database

  1. Create a new Microsoft Access database called Test.mdb with 2 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 enter 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, leave the default values and 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 previous section). Click Open, and then click Next.
  7. Click cell B1 on Sheet1 to link that cell to the Name field in Table1.

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

Creating the Workbook That Is Based on the Template

  1. Click Finish. On the File menu, click New. Double-click Test.xlt.

    A copy of the template is opened.
  2. If a macro warning dialog box appears, click Enable Macros.
  3. Enter 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 you entered 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 the Index tab in Microsoft Excel Help, type the following text

template

and then double-click the selected text to go to the "What is the Template Wizard with Data Tracking?" topic.
Modification Type:MinorLast Reviewed:8/17/2005
Keywords:kbprb KB175702
©2004 Microsoft Corporation. All rights reserved.