Delete records based on fields in another table

[Guest]

Please help: I have a table T1 with a number field adressing records in
another table T2.

Now I want to delete all records in T2 referenced in T1. I create a query
with the joining of the two tables - as described in help. But when running
the query I get the error (and the explanations dont really make any sense,
as I am creator, admin and only user):

Could not delete from specified tables. (Error 3086)
You tried to delete data from one or more tables, but the deletion could not
be completed.

Possible causes:

You do not have permission to modify the table. To change your permissions
assignments, see your system administrator or the table's creator.
The database was opened for read-only access. The database is read-only for
one of these reasons:
You used the OpenDatabase method and opened the database for read-only
access.
The database file is defined as read-only in the database server operating
system or by your network.
In a network environment, you do not have write privileges for the database
file.
In Microsoft® Visual Basic®, you used the Data control and set the ReadOnly
property to True.
To delete the data, close the database, resolve the read-only condition, and
then reopen the file for read/write access.

 

[Gary Walter]

Hi R,

Hard to say for sure w/o looking at your query,
but common reason for this when delete query
references more than one table is that you need
to add "DISTINCTROW"

http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

***quote***
Delete Queries
**********************************************************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft
Access.
****************************************************************************************
However, because the default value for UniqueRecords is No in Access 2000,
you must set the value of this property manually when you create a new
delete query in Access 2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click
Properties.
Click an empty area in the upper half of the query window so that the
property sheet displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
****end quote****

good luck,

gary

 

[Guest]

RSunday,

In the design view of the query chose Query Properties. Change the "Unique
Records" selection to Yes. Close the dialog box and run your query.

Tim

 

[Guest]

That does the trick! Thanks

RSunday,

In the design view of the query chose Query Properties. Change the "Unique
Records" selection to Yes. Close the dialog box and run your query.

Tim