473,320 Members | 1,883 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

8,435 Expert 8TB
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?
Mar 23 '07 #1
9 2002
Denburt
1,356 Expert 1GB
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.
Mar 23 '07 #2
Killer42
8,435 Expert 8TB
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.
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.
Mar 23 '07 #3
Denburt
1,356 Expert 1GB
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.
Mar 23 '07 #4
Killer42
8,435 Expert 8TB
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.
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...
Mar 23 '07 #5
Denburt
1,356 Expert 1GB
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. :)
Mar 23 '07 #6
nico5038
3,080 Expert 2GB
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)
Mar 24 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
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
Mar 25 '07 #8
Killer42
8,435 Expert 8TB
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...
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.
Mar 25 '07 #9
Denburt
1,356 Expert 1GB
.45 caliber... :)
Mar 25 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Logan | last post by:
Several people asked me for the following HOWTO, so I decided to post it here (though it is still very 'alpha' and might contain many (?) mistakes; didn't test what I wrote, but wrote it - more or...
11
by: CSN | last post by:
Is it possible to iterate over an array in plpgsql? Something like: function insert_stuff (rel_ids int) .... foreach rel_ids as id insert into table (rel_id, val) values (id, 5);
8
by: Vladimir | last post by:
Hello, I have a table in MS Access database. It has one field (with BYTE datatype) that has several properties set in Lookup tab of table Design View. Display Control = Combo Box. Row Source...
7
by: Doug Vogel | last post by:
Hi All - I have a client for whom I developed an Access 2000 database. The database is split into 2 files - front-end (forms, queries, reports), and back-end (tables). An .mde file has been...
4
by: Ray Dukes | last post by:
What I am looking to do is map the implementation of interface properties and functions to an inherited method of the base class. Please see below. ...
10
by: Rich | last post by:
Greetings, Just starting out with .net. I have a vb.net app where I connect to an Access mdb. I use a connection component from the tool box (conn1), a data adapter component from the toolbox...
8
by: Topper | last post by:
Hello. I have simple web folders structure: -ROOT - BIN WebService.dll WebService.asmx I need to use my WebService.dll not in bin folder - for example, in ROOT. How do i this? How can i do...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.