can delete records in ACCESS

Question

Post reply

can delete records in ACCESS

Alex Cooper

SSC-Addicted

Points: 499
More actions
August 31, 2007 at 11:38 am

#178254

I can not delete records in ACCESS,
I open the "table view", delete the record. But when I view in "reports" the record is constantly appears.
what am I doing wrong or not doing ?
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Alex Cooper SSC-Addicted Points: 499 More actions · Answer 1

Sorry - But when I run a query that record still appears...

any suggestions?

Andrew Collett SSC Enthusiast Points: 129 More actions · Answer 2

I would have said that you were using a delete query that's deleting from a set of linked tables, with the criteria on one table and the field you want deleted on the other, but that's another problem (solved using an update then a delete in a process etc).

Anyway, to resolve, or at least diagnose, I'd open the repot in design mode, get the report properties, IIRC it's a top left right mouse click - you want to get the properties for the whole report not just a specific field.

Then, have a look at source data, if you're basing the report on a table then it'll list that, but there's also the option to edit that 'selection' and convert it into a query - this is a reasonably good idea as it'll mean you can transform/manipulate data between table and report - so logic isn't tied up in the report.

So, edit, get a query and then you may be able to see where the issue lies, i.e. being sourced from somewhere else, or some condition on deletion not being met.

Apologies for not having a solution there's not really much to be going on...

Cheers,

AndyC

Andrew Collett SSC Enthusiast Points: 129 More actions · Answer 3

Actually in light of your second post - write a query that selects the record you wish to delete (using some criteria or other) then change it ot a delete query. Much better control.

And if you want to make it a generic query, you might want to start with some basic condition boxes, so in the condition box put something like:

[Enter Criteria]

: Just like that, it'll then prompt you to enter criteria.

Of course, test as a select to get the right data then change to delete in the query type.

Should be losing data all over the place soon enough!

One thing you might want to also consider is adding a flag "Deleted" to your table design, set it to false then if you wish to delete flag as true. Then you report query/criteria just has to rely on that flag - as would every other query you have of course. Benefit of this is that you can undelete easily...

abhojwani Right there with Babe Points: 764 More actions · Answer 4

Does that table have a Primary Key? If it doesn't try adding a Primary Key to the table and then try deleting the row.

Alex Cooper SSC-Addicted Points: 499 More actions · Answer 5

This is my Query:

SELECT ShipmentNumber, ShipmentDescription, Status, ShipmentNotes, ReceiptDate, ShippedDate, FinalizedBy, FinalizeDate, ScheduledDate, CreatedBy,

CreateDate, RevisionNumber, RevisionDate, RevisedBy

FROM dbo.Shipments

WHERE (Status = N'Ready-to-Ship') OR

(Status = N'Ready-to-Stage')

My DELETE CLAUSE:

DELETE FROM dbo.Shipments

WHERE ShipmentNumber = '7/24BLUE'

and ShipmentNumber = 'NBOTTO7/26'

and ShipmentNumber = 'NB11/13'

and ShipmentNumber = 'NBVINYL2/8/07-2'

and ShipmentNumber = 'NB1/24'

and ShipmentNumber = 'NB6/27'

and ShipmentNumber = 'NBMERLIN6/7'

When I add a delete clause to my query analyzer the records get deleted.

when I go to updated the query in access I get this pop-up warning:

UNABLE TO PARSE QUERY TEXT: The SQL pane is out of sync with the other panes. The query cannot be presented graphically in the Diagram pane and Grid pane. Do you want to continue.

Choose YES to preserve your recent changes to the SQL pane

Choose NO to undo your typing and return to the SQL pane.

Because I can not execute the query without saving it, my question is:

in executing this YES will I destroy my current query.

And is there a specific format I should be writing this in.

Neb12pgmr SSC Rookie Points: 36 More actions · Answer 6

Alex,

Your problem is with your delete SQL. You are most likely not deleting any rows because shipmentNumber can only be one value at a time, try using OR's instead.

DELETE FROM dbo.Shipments

WHERE ShipmentNumber = '7/24BLUE'

OR ShipmentNumber = 'NBOTTO7/26'

OR ShipmentNumber = 'NB11/13'

OR ShipmentNumber = 'NBVINYL2/8/07-2'

OR ShipmentNumber = 'NB1/24'

OR ShipmentNumber = 'NB6/27'

OR ShipmentNumber = 'NBMERLIN6/7'

I'm a little new at this myself, so this is my best guess at why rows are not being deleted.

Good Luck!

Mariki Jovner-449371 Newbie Points: 7 More actions · Answer 7

Looking at your problem when you do delete the records it works but in the reports it show. Try to run a compact and repair on the database if this is still the problem.

Alex Cooper SSC-Addicted Points: 499 More actions · Answer 8

Right now there are people working in the database mentioned. I will try later tonight and let you know if it works. Thanks...

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 9

If a record is deleted, then it is deleted. A compact and repair will not remove the record. It will remove the space the deleted record had used as a record, but when it is deleted, the record itself is GONE!!!

The poster that said you need to use ORs in your delete clause was correct. You have ANDs there and that statement will NEVER delete anything.

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com