Chunks of Records Disappearing From Table

[hmm2]

Has anyone heard of or come across a problem in which, for no apparent reason, SEVERAL records have seemingly disappeared from a table?

I have a statistical database in which data is imported monthly. There are actually two sets of data for each month, imports and exports (waterborne trade data). One of my tables contained all of the import and export data for 11 months (Jan - Nov); verification and QC checks were made in the usual fashion with each month's data; everything was "normal"; no errors detected anywhere, etc. Somewhere along the line within a 3-month time frame, about a third of the tables data just flat disappeared. I'm talking about a million or so records dropping down to about 600,000 for no apparent reason. And the records that disappeared were not entirely sequential. They were in "chunks", meaning that all of the import records for April were missing, but the April exports were still there. Another month's export records were missing, while it's import records were still there. In other cases, both import and export records were missing for an entire month. But there was no apparent pattern; the first 3 months were fine, then the import or export records would be missing for a month or two, then the next month was still entirely present, then a month here and there entirely "erased", and so forth.

This is a stand-alone database on my hard drive. No one else uses the database, and only one other person knows how to access the database (on my pc; it's not a shared file). I have printed reports prior to this "disappearance" clearly showing that the missing records were present at an earlier time.

I am planning to contact Microsoft about this, but thought I'd check around to see if anyone else has heard of or experienced a similar problem.

ANY CLUES?!?!

hmm

 

[AlexMidd]

I have never come across this before and doubt that it is an Access problem. I'm sure we have all had strange problems that appear to have no cause, but usually there is something wrong in the set up of the database - it's just that we can't find it. I suspect this is the case in this instance as well. Have fun! )

Alex Middleton

 

[hmm2]

That's the typical response to an issue like the one I am questioning - I know, because your response is the same one I would give if the shoe were on the other foot. (I'd be convinced it was simple "operator error" somewhere along the line, and dismiss it.) However, even questioning my own processes, designs, etc., and surmising it was a result of something I caused, I have exhausted every possibility I can conceive. This is an isolated incident with one particular table out of approximately 40+ tables and 25+ databases that have been used in the same fashion, ongoing for several years. I was able to restore a backed-up copy of the database in question which preceded the disappearing act, but am clueless as to how the data could have been deleted from the table in seemingly random chunks.

 

[AlexMidd]

you may be right. All I am saying is that, in my experience, Access does not do things like this off its own bat. The odds are on something being done by a user or programmer (sorry but this means you) somewhere and it just doesn't want to be found - I know, I have done it many times myself!

Sorry I can't be more helpful. Unless someone else has come across this type of thing. Have fun! )

Alex Middleton

 

[petermeachem]

Agree with Alex. The point is that they were not random chunks, but rather complete months. Are you convinced the data was there in the first place? Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[hmm2]

Yes, the data was there to begin with. I have verification reports and other "quality control" checks that could not have been produced without the data being in the table. I also had a backup copy of the database with the entire table, dated 3 months prior to when I found the problem.

 

[evalesthy]

Seems like some systematic troubleshooting is in order - as the others above have implied. How many times has this happened? Have any changes (code/queries/forms,etc) been made that coincide with the problem? If you were to take a copy of the data and run a few dummy imports/exports, etc. can you force the problem to occur? Given the large amount of records you are working with could you make a copy of the database with a small number of records that would be easy to track for testing purposes? As with the other responders - I have never seen Access 'fail' in the manner you are describing - I think you have to either find the database coding problem and/or rule out third party interference (malicious or otherwise).