HowTo: accidentally lose half your Access data without noticing :(

Hi all.

Thought I'd share a recent experience with you, as a cautionary tale.

I have a reasonably large-ish database (around 400MB) holding a single table. One of the fields is a pointer to a (linked) lookup table in another database (this one around 90MB). Despite a small performance hit, storing some repetitious values in the lookup table has saved quite a bit of space (a few hundred MB at least).

I import stuff each day into the main database, also adding a few entries to the lookup table as required. Afterward I always compress both databases since the import process blows out the size considerably.

Since they can each take anywhere from around 15 seconds to a couple of minutes to compress depending on conditions, I often compress them both at the same time. The other day when I did this, it appeared to work as normal. But when I went to copy the updated databases to their final destination, I was startled :0 to find that the main one was something like half the size compared to the previous day.

I think there was some sort of error message about failing to find a linked table or something, but it was lost among the flood of confirmations during the import process.

Anyway, the lesson would appear (to me at least) to be that if you have linked tables, you should not compress both databases at the same time.

Comments, anyone?

re: HowTo: accidentally lose half your Access data without noticing :(

So what happened did you lose some records in the table or did you completly lose a table? I just read was possible to recover a deleted table but havent tried it.

re: HowTo: accidentally lose half your Access data without noticing :(

Half of the table was missing. (Not precisely half, of course, but some of it).

I doubt that I could have recovered it in this case because it was lost during a Compress, which copies everything to a new file. So the lost records weren't deleted - they were simply not copied to the new file before deleting the old one.

This is at least the second major problem I've encountered in this area. The first, some months back, was when I started a compress, then interrupted it (um... with Ctrl-C, I think). It simply stopped, pretending everything was normal, but left me with half my data missing. I rather like MS Access, but there it has some quite serious shortcomings.

re: HowTo: accidentally lose half your Access data without noticing :(

You have me thinking now.... I have scripts that run nightly to update the databases and do reporting etc. and one of the last things it does is a compact. Crap maybe I should adjust some timings throuout the night I have 4 scripts that run at midnight on 4 different db's....

Thanks for the heads up always good to know.

re: HowTo: accidentally lose half your Access data without noticing :(

No problem. It has only happened the once, so may require some pretty coincidental timing, or even something else that went wrong that I wasn't aware of. But probably best avoided if possible, and I thought I should say something.

Perhaps we could develop some standard techniques for data integrity checking. For example, things like...

• Checking that database is larger after importing/compacting than before it started
• Cross-checking "real data" tables against lookup tables, to ensure no orphaned lookup values.
• In the case of an autonumber field, perhaps checking for large gaps between next-to-allocate and last-allocated values. Though to do that, I imagine one would have to create a dummy record. Still, I guess you could back out rather than commit the transaction.
• Um...

re: HowTo: accidentally lose half your Access data without noticing :(

Data Integrity checks are good and you have some ideas I will probably run some tests to see if I can duplicate something similar. I so love blowing things up. :)

re: HowTo: accidentally lose half your Access data without noticing :(

One thing to check is where the temp directory for Office (and Access) is situated.
Doing a compact will work in the same folder as the .mdb, but for importing and other manipulations Access will use a copy in the temp directory.
I did ran into trouble in the past on my D: drive having less than 150Mb free on the C: drive that held the temp files...

Nic;o)

re: HowTo: accidentally lose half your Access data without noticing :(

Hi guys

If you do come to any conclusion on this I would appreciate an article on it even if it is just a cautionary tale as per Killers post.

Mary

re: HowTo: accidentally lose half your Access data without noticing :(

I know it wasn't a lack of disk space in my case. Not sure which disk the temp files were on, but it would have had at least 10-15GB free, while the database file was a few hundred MB.

re: HowTo: accidentally lose half your Access data without noticing :(

.45 caliber... :)