Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003: Running a delete query

    I have been using the same .mdb accounts database for the last 4 years, adding data to it on a monthly basis. Each month I need to add data in respect of an interim set of monthly draft accounts, then delete that months interim data and finally add a complete data set for the month. This process has worked successfully for four years. I am using an Access 2003 database, however we have just updated our office programs to Access 2007. I have 2 queries: 1. For the last 2 months the accounts database has refused to let me run the required delete query referred to above. I can run select queries and all the other required functions, but it will just not run delete queries. Has the upgrade to office 2007 changed a setting that I need to reset? 2. Is there an advantage in updating the database file to Access 2007 and if so can you please provide some tips on file types etc? FYI, I run 4 other databases using the same sort of principles and the other databases seem to still be running properly. Thanks

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    What exactly happens when you try to run the delete query?

    If your current databases are working the way you want it, I see no big advantage moving the files to the new Access file format.

  4. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nothing happens when I run the delete query. If I change delete to select then the correct data is selected. When I change the query back to delete the DB once again just does nothing.

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts
    It would be useful to post the actual SQL statement that you are using to try to do the Delete. Updating to 2007 should not have impacted the operation of the query in any way - and we continue to use the .mdb format in virtually everything we do that doesn't use a SQL Server backend. Is it possible that Warnings have been turned off, and the query is encountering an error that you can't see.
    Wendell

  6. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Can you delete a record manually to check there is no locking in place?

  7. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for all your suggestions.
    • The delete query is just "Delete * from Accts2012 where period = 9" As stated previously this has worked quite happily for the last 4 years
    • I will try manually deleting a line and let you know how I get on.
    • Can somebody please advise what will happen if I create a clean instance of access on the server and then insert the current tables and queries into this new instance. Will all the required attributes come accross with the tables?
    Thanks again


    Alex

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    If you import the tables and queries to the new database, every detail will be imported.

    About the delete query, what happens if you open it in design view and then just choose Datasheet view? Are any records displayed?

  9. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All the relevant records that I would like to delete appear to be displayed if I open the query and then choose data sheet view.

  10. #9
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    And if you execute it after displaying it in datasheet view?

    Are there tables that have relationships to this one?

  11. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have copied all the tables and queries into a new data base instance on the server and the same fault has appeared in the new instance (still 2003 version) of the database. I have tried executing the query after displaying the data sheet view and it will not execute. However I have now noticed that when I try to run the query the bottom left area of the access screen shows tha following message "The action or event has been blocked by Disabled mode". Can you please advise how to re-enable an action.

  12. #11
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    When you open the database, do you get a yellow bar with a security warning?

  13. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No yellow warning bar is shown on opening the DB. However I think that, following a bit fo ferkling in the options area, I may have fixed the problem.

    However, I am still a bit confused about why the problem arose on one DB but not on the others that I maintain.

    Thanks for all your help

  14. #13
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,308
    Thanks
    130
    Thanked 1,159 Times in 1,067 Posts
    Are the databases located in different folders, maybe? I no longer remember how this used to be handled in Access 2003. In posterior versions, you can add the locations of your database to a list of trusted folders and that solves the issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •