Solved

My delete queries do not work in Access 2002

Posted on 2003-12-02
31
1,432 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 9
  • 4
  • +2
31 Comments
 
LVL 5

Expert Comment

by:morpheus30
ID: 9861033
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
ID: 9861056
Hey epaynedsm!

  Post one of the non-working queries.

regards
Jack
0
 

Author Comment

by:epaynedsm
ID: 9861112
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 32

Expert Comment

by:jadedata
ID: 9861149
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
ID: 9861210
Tried this, and same result.
0
 
LVL 5

Expert Comment

by:morpheus30
ID: 9861271
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
ID: 9861357
I am the creator of the table, just today, and the only user. No form is open.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9861457

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
ID: 9861556
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
ID: 9861618
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
ID: 9862031
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
ID: 9862078
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
ID: 9862089
Andrew:
Yes, I can delete from the table manually.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9862121
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
ID: 9862157
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
 
LVL 5

Expert Comment

by:morpheus30
ID: 9862246
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
ID: 9862460
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
ID: 9877820
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
ID: 9877916
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
ID: 9877954
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
ID: 9878004
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
ID: 9878054
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
ID: 9878056
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
ID: 9878063
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
ID: 9878094
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
ID: 9878153
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
ID: 9878165
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
ID: 9878334
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
ID: 9878372
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
ID: 9878382
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
ID: 9878411
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

726 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