XL: Template Wizard Writes a Record to All Tables in Database

ID: Q175702

The information in this article applies to:

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" (without 

   the quotation marks) 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.

Additional query words: XL97 

Keywords          : kbtool xltemp xlwiz 
Version           : WINDOWS:7.0,7.0a,97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999