Results 1 to 5 of 5

Thread: [RESOLVED] Vanished Data

  1. #1

    Thread Starter
    Hyperactive Member Quasar6's Avatar
    Join Date
    Mar 2008
    Location
    Sol 3
    Posts
    325

    Resolved [RESOLVED] Vanished Data

    I have created an app which populates a standard, 2003 Microsoft Access *.mdb database. A seperate database is created for each project my employer starts, and they can get quite large with tens of thousands of records. An average file size is about 6MB.

    On one of our projects, all the data from an entire table has recently vanished without warning. We're not sure why, or even when: although certainly within the last 48 hours. This has only occured this once, and only on this one project.

    The application which interacts with the D'base uses Binding sources and TableAdapter methods, and the table from which the data has disappeared is not directly editable: it is modified programmatically, by means of Insert and Delete methods. Finally, it is continually filtered with a Fill method depending on what is selected elsewhere in the app.

    I'm hoping that someone knows something that may have caused this, or even better, if there is someway to retrieve the data.

    Thanks for your time,
    Qu.
    "Why do all my attempts at science end with me getting punched by batman?" xkcd.

    |Pong||
    Sorry for not posting more often.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Vanished Data

    Quote Originally Posted by Quasar6
    I'm hoping that someone knows something that may have caused this
    Assuming nothing unnatural happened, either you executed a DELETE without a WHERE clause or else you retrieved all the data, deleted it and then saved the changes.
    Quote Originally Posted by Quasar6
    if there is someway to retrieve the data.
    Restore from the most recent backup that still contains the data. If you don't want to lose other changes made since then you'll have to write code to copy just that data from the backup to the live database.

  3. #3
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Vanished Data

    Access tend to be problematic so it is always advisable to have a back-up of your database always.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  4. #4

    Thread Starter
    Hyperactive Member Quasar6's Avatar
    Join Date
    Mar 2008
    Location
    Sol 3
    Posts
    325

    Re: Vanished Data

    Thanks guys.

    Your suggestions helped me find the problem: the only DELETE statement has a WHERE (ID = ?) clause, but I was using a FillBy statement to filter the table and gather the records for deletion, then a foreach loop to delete them (the reason for this was that I wanted to run checks on each row before deleting them). A redundant "Fill" statement that I should have removed had filled the table with everything, and a user had followed this up by pressing the delete button.

    Luckily, and purely by coincidence, the project manager had been making occasional backups, so we could retrieve about 3/4 of the data by copy-pasting the records.

    Taking Dee-u's advice, I've also added a once-a-day auto backup system to the program.

    Cheers and resolved,
    Qu.
    "Why do all my attempts at science end with me getting punched by batman?" xkcd.

    |Pong||
    Sorry for not posting more often.

  5. #5
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    Re: [RESOLVED] Vanished Data

    Data retention/purging/audit policies are often overlooked in designing applications/systems, you really don't want an ever increasing storage size for an access database. If policy was thought out beforehand, design could have incorporated audit considerations and data maintenance, e.g. no actual record deletions... info could be transferred to audit tables (requires on delete trigger, update triggers can also be used to log changes for audit purposes), record could be tagged instead so referential integrity maintained rather than cascading deletions to child tables, and so on and so forth.

    To get the benefit of triggers along with other features, you might want to consider migrating to another database, e.g. Oracle Database Express (limited to 4GB but has APEX), SQL Server, or MySQL.
    Last edited by leinad31; Dec 5th, 2008 at 02:30 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
  •  



Click Here to Expand Forum to Full Width