Db deleting records from a table

[LEXCERM]

Hi DreamWeaver and thanks for replying.

Here is an example.

I have attached a DB which has one table and over 100,000 records. Manually start the compact and repair then hit ESC quickly after. Records are missing. Just noticed that a table is created called MSysCompactError.

Regards,
Paul.

 

[gemma-the-husky]

sorry to intrude, but just some thoughts

how can you tell you are losing records - how do you "know" the count you have before the compact

if you are using an autonumber key, you should be aware that the compact operation will reset the autonumbers to the next highest number in each table, which may give the illusion of losing records.

why don't you on close app for each user output a text file consisting of the record counts for each table - then you will know for certain whether you are losing records or not, and at least you will have some documentation to trace your problems.

Presumably if you consistently lose data, this would show in funny figures coming from your app.

 

[LEXCERM]

Hi Gemma-the-husky and, no, you are not intruding!

Basically, if you checkout my attachment, the DB has one table. Open the table and look at the record count at the bottom (ignore the auto-number).

After hitting the ESC key before compact has finished, the table count is reduced.

Sorry to sound simplistic.

regards,
Paul.

 

[LEXCERM]

Update.

Hi there,

After a bit more testing, I have found that if the database is split and you ESC during a compile (whilst in the FE) the data remains intact. But when ESC during a compile in the BE, it deletes the data.

Moral of the story: Split your DB's and only compile the FE.

Regards,
Paul.

 

[gemma-the-husky]

i've had a play with that - curious behaviour isnt it

i know what access does in a compact is create a temporary copy of the dbs, convert into that, delete the original, and rename the copy

which is why it closes your open objects before it does all this.

in access help it says you can interrupt the process with ESC or CTRL-BREAK all i can assume is that this aborts the process in an indeterminate state, which is not vey clever at all!