My delete queries do not work in Access 2002

How do you do this delete?  You can link the Excel file using the Linked Table Manager and then create a query like this:

DELETE
FROM AccessTable
INNER JOIN ExcelFile
ON AccessTable.PrimaryKey = ExcelFile.FieldWithKeyValue

Hey epaynedsm!

  Post one of the non-working queries.

regards
Jack

I use Access 2002's "Find unmatched query wizard", which joins the tables, change the offending table's acctnr field to "is not null" and run the query, which in my previous configuration has always brought up the offending records, then delete them manually. When I attempt to do this as a select query, it brings up the records, but when I attempt the delete, I get "Records not deleted. Data is read-only." When I try by changing it to a delete query, I get "Could not delete from specified tables."
Here is the SQL from the query:
DELETE [60-60].*, Donor_list_for_2003_Angel.acctnr
FROM [60-60] LEFT JOIN Donor_list_for_2003_Angel ON [60-60].acctnr = Donor_list_for_2003_Angel.acctnr
WHERE (((Donor_list_for_2003_Angel.acctnr) Is Not Null));

if Donor_list_for_2003_Angel is the excel spreadsheet...

Try:

  WHERE (len(Donor_list_for_2003_Angel.acctnr) =0);

Excel sometimes has a hard time coming across with true null values.

You are asking for trouble have a table named 60-60.

Tried this, and same result.

Records not deleted. Data is read-only."

Is this file password protected?  Also, are you sure no other users have the same table open or a form based on this table is open?  Seems to me that the problem is not the query but the table itself.  Something else is locking it...

I am the creator of the table, just today, and the only user. No form is open.

In the delete try removing the field from the excel spreadsheet as you are only allowed to delete from the one table

DELETE [60-60].*
FROM [60-60] LEFT JOIN Donor_list_for_2003_Angel ON [60-60].acctnr = Donor_list_for_2003_Angel.acctnr
WHERE (((Donor_list_for_2003_Angel.acctnr) Is Not Null));

If that doesn't work then

DELETE [60-60].*
FROM [60-60]
WHERE acctnr IN (SELECT acctnr FROM Donor_list_for_2003_Angel);

Cheers, Andrew

Same thing on both of these. It will bring up the information in datasheet view, but will not delete the records.

Your SQL Statment should be modified like this:

DELETE
FROM [60-60] LEFT JOIN Donor_list_for_2003_Angel ON [60-60].acctnr = Donor_list_for_2003_Angel.acctnr
WHERE (((Donor_list_for_2003_Angel.acctnr) Is Not Null));

Can you open the table and delete them manually?

If my second example doesn't work then the issue it not with the query but the table / database.

Cheers, Andrew

morpheus30:
I am not deleting from the "donor_list.." Excel file, but from the "60-60" table. When I alter as you suggest, the query is not specifying what records to delete.

Andrew:
Yes, I can delete from the table manually.

Try and import the Excel Spreadsheet and do this in access tables this could be an issue with the Excel attachments. Cheers, Andrew

Just exported my test Table to Excel and attached it and the delete query still runs OK with the list of deletiong in the attached spreadsheet.

Have you tried reconnecting the attached spreadsheet?

Cheers, Andrew

Try this...

DELETE A.*
FROM [60-60] A
INNER JOIN [Donor_list_for_2003_Angel] B ON A.AcctNr =  B.AcctNr

Here is my working example

tblMain (Access 2002 Table)
ID      Description
1      a
2      b
3      c
4      d
5      E
6      f
7      G
8      h
9      I
10      j
11      k
12      l
13      m
14      n
15      O
16      p
17      q
18      r
19      s
20      t
21      u
22      v
23      w
24      x
25      y
26      z

tblDelete (Attached Excel Sheet)
ID
5
7
9
15

qryDelete_Join (This Fails)
DELETE tblMain.*, tblDelete.ID
FROM tblMain LEFT JOIN tblDelete ON tblMain.ID = tblDelete.ID
WHERE (((tblDelete.ID) Is Null));

qryDelete_SubQuery (This Works)
DELETE tblMain.ID
FROM tblMain
WHERE (((tblMain.ID) In (SELECT id FROM tblDelete)));

Cheers, Andrew

It still does not work. I tried your last solution, and tried even doing it from an Access table rather than the Excel spreadsheet. Still "Could not delete from specified tables."

Do you get the same error? What is the SQL you are using? Are you using access as the front end or are you doing this from another langueage VB, VBScript etc?

Cheers, Andrew

Create a new database and import the tables into the new MDB file and try the delete query from there. Cheers, Andrew

I use the Design view within Access 2002... have been doing this in Access 97 for years. The SQL view says: DELETE [60-60].*, [Donor list for 2003 Angel2].acctnr
FROM [60-60] LEFT JOIN [Donor list for 2003 Angel2] ON [60-60].acctnr = [Donor list for 2003 Angel2].acctnr
WHERE ((([Donor list for 2003 Angel2].acctnr) Is Not Null));

Even creating a new blank database, pulling in these two Access tables, the query receives the same error.

So this should be the right question :)

This should work:

delete *
from
  [60-60] as A
where
  not exists (select null from Donor_list_for_2003_Angel where acctnr=A.acctnr)

Have you tried the subquery? This is the only weay I can get the delete to work in Access 2002. Cheers, Andrew

I can send you the example that works if you want to see it, also do you want to send me the offending database, my email address is in my profile. Cheers, Andrew

Oops misread the question you want to delete the records that are duplicate, small change to the query:

delete *
from
  [60-60] as A
where
  exists (select null from Donor_list_for_2003_Angel where acctnr=A.acctnr)

The Unique Records Propert puts in the DISTINCTROW option in the SELECT so

DELETE DISTINCTROW [60-60].*
FROM [60-60] LEFT JOIN Donor_list_for_2003_Angel ON [60-60].acctnr = Donor_list_for_2003_Angel.acctnr
WHERE (((Donor_list_for_2003_Angel.acctnr) Is Not Null));

Cheers, Andrew

Just to support what I posted, this is taken from the Microsoft Access 2002 help file

"When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the query’s UniqueRecords property must be set to Yes. "

Cheers, Andrew

TO change all your delete queries you can use DAO

dim db as database
dim qd as querydef

    Set db = CurrentDB()
    For Each qd In db.QueryDefs
        If left(qd.Name, 1) <> "~" Then ' Ignore temp queryies
           If left(qd.sql, 7) = "DELETE " Then
              If mid(qd.sql,8,11) <> "DISTINCTROW" Then
                 qd.sql = "DELETE DISTINCTROW " & mid(qd.sql,8)
              End If
           End If
        End If
    Next qd

Cheers, Andrew

Bravo, Andrew! The stupid part about this is that I read something about the Unique Records property and have used that in the past. But it is hard to remember when you have to use it. Thanks so much!

It was bugging me to say the least. In previous version DISTINCTROW was just a waste of space as SELECT ... was the same as SELECT DISTINCTROW ...

One thing it showed to me is the old RTFM, well RTFH in this case as it is in there in you work at it.

No one can know everything, the trick is to know where to find it.

Cheers and best wishes,  Andrew