ACC: Append Query Causes Divide Error

• Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run an append query, you may receive one of the following error messages:

Microsoft Windows 95

   This program has performed an illegal operation and will be shut down.
   If the problem persists, contact the program vendor.

   MSACCESS caused a divide error in module MSJT3032.DLL

   MSACCESS caused a divide error in module MSJET35.DLL

Microsoft Windows NT

   An application error has occurred
   and an application error log is being generated.

   MSACCESS.exe
   Exception: divide by zero

CAUSE

The append query contains a join to a field in another query whose UniqueValues property is set to Yes. For example, the append query may be based on a table that is joined to a select query whose UniqueValues property is set to Yes in order to limit the data that is appended.

WORKAROUND

When creating an append query, do not use a join to a query whose UniqueValues property is set to Yes. Set the UniqueValues property of the select query to No, and then set the UniqueValues property of the append query to Yes. Instead of using a select query, you may also want to consider using the source table of the select query, and then setting the UniqueValues property of the append query to Yes.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft Access versions 7.0 and 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

Create the Tables

1. Create a new database in Microsoft Access.

2. Create the following table and save it as tblDivideError1:

         Table: tblDivideError1
         ---------------------------
         Field Name: ID
            Data Type: Number
            Field Size: Long Integer
         Field Name: TextName
            Data Type: Text
            Field Size: 50
   
         Table Properties: tblDivideError1
         ---------------------------------
         PrimaryKey: ID
   
      Close and save the table.
   
   

3. Create a copy of the tblDivideError1 table. To create a copy of the tblDivideError1 table, select it in the Database window; press CTRL+C, and then press CTRL+V. In the Paste Table As dialog box, type tblDivideError2 in the Table Name box, and then click OK.

4. Open the tblDivideError1 table and add the following data:

         ID     TextName
         --     --------
         1      Test
         2      Test
         3      Test
         4      Test
         5      Test
   
   

5. Create the following table and save it as tblDivideError3:

         Table: tblDivideError3
         ---------------------------
         Field Name: ID
            Data Type: Number
            Field Size: Long Integer
         Field Name: AltID
            Data Type: Number
            Field Size: Long Integer
   
         Table Properties: tblDivideError3
         ---------------------------------
         PrimaryKey: ID
   
      Close and save the table.
   
   

6. Open the tblDivideError3 table and add the following data:

         ID     AltID
         --     -----
         1      2
         2      3
         3      5
         4      5
   
   

Create the Queries

1. Create a new query based on the tblDivideError3 table and save it as qryDivide1:

         Query: qryDivide1
         -------------------------
         Type: Select Query
   

         Field: AltID
            Table: tblDivideError3
   
         Query Properties
         ------------------
         Unique Values: Yes
   
   

2. Create a new query and add both the tblDivideError1 table and qryDivide1 query. Save it as qryDivideAppend:

         Query: qryDivideAppend
         ---------------------------------------------
         Type: Append Query
         Join: tblDivideError1.ID <-> qryDivide1.AltID
   

         Field: ID
            Table: tblDivideError1
         Field: Name
            Table: qryDivide1
   
         Query Properties
         ----------------------------------
         Destination Table: tblDivideError2
   
   

3. Run qryDivideAppend. Note that you receive the error described in the "Symptoms" section.

REFERENCES

For more information about the UniqueValues property, search the Help Index for "UniqueValues property."