Results 1 to 6 of 6
  1. #1
    Pluto is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Location
    Memphis, TN
    Posts
    7

    Existing Access Database - Delete Old Data

    Is it possible to take an existing Access Database and delete previous data entered to coincide with the start of the new fiscal year without having to rebuild from scratch?



    That is to say, there are some new fields that need to be added, but the powers that be don't want to have to take the time to build it from the bottom up again, but also don't want the old data in it so that the first entry will start with ID 1 instead of ID 2501.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How important is the data to your business? Required for auditors?

    You could just copy the structure to a new table. And /or you could rename the current table to "archived..."
    If there is no data in your new table, you could do a compact and repair, and autonumber field should start fresh.

    Most orgs don't delete fiscal year data.

    You could redesign the table to include a fiscalYear field that would work for multi fiscal years.
    A lot depends on your business and the approach to data management and business discipline.

    How long do you/the powers think it would take to redesign the table, and associated processes?

  3. #3
    Pluto is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Location
    Memphis, TN
    Posts
    7
    There's a front end and back end to this database.

    They don't want to delete the data, but they don't want it accessible in the database to all of the front end users, even though they should only be using the front end and not the back end. But more to the point, they want a few new fields added to it and only want to report moving forward. They just think that it would take more hours to build it and the cost of building it be out of their budget range as opposed to the possibility of being able to start fresh.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I agree with Orange, rename it to Archived. Put it into the backend and remove the link, that way front-end users will not be able to see it. Should they decide they want to run reports off it you can write code to link to it, run the report, then delete the link. Deleting data is a bad idea. You could add "USys" in front of the name and it will become a hidden table.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    but the powers that be don't want to have to take the time to build it from the bottom up again, but also don't want the old data in it so that the first entry will start with ID 1 instead of ID 2501.
    I really hope you are not displaying ID if it is an autonumber type field.


    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers

  6. #6
    Pluto is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Location
    Memphis, TN
    Posts
    7
    Thanks, guys. I appreciate the advice!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-07-2018, 10:44 AM
  2. Does INTO statement delete existing table?
    By vetabz in forum Queries
    Replies: 6
    Last Post: 01-25-2017, 05:46 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Creating database from existing data
    By arturju in forum Database Design
    Replies: 4
    Last Post: 05-15-2014, 01:29 PM
  5. Replies: 6
    Last Post: 08-18-2013, 01:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums