Db deleting records from a table

[Dyonn]

Hello.

I am using a large database, which usually works fine, and is set to compact on close.

Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)

Does anyone have any ideas as to what is causing this?

Thanks.

 

[FoFa]

If you have some kind of referential Integrity defined, and you have deleted records that could cause other records to be deleted.
You could have a really corrupted DB. Have you tried just coping everything to a new DB (Import all the objects) and see if the same thing happens?
One other thing is there could be a process that deletes records flagged for deletion. Do you have any proces like that say on DB close or something? It may not be the compact, but something else that only appears to happen during the compact.
You have a shareware product that limits you by removing records when over your limit (that is a stretch, but thought it worth a mention).

 

[Dyonn]

Thanks for the reply. I will try importing all the objects into a new database.
There aren't any relationships referring to that table, or anything that would make records disappear.

 

[FoFa]

One other question, what are you basing the fact that records are "missing" on?
Just want to make sure the problem is not the results instead of the table/DB (no offence intended), it is just some times we focus on the results, and miss something ("can't see the forest for the trees" kind of thing). Just thought it worth a mention since I have done that (like once maybe )

 

[Dyonn]

The table is used many times a day to run queries, and data is added weekly, so there are always at least 100,000 records in it, and always a different number of records, so it stands out because there are so few records in it, and that there are a round number of records. There are definitely no filters or anything, and the size of the database had gone down quite a lot as well.

 

[FoFa]

OK, so that begs the question, data is added weekly, so would not your row count always grow? Some thing must be deleting rows. What deletes from this table? Maybe it is a process more than the compact (which just shows you the problem).
Are you running a count query against the table?

 

[Dyonn]

The data is all deleted monthly, before the first week of the month is added, and that is a manual process.

Nice try, but it's not that easy

 

[FoFa]

And how does this manual process work for the deletions?
One by One selection?
Run a query?

 

[Dyonn]

I run a delete query to delete all the data from the table. The last time I did that was a week and a half before the disappearing data incident...

 

[Dyonn]

It has now done this at the same time for 3 consecutive weeks. Last week it went from 236,792 records down to 10,000.

This week it went down from 361,320 to 265,000.

We add data to the table every Tuesday morning, it's fine all day Tuesday, but first thing on Wednesday, it has deleted data.

There is an automatic backup every night, which works fine every other day, and for all the other databases, and there is a compact on close, which works fine every other time.

 

[Mile-O]

Why on earth do you delete data on a monthly basis?

 

[Dyonn]

Because there's a lot of data! It gets archived to another db.

 

[FoFa]

Ok, still dosn't mean the compact is removing the data.
Is it one table being effected, or more than one?
Are there keys and indexes on this/these tables, if so what kind?
Is there referential Integrity setup on this DB?
Is this DB replicated?

 

[Dyonn]

It's only 1 table that is being affected, but there are many more in the db. There is only 1 index on the table (and that's not necessary). It's not replicated, and there's no referential integrity.

 

[FoFa]

Good, that simplifies things. I take the DB is not corrupted, so if these rows are being removed, what is cuasing it? Does this happen daily? Have you tried taking the compact/fix out and running for a few days without to see what happens?

 

[Dyonn]

It doesn't happen daily, but it has happened the last 3 Tuesday nights. This Tuesday I will try taking the 'compact on close' off.

 

[Dyonn]

Well, after converting to Access 97 and back to 2002 (and re-creating all the groups manually) for a second time, it seems to have stopped doing it now. Although now there is a spearate strange problem, where on a particular query (either datasheet view or design view), it exits access immediately, no error message or anything...

 

[LEXCERM]

Hi there,

I am having a similar problem Dyonn had with regards to deletion of data when compacting.

What hppens is that if I check the COMPACT ON CLOSE option, compacting works fine when the DB closes. However, if a user hits ESC before compacting has completed, a particular table looses 68,000 records (original data 118,000 reduced to 50,000).

Any ideas on what I should do or why this is happening? I have created a new DB and imported the data but the same thing happens.

Thanks in advance,
Paul.

 

[Dreamweaver]

Turn Of Compact On Close

 

[Dreamweaver]

as a thought are you using a front end/Backend arrangment? if so can't see how data's lost from the data file as it's only the front end with links thats compacted not sure if compact on close if set works when a remote backend is closed???