Repair and compact deleting records???

[Gina Maylone]

Good morning everyone!

I have a client who SWEARS that everytime he compacts and repairs the front end, clients disappear. I saw which client was missing (and she was missing) - but there's no way I can verify how she went missing. Again, he said that it has happened for years - when the compact the front ends. Has anyone else had this experience? I never have.

Access wonkiness is running rampant today! Full moon I think.



~Peace~

[ranman256]

is this client IN the FE?
I dont see how it can delete a record in the BE from compacting the FE.

[Gina Maylone]

Yes, if he compacts the front end it eats clients.

[JoeM]

Why would he ever have need to Compact the Front-End?
The data is stored on the Back-End.

Perhaps they have a corrupt version of the Front-End and need a new one.

[Gina Maylone]

The front end is new about every other day as we make changes and updates.

[Gina Maylone]

Also, if a front end starts acting wonky I always recommend a compact/repair - usually takes care of whatever ails it.

[JoeM]

The front end is new about every other day as we make changes and updates.

What kind of changes, exactly?
Data or other things?
How are you distributing the front-end to your client?
Does each client have their own copy of the front-end?

[Gina Maylone]

He comes up with ideas all the time add a field, send an email, print instead of preview reports, etc. I usually update on my side and remote in and transfer the file to him. Yes, each client has their own front end. He copies the modified fe to their computers and relinks to the be.

[JoeM]

Hmmm....
So, are you the one making all the changes, and then sending him the new FE? If so, I don't think there should be much of a need for him to Compact and Repair the front-end. The size of it after you send it to him should be pretty stable.

[Gina Maylone]

Ok, BUT is it possible that compact and repair is deleting records?

[JoeM]

The way it is supposed to work is the following:

When you delete records from Access, the records are really not purged from the database (they are just removed from your view). That is why deleting records does not decrease the size of a database, and databases get bloated over time. So the records are really, in essence, just flagged for deletion.
Compacting and repairing purges all those records flagged for deletion.

Compacting and repairing should not delete records that are NOT flagged for deletion. My guess is that there may be one of a few things going on:
- The database is corrupt
- There is a cascading updates/deletes situation going on, where deleting records in one table automatically deletes records in another (has nothing to do with Compact & Repair - maybe they just didn't notice it right away)
- User error

[Gina Maylone]

Right. I checked the relationships, there are no cascading delete relationships. And they don't delete their contacts. I had to run an update query to get rid of really old ones recently (and the missing contact was not one of them). I was leaning toward user error myself. BUT that's not always fun to discuss with a client. I just thought that perhaps someone else had experienced this. I KNOW it's NOT supposed to happen - but seriously this guy has some of the strangest things happen to his database. And I've ruled out corruption. Everything works perfectly on my side but as soon as I give it to him - things get wonky. I inherited it that way. Thank you so much for taking the time JoeM!

[John_G]

Might there be an Autoexec macro that runs when the database is opened, or some code in a form that opens when the database is opened, that would attempt to "clean up" the data (perhaps by deleting records with no related ones)? A compact-and-repair essentially restarts the database which would cause that macro or form code to run.

[JoeM]

You might want to check which version of Access he is using, and compare it to which version you are creating it in.

[ssanfu]

Hi, Gina

Just a comment.

We get data monthly for a client. Before importing the data, I do a "Compact and Repair". I did this monthly C & R in A2000 and now in A2010.
Not once has any data been lost due to the C & R. (roughly 6000 employees in the employee table and 1 million records in the main data table.)
And this is over the past 6 years.......

Good luck with the client.