Solved

My delete queries do not work in Access 2002

Posted on 2003-12-02
31
1,427 Views
Last Modified: 2007-12-19
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!
0
Comment
Question by:epaynedsm
  • 14
  • 9
  • 4
  • +2
31 Comments
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
Hey epaynedsm!

  Post one of the non-working queries.

regards
Jack
0
 

Author Comment

by:epaynedsm
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
Tried this, and same result.
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
I am the creator of the table, just today, and the only user. No form is open.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility

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
 

Author Comment

by:epaynedsm
Comment Utility
Same thing on both of these. It will bring up the information in datasheet view, but will not delete the records.
0
 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
Andrew:
Yes, I can delete from the table manually.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Try and import the Excel Spreadsheet and do this in access tables this could be an issue with the Excel attachments. Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Expert Comment

by:morpheus30
Comment Utility
Try this...

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

Expert Comment

by:TextReport
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Create a new database and import the tables into the new MDB file and try the delete query from there. Cheers, Andrew
0
 

Author Comment

by:epaynedsm
Comment Utility
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
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Have you tried the subquery? This is the only weay I can get the delete to work in Access 2002. Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 23

Expert Comment

by:heer2351
Comment Utility
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
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
Comment Utility
SOLUTION TIME: Set the Unique Records Property to Yes in your DELETE Query and my DELETE query worked !!!!!!

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
 

Author Comment

by:epaynedsm
Comment Utility
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
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now