Missing Records

[ed333]

800+ missing records!!!! HELP, PLEASE!!!

I just had some of my users report a back-end corruption. It seemed like no big deal, until she went to see if the last edit she made was there. Well, turns out we're missing the last 800 or so records that were entered. No big deal, I thought, we back up the server every night. I'll just whip it out of the backup file and restore that one table.

WRONG!!!

Oddly enough, even though she had access to these records today (and yesterday), THEY DO NOT APPEAR in the backed up MDB file!!

We use Backup Exec Desktop Pro (by Veritas), Access 2002 (and runtime) and are running Win 98 and XP (the server runs Me, though). The users have a local front end .mdb, with all the data on the back end on the server.

These are our billing record for september, so we really need them in order to send out the bills.


If anyone has any suggestions, please respond. I will be eternally grateful!

Ed

 

[Pat Hartman]

Can you find them if you open the table directly? Did you perhaps delete a "parent" record for which you did not have RI enforced?

 

[ed333]

Pat,

Well, the table missing the records is the first place I looked. The records run from 1 to 8750. My user ran reports yesterday which show records up to 9600.

Incidentally, the .mdb file is extractable from the backup, but I tried Fri, Sat and Sun -- no sign of the missing records. I don't unterstand why these were accessable until 3 pm today, and they are not on the backup? Am I missing something?

Only this one table seems affected, thankfully.

Thanks for the idea, though.
Ed

 

[WayneRyan]

Ed,

Let's start with the obvious. Have you backed up what you
have now and tried a compact/repair?

Failing that, then import all objects into a new, blank db?

Wayne

 

[namliam]

Is your report maybe based on a query that creates duplicates?

Thus expanding your tru no of 8750 to 9600!!! Where you just truly have 8750... it would appear verry wierd to have a report say 9600 on monday and the backup sunday just has the 8750. I cant imagine they input the 800 in just the monday....

Check your query/report design, is my suggestion...

Regards

 

[ed333]

Thanks for the suggestions, guys. The query for the report comes straight from the Charges table. We first noticed the problem in the form for editing individual charges, when after the compact/repair we were not able to pull up any of the records we were working on. This led to further investigation and discovery of those missing records. I know for a fact that these records were all entered within the last 3 weeks. I will try importing the tables to a new db, and see what happens.

Ed

 

[ed333]

I tried the compact/repair and import -- no luck. Next I tried opening up the .mdb from the backed-up file with a text editor. Amazingly enough, the text from the missing records is there! Why can't access see it? I really am pulling my hair out on this one.

 

[namliam]

Well, the table missing the records is the first place I looked. The records run from 1 to 8750. My user ran reports yesterday which show records up to 9600.

I know for a fact that these records were all entered within the last 3 weeks.

And you are unable to recover them from backup. Strange....

Either your FACT is no fact and no input was done. Or there is some sort of filter active on your table....

I cannot think of anything other than that....

Regards

---

Hmz crosspost, Your newer post leads me even more to the filtering. There must be some sort of filter/query problem....

 

[Autoeng]

I tried the compact/repair and import -- no luck. Next I tried opening up the .mdb from the backed-up file with a text editor. Amazingly enough, the text from the missing records is there! Why can't access see it? I really am pulling my hair out on this one.

Can you post data from the text editor showing one record that is retrievable and one that is not (8750-8751)? Post everything in the record as it might be relevant to finding what the problem is.

 

[ed333]

I put a fresh install of access on a box that is off our network. Then I extracted the .mdb from last thursday's backup.
I copied that to a cd-r and physically walked it over to the box with the fresh access install. Upon opening that .mdb file, the number of records is still at 8750. I checked for filters, there are none set. There are no queries in use here, either, just the raw table. I cannot comprehend how data that was used on monday and tuesday is NOT appearing in the backed-up .mdb
(It IS there if you view it as ANSI Windows Text under Word Perfect, though.)

Thanks, though, namliam.

Ed

 

[ed333]

I have been seaching through the text trying to determine how access stores its data. It appears to separate "normal" fields from memo fields. The memo fields from the lost data appear present, but I am unable to determine where their corresponding data resides. Also, numerical values are not stored as text, making this more difficult.

I have been going backwards through my backups, and have discovered that 9-9-03 had 9029 records in the charges table, while the backup for 9-10-03 only has 8750. However, the file sizes continue to grow through 9-15, indicating (possibly) that the data may be there. However, a compact/repair shrinks the file down.

I can email zipped copiesof the table from before and after the glitch happened, if anyone thinks it may help.

Many thanks,
Ed

 

[Autoeng]

Can you post data from the text editor showing one record that is retrievable and one that is not (8750-8751)? Post everything in the record as it might be relevant to finding what the problem is.

Previously requested.

 

[ed333]

Thank you all for your support. On a whim, I tried exporting the table as ASCII delimted text, just to see what turned up. ALL OF THE RECORDS WERE THERE!!! I copied my table structure and imported back into the new table, and it appears that I haven't lost anything. Does anyone know why this may be?

Ed

 

[Autoeng]

Post the Ascii delimited text for records 8750 - 8751 so we can see if there is anything that would explain it.

 

[ed333]

Here is 8749-8753, taken from teh backup done on 9-15.


8749,9/5/2003 0:00:00,"58582.s1",1,,,,,600.00,,,200.00,3.00,,"DEL","Prepare Clearance Opinion","4665.0","DEL","A",,,,,,,,,0.00,0.00,,,
8750,9/5/2003 0:00:00,"58481.S1",1,,,,,150.00,,,200.00,0.75,,"DEL","Prepare Patentability Opinion","4665.0","DEL","A",,,,,,,,,0.00,0.00,,,
8751,9/5/2003 0:00:00,"58582.s1",1,,,,,600.00,,,200.00,3.00,,"DEL","Prepare Clearance Opinion","4665.0","DEL","A",,,,,,,,,0.00,0.00,,,
8752,9/5/2003 0:00:00,"58583.00",1,,,,,200.00,,,200.00,1.00,,"DEL","Clearance Matters Discussion With Scott Barker","4665.0","DEL","A",,,,,,,,,0.00,0.00,,,
8753,9/3/2003 0:00:00,"44641.33",1,,,,,50.00,,,200.00,0.25,,"ASN","Cold Call With Attorney Black re Patent Infringement Issues","4776.0","GDK","A",,,,,,,,,0.00,0.00,,,




Even though I've been able to reconstruct the data, I need to find out why this is happening. The backend just corrupted again when 3 people were entering (different) data at the same time a few minutes ago. On repair, about 15 records were again lost.

Thanks,
Ed

 

[Autoeng]

It looks like all of the data is in good condition so I don't see anything there. Do you experience network dropoffs on machines?

 

[ed333]

I was just reading Ms KB 283849, which indicates that a network data collision or perhaps faulty hardware (card, cable, etc.) could be the cause of the corruption. We are getting a new server in about a week or so, but if a user's machine is causing the problem, I imagine that we'll still have difficulties. I'm not too sure how to track down those types of hardware malfuctions.

(To answer your question, though, Autoeng, no one is dropping off the network.)


Thanks,
Ed

 

[GumbyD]

Just a thought - I work on multiuser access databases. Microsoft added a "Feature" to windows 2000 and above called Opportunistic Locking which attempts to assist in program management. To bad it is not very good at it. When you run Access and this is not disabled it can cause problems similiar to the ones you are experiencing. I posted the instructions on how to disable Opportunistic Locking on another thread in this forum, so you should be able to find them using a search. Good luck.

GumbyD