Correlated subquery in DELETE stmt not work in Access '97 or 2000
Hi,
I cross-posted a version of this to the comp.databases.ms-access ng, but this
post is more specific to VB which is what I am using, along with Access.
I have the following query that I think should work in Access:
DELETE * FROM ABAMaster AS A WHERE NOT EXISTS (SELECT * FROM ACCOUNTHEADER
WHERE ABANUM = A.ABANUM);
*** I have tried many variations of the aobve SQL, but to no avail.
I get the following error when I run it in both Access '97 and Access 2000:
Invalid argument. (Error 3001)
The text of the error message follows:
Quote:
>You tried to perform an operation that involves a routine in a DLL, and one of
the
>arguments to the routine is invalid. Check your entry to make sure you have
>specified the correct arguments, and then try the operation again.
>This error also occurs when you attempt to use mutually exclusive constants in
>an argument to a method, such as specifying both dbConsistent and
>dbInconsistent in the options argument to the OpenRecordset method.
I get the same error if use "NOT IN" in place of "NOT EXISTS". This should be a
simple operation: delete all rows from TableA which do not have a related row
in TableB, but Access is giving me a fit.
I was exec'ing this via an ADODB call in VB 6.0. I switched over the Access to
try the query there. In both places, I get the same error.
Do I need to update some data access component of NT or '98 that Access uses?
Anybody else out there have a similar problem. I search MS' knowledge base to
no avail. I used www.deja.com to no avail as well.
Thanks,
Greg
