Random Loss of Data

[jbstearns]

I have a front end/back end Access 2007 database, and I am experiencing random loss of data! Maddening!


It isn't data that we're necessarily in the midst of using. We will just go to access a record that we KNEW was there, and may find it gone.
It is a database for training courses. My tables are pretty simple: students, classes, and attendance which says which students attended which classes with additional info about that attendance. This morning, I re-entered a class twice (after it disappeared the first time and second time), and re-attached the students to the course, and then it disappeared again, and took another course with it! Then a totally separate type of course was noticed as being gone.
It seems isolated to the classes disappearing, and therefore with cascading the students which are attached to the classes. I don't believe I'm losing the students from the student table.
The only thing different about this database from my other 2 large databases is that there are photos attached to the class attendance, as we have to have a photo each time they come. So it makes the data file larger with the photos.
Any ideas, suggestions, fixes?
I am wondering if I should take off the 'cascade update/delete' from the table relationships to help prevent data loss (i.e. just the class but not the class attendance) and I would only have to re-enter the class, but that's only a half-fix.
Help!
Jessica

[June7]

Why would classes be disappearing? Are they deleted? Why delete records? Deleting records should be a rare occurrence. There is no mystery if Cascade is invoked and records are deleted.

Are you aware Access has a 2GB size limit? Embedding images can use that up quickly.

[Rawb]

Are you entering data directly into the Tables or using a Form?

I'm going to just throw out a whole bunch of different things to try/check:

• Make sure you're not running the DB from your e-mail or a temporary Folder (yes, I've seen this happen).
• Backup your DB and then try a Compact/Repair.
• Import everything (Tables, Forms, Macros, etc.) into a new DB and try using it for a while.
• If multiple users are all accessing the DB, make sure it's a split DB with a front-end installed on each user's computer (don't run the front-end from a network location if you can help it).
• If multiple users are all accessing the DB, make sure none of them are deleting the Records.
• If using Forms to enter/update data, make sure you don't have any Macros/VBA code that's deleting the Records.

[jbstearns]

exactly! classes shouldn't be disappearing! we aren't deleting them on purpose. occasionally, we delete a class due to an error in making the entry of the class, like it shouldn't even exist, but that's about it. I have it set to cascade delete to help avoid other people always needing me to do database background work. and the file is password protected, so even though I couldn't imagine any maliciousness being involved, it would be prevented.

however, your info about the 2GB limit may have something to do with it. I will check into that! Is the limit for ANY Access file, i.e. my front end OR my back end data?

[jbstearns]

thanks for the suggestions!
the data is being entered using a form. I've checked the background tables and the data really isn't there.
not running from email or temp folder
I've done the compact and repair already almost always my first go-to for fixes, ha.
the front-end/back-end is already set up how you said it should be
we use a password for protection, and those of us who use the database do NOT NOT NOT want this to happen
I like your idea of importing to another DB to try and I will check my macros, code etc. this is a very simple DB of mine, and I don't think I've got many macros or Code at all

[jbstearns]

checked the front end and back end file sizes...both well below 2GB

[June7]

How have you implemented password protection. Are you aware that holding down shift key while opening db will override most db option settings and autoexec macro?

If you want to provide db files for analysis, follow instructions at bottom of my post.

[jbstearns]

the database is encrypted and you need the password to access it in any way...at least, that's how it seems to work! let me know if there's a better way
I use the shift key after entering the password in order to access the background myself

[jbstearns]

I think it's fixed!!! Another person tipped me off to computer crashes being linked to loss of data. I've been having crashes consistently for a while, isolated we think now to a bad USB hub to allow for my printer cable to reach my CPU tower. So we took care of the hardware issue, and I've done repairs and some data re-entry, and I think we're good to go now!!!
thanks for everyone's input!

[orange]

Do you do regular database backups? If not, you should regardless of failures/crashes.
I find it strange that you didn't mention the frequent system crashes until post 9???

When a system/network supporting (multiuser) database crashes, who knows for sure what was physically written, or what buffers were lost in the crash.

You should research Transaction processing and transaction logging for reference.

Good luck

[jbstearns]

we do system backups, but they get updated/overwritten every other night. we've implemented a separate system now to help with when we don't realize there's a problem and the problem is now integrated into the backup. live and learn.
the crashing was my individual PC, not the network drive where the data is stored. that's why I didn't think it was related and didn't mention it. again, live and learn.
we suspect my crashes while accessing the system were the cause of the data loss. time will tell.
but for now I'm considering this issue SOLVED!

[orange]

Just for clarity -- since it was your individual PC that was crashing -- have other users experienced the loss of data that you mentioned? Or was it only your PC that suffered the loss? Seems to me if your copy of the FE was processing some data -and BAM!!! system crash --if you were updating a BE table, then the data you were changing in the BE may not have completed.

[jbstearns]

The data loss was user-wide. We are assuming since I was accessing BE data and I crashed, it caused data loss. Sometimes, the data loss was noted by other individuals first, and only recently was it noticed by me first. the data loss that was noticed by me immediately was what I was working on. I had a crash during that instance, the DB was open on my PC but it was not what was current on my screen and not what was crashing. but it was open. the other instances seemed to be random data loss noticed by others likely hours/days after the loss happened. It's not data we're accessing all the time.

[Rawb]

I've run into a similar issue myself regarding "random" database corruption. In my case, it was caused by one of the users running the FE on Win98 - the differences in Jet between Win9x and Win2k/WinXP were causing corruption virtually every time the database was accessed on the Win9x computer.

One thing I'd strongly recommend is that you look through your Tables to see if there any "junk" Records - in my situation, we would get Records containing non-ASCII text strings (in both Text and non-Text columns) and null values in required fields. We also ran into issues where, in spite of Relationships set up between Tables, Records from one Table would get deleted/corrupted without cascading to the related Tables, essentially breaking the Relationship.

Whenever I ran into that kind of thing, I had to manually break the relationships, delete the corrupted Records (in most cases fixing the data wasn't possible), and then rebuilding the Relationships. It was a pain, but the database wouldn't work correctly again until it was done.

[jbstearns]

Rawb, that does sound like a pain! I have run into corrupted records before, and I have looked through my tables, and thankfully I don't appear to have any.

But along a similar subject...I have 3 databases...two created with access 2003, and this one with the recent trouble was actually my newer one, created with Access 2007. We have discussed upgrading, which eventually we will need to do. I remember when we got Access 2007 that I had several 'tweaks' to do in my prior created databases. I'm a little leery of the work i'll have to do to get us up and running again when we do upgrade. Any words of wisdom to offer in regards to that? Or do you know of any articles that discuss changes I may have to address? (I am a girl-Friday type of employee who has a good amount of Access experience but it's one of about 40 different hats that I wear.)