Missing records

[Bagheera]

Rather alarmingly, I found that tranches of records have 'gone missing' from
my database. Missing records appear to be in batches throughout the table. I
have a relatively recent backup which shows they were there about a month ago
(this backup has 5,000 records whereas the main file shows only 3,000 now).
Since then, other blocks of records have disappeared. I fear our daily backup
has probably been overwriting the 'healthy' database.

As I only discovered this late on Friday evening (isn't it always the way?),
I haven't had a chance to analyse the missing data to see if there's a
pattern but, at first glance, there doesn't appear to be.

The database is basically a CRM system for a charity and the missing data
relates to the contacts/interactions with our supporters. Records of
supporters that were created at the same time appear to be intact, but
details of interactions have been lost.

If anyone has any thoughts or ideas prior to my facing this when I return to
work tomorrow, I'd be really grateful to hear about them.

Incidentally, I'm the main user of the database. A couple of other people
use it but it's highly unlikely they could have deleted blocks of records,
even by accident.

Many thanks.

 

[Jeff Boyce]

First things first ... and you've already confirmed that you have a backup.

Next, you didn't describe 'how' you are confirming that records are missing.
One way would be to look in the tables themselves. Another would be to look
via queries, or forms. In any of these instances, I believe you can apply a
filter, which means it would appear that records are missing (those filtered
out for display purposes).

If this were mine, there'd be a date/time field in the table(s) that
indicates when the record was created and/or last updated. With those
records remaining, I'd look for a pattern of missing dates, perhaps similar
to what you described facing you tomorrow.

If you'll provide a bit more description about your configuration, folks
here may be able to offer relevant ideas. For example, is this a split
database? Is there a single back-end and multiple front-ends, one on each
user's PC? Is this over a LAN or a WAN?

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Arvin Meyer [MVP]]

First thoughts are which version? And have you installed all the service
packs?

Typically, problems like this are user error. I once had a user delete over
15,000 records thinking that if she didn't save, they'd still be there. She
had Excel experience and thought that datasheets and spreadsheets worked the
same.

It is also possible, though rare, that your network is at fault. You'd get
an error message somewhere if that was the case.

Also make sure that your database is split, with a copy of the front-end on
each workstation.

 

[Bagheera]

Thanks for your speedy reply.

I confirmed that records were missing by looking in the table and, yes, I
checked there wasn't a filter applied (at which point I would have shouted
'hurrah' and had a more peaceful weekend).

Initially, I noticed that one supporter's record was incomplete, which is
what made me delve a little deeper. I knew that they had made a donation
about a year ago but it wasn't showing on the form. Sadly, it wasn't showing
in the table either and nor were its neighbours. About 150 records were
missing in this block

Records are numbered (using the Autonumber facility) so, by sorting by this
field, it was quite easy to spot that a whole block was AWOL. On checking
further, I realised that other blocks of a similar size were also missing.

Yes, the records have a 'create date' and 'modified date' so I will be able
to identify the missing culprits fairly easily, I hope. It shouldn't be too
difficult to identify all the missing blocks by using Excel - rather than
checking each record manually. After that, it's a question of finding the
data again which could take a little while.

It's a single file database on a LAN.

Wish me luck! But it's nice to know you'll be thinking of me!!!

 

[Fred]

You mentioned that you probably lost your backups via. overwriting by newer
backups. While most backups focus on recovering from failure from the
storage device, I suspect that it's much more common to use them to recover
corrupted files or files messed up by human error. In those cases, discovery
of the problem may take a long time. Our low tech solution is backup
scripts which keep a weeks worth of daily backups (the newest one causing
deletion of the oldest one) plus similarly a year's worth of monthly backups,
and 5 years worth of yearly backups.
So, with just 24 copies we can look back 5 years for the good version of a
file or data.

 

[Jeff Boyce]

Bagheera

?? " a single file database on a LAN "??

If you mean the database has not been "split", then both the forms and the
tables exist in a single file on your LAN. This is a formula almost
guaranteed to result in occasional corruption problems and infrequent user
contention. After all, if more than one person tries to open that (single)
file simultaneously, not only is Access trying to pump all the forms and
queries across the LAN to each person (the Network gurus tend to get cranky
about this), but Access also has to figure out who (which user{s}) gets to
see the form and lock the data.

A safer way to design a multi-user database/application is to "split" it ...
the data stays on your LAN in one Access .mdb/.accdb file, while a copy of
the front-end (forms, reports, queries, everything but the data) goes on
each and every user's PC.

Good luck in your rebuilding!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Bagheera]

Thanks for all your comments. It's only recently that other users have been
using the database and it's very rare that more than one person uses it at a
time. That said, I've been meaning to split the database - just in case.
Perhaps now is the time.

I'm pretty sure that this isn't user error - I know my colleagues' IT
capabilities and they would have been hard pushed to have deleted 2,000
records, even by accident as they only use the front-end forms rather than
opening up the tables. We have had a couple of problems with our server - it
managed to delete a colleague's database entirely just before Christmas.
Peace and goodwill towards the IT department was temporarily suspended!

In answer to Arvin's question, it's Office 2007 and I'm pretty sure it's all
up-to-date with the latest service packs etc.

Anyway, guess what I'm going to be doing tomorrow? I shall be hitting the
gin in the UK while all you guys in the US are watching your new president.
Do you get the day off?