Re: Creating a Delete Query from an Unmatched Query
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Mon, 15 May 2006 15:57:44 -0400
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));
"hgbwhite" <hgbwhite@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D85FB15-8E72-4A4B-B9D6-00649D2CD256@xxxxxxxxxxxxxxxx
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?
"KARL DEWEY" wrote:
Try not joining the query but use the query field as criteria.
"hgbwhite" wrote:
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?
.
- References:
- RE: Creating a Delete Query from an Unmatched Query
- From: hgbwhite
- RE: Creating a Delete Query from an Unmatched Query
- Prev by Date: Re: filter by query
- Next by Date: Re: finds gaps in sequential numbers?
- Previous by thread: RE: Creating a Delete Query from an Unmatched Query
- Next by thread: Re: Query with multiple fields
- Index(es):
Relevant Pages
|
