Creating a Delete Query from an Unmatched Query

[Guest]

I am trying to delete records from table1 that are not in table2.

What I did......

I created an UNMATCHED query from table1 and table2. I then created a
DELETE Query using table1 and the UNMATCHED query using an inner join.

I keep getting the error:

"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."

I have confirmed all of the possible problems from above.

Does anyone know why this is not working or a better way to accomplish the
same thing?

 

[Guest]

Try not joining the query but use the query field as criteria.

 

[Guest]

I tried this and it did not work. Here is the actual code from the unmatched
query and the delete query that uses the unmatched query. The unmatched
query's name is "Table2 Without Matching Table1".

UNMATCHED QUERY: SELECT Table2.test
FROM Table2 LEFT JOIN Table1 ON Table2.test=Table1.test1
WHERE (((Table1.test1) Is Null));

DELET QUERY: DELETE Table2.*, [Table2 Without Matching Table1].test
FROM Table2, [Table2 Without Matching Table1]
WHERE ((([Table2 Without Matching Table1].test)=[table2].[test]));

Any other Ideas?

 

[John Spencer]

I would try the following:

DELETE DistinctRow Table2.*
FROM Table2 INNER JOIN [Table2 Without Matching Table1]
ON [Table2 Without Matching Table1].test=[table2].[test]

You can only reference ONE table in the DELETE clause. You were referencing
two

Another way of writing this
DELETE DistinctRow Table2.test
FROM Table2 LEFT JOIN Table1 ON Table2.test=Table1.test1
WHERE (((Table1.test1) Is Null));

I tried this and it did not work. Here is the actual code from the
unmatched
query and the delete query that uses the unmatched query. The unmatched
query's name is "Table2 Without Matching Table1".

UNMATCHED QUERY: SELECT Table2.test
FROM Table2 LEFT JOIN Table1 ON Table2.test=Table1.test1
WHERE (((Table1.test1) Is Null));

DELET QUERY: DELETE Table2.*, [Table2 Without Matching Table1].test
FROM Table2, [Table2 Without Matching Table1]
WHERE ((([Table2 Without Matching Table1].test)=[table2].[test]));

Any other Ideas?

Try not joining the query but use the query field as criteria.