Post reply

can delete records in ACCESS

  • 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.

  • August 31, 2007 at 11:40 am

    #730849

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

    any suggestions?

  • September 2, 2007 at 9:13 pm

    #730956

    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

  • September 2, 2007 at 9:17 pm

    #730957

    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...

     

  • September 4, 2007 at 11:11 am

    #731282

    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.

  • September 4, 2007 at 11:34 am

    #731287

    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.

  • September 4, 2007 at 1:12 pm

    #731306

    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!

  • September 4, 2007 at 3:11 pm

    #731333

    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.

  • September 4, 2007 at 3:29 pm

    #731337

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

  • September 5, 2007 at 7:30 pm

    #731673

    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

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

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