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

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.

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

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.

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.