My delete queries do not work in Access 2002

I have often used delete queries in Access 97, comparing two tables and then deleting the selected records. We have recently moved to Access 2002, and I have not been able to get a delete query to work since. I continually get the error, "Could not delete from specified tables." The records I want to delete are in an Access table I created, and my comparing table is an Excel file. Help please!
epaynedsmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

morpheus30Commented:
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
0
jadedataMS Access Systems CreatorCommented:
Hey epaynedsm!

  Post one of the non-working queries.

regards
Jack
0
epaynedsmAuthor Commented:
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));
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

jadedataMS Access Systems CreatorCommented:
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.
0
epaynedsmAuthor Commented:
Tried this, and same result.
0
morpheus30Commented:
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...
0
epaynedsmAuthor Commented:
I am the creator of the table, just today, and the only user. No form is open.
0
TextReportCommented:

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
0
epaynedsmAuthor Commented:
Same thing on both of these. It will bring up the information in datasheet view, but will not delete the records.
0
morpheus30Commented:
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));
0
TextReportCommented:
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
0
epaynedsmAuthor Commented:
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.
0
epaynedsmAuthor Commented:
Andrew:
Yes, I can delete from the table manually.
0
TextReportCommented:
Try and import the Excel Spreadsheet and do this in access tables this could be an issue with the Excel attachments. Cheers, Andrew
0
TextReportCommented:
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
0
morpheus30Commented:
Try this...

DELETE A.*
FROM [60-60] A
INNER JOIN [Donor_list_for_2003_Angel] B ON A.AcctNr =  B.AcctNr
0
TextReportCommented:
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
0
epaynedsmAuthor Commented:
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."
0
TextReportCommented:
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
0
TextReportCommented:
Create a new database and import the tables into the new MDB file and try the delete query from there. Cheers, Andrew
0
epaynedsmAuthor Commented:
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.
0
heer2351Commented:
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)
0
TextReportCommented:
Have you tried the subquery? This is the only weay I can get the delete to work in Access 2002. Cheers, Andrew
0
TextReportCommented:
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
0
heer2351Commented:
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)
0
TextReportCommented:
SOLUTION TIME: Set the Unique Records Property to Yes in your DELETE Query and my DELETE query worked !!!!!!

Cheers, Andrew
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TextReportCommented:
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
0
TextReportCommented:
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
0
TextReportCommented:
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
0
epaynedsmAuthor Commented:
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!
0
TextReportCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.