ACC1x: Cannot Edit Data Using Query with Attached dBASE Table

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

You are unable to edit fields in a Microsoft Access table through a query that joins an attached dBASE table to the Microsoft Access table. The query joins the tables on a key field in the Microsoft Access table.

CAUSE

Since relationships cannot be created with attached tables using the Edit Relationship dialog box, joins must be performed manually in the QBE grid. When a join is created between an attached dBASE table and a Microsoft Access table on the primary key field, Microsoft Access assumes the type of relationship is one-to-many with the Microsoft Access table representing the "one" side. You cannot edit fields that come from the "one" side of a one-to-many relationship.

RESOLUTION

Establish a main/subform setup with the subform based on the attached dBASE table and the main form based on the Microsoft Access table. You will be able to edit data from both tables through their respective forms.

STATUS

This behavior no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Even if the data in the dBASE table actually has a one-to-one relationship with the Microsoft Access table, you will not be able to edit fields from the Microsoft Access table using a query joining these tables. If you create a single form based on this query, and set the form's AllowUpdating property to Any Tables, you will not be able to edit data in the Microsoft Access table.