By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Where issue with delete query

P: n/a
AP
I have a single table delete query in ACC2003 that will only delete if
I drop in a specific field and put the criteria 'is not null' I am
just simply trying to delete all records from the table. If I change
it to delete from *, it will return '0 records will be deleted, if I
return to to a select query, there are definitely records back there?
Any suggestions?
Thanks
Dec 19 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Wed, 19 Dec 2007 09:50:59 -0800 (PST), AP wrote:
I have a single table delete query in ACC2003 that will only delete if
I drop in a specific field and put the criteria 'is not null' I am
just simply trying to delete all records from the table. If I change
it to delete from *, it will return '0 records will be deleted, if I
return to to a select query, there are definitely records back there?
Any suggestions?
Thanks
Delete TableName.* from TableName;

If you wish more specific help, you'll need to post the exact query
SQL that works, as well as the exact SQL of the query that doesn't
work? You can see it, we can't.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Dec 19 '07 #2

P: n/a
AP
Ok, here is the deal, I am trying to speed up a few queries because
another user who was trying to delete ALL records from a table used
the following method:

DELETE [tbl_Daily Inserted].Inserted
FROM [tbl_Daily Inserted]
WHERE ((([tbl_Daily Inserted].Inserted) Is Not Null));

The is not null, causes slower performance so I changed it to this
DELETE [tbl_Daily Inserted].*
FROM [tbl_Daily Inserted];

And it says I will delete 0 records, if I change it back, I get the
message that i will delete all 38K records. There are no null fields.
Never had this probelm>??????
Dec 19 '07 #3

P: n/a
On Dec 19, 3:04 pm, AP <megacross...@greenixsolutions.comwrote:
Ok, here is the deal, I am trying to speed up a few queries because
another user who was trying to delete ALL records from a table used
the following method:

DELETE [tbl_Daily Inserted].Inserted
FROM [tbl_Daily Inserted]
WHERE ((([tbl_Daily Inserted].Inserted) Is Not Null));

The is not null, causes slower performance so I changed it to this
DELETE [tbl_Daily Inserted].*
FROM [tbl_Daily Inserted];

And it says I will delete 0 records, if I change it back, I get the
message that i will delete all 38K records. There are no null fields.
Never had this probelm>??????
It's difficult to analyse the inexplicable with all the clues.
It's a lot more difficult without all the clues:

Jet?

Split Backend?

Within a Transaction?

What does
Debug.Print DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) FROM
[tbl_Daily Inserted]")(0)
show?

Have you tried:
DELETE * FROM [tbl_Daily Inserted]
?

The other user? ... opens the db ... how? He/she has his/her own copy
of the frontend? He/she opens the same file (frontend) the you open?
What?

If you're deleting a million-zillion records it may be faster to drop
and rebuild the table.

Is the query a saved query? How do you run it?
Dec 19 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.