Dreaded corruption strikes again

[CoopEng]

The nightmare that is corruption. Every programmers greatest fear. And I am not immune to it either unfortunately. This is the first corruption I've had to deal with so I was hoping some people with a bit more experience could give me a hand with this one.

In Access 2003 I have a front end and a back end. The front end works fine until it calls upon a certain table. And for some reason (corruption) calling this table in certain forms causes the whole database to crash.

The front end is fine, no problems there. I've traced the issue down to a single record in one of my backend tables. Every time I try to look at this record (even if it simply appears on screen) the database will crash and I can't do anything to stop it or prevent it.

I've tried to compile, compress, repair, decompile and none of it does anything. Actually when I try to 'compress and repair' my backend, the thing crashes too.

It's becoming frustrating. Is there anything that you guys can think of to help solve this problem? any suggestions or similiar experiences you can share that might help?

Thanks in advance

[mshmyob]

Yep. Seen it, been there, done it. When it happens to me I always have to open the table manually (not with your APP - use Access itself or any tool that can read an Access databse file). Scroll through the table(s) until you see the corrupted record(s) (it will usually have a bunch of ASCII characters in the fields) when you find them delete them.

It has always worked for me. Good luck.

I have never found the Access compress/repair to be of much use.

cheers,

The nightmare that is corruption. Every programmers greatest fear. And I am not immune to it either unfortunately. This is the first corruption I've had to deal with so I was hoping some people with a bit more experience could give me a hand with this one.

In Access 2003 I have a front end and a back end. The front end works fine until it calls upon a certain table. And for some reason (corruption) calling this table in certain forms causes the whole database to crash.

The front end is fine, no problems there. I've traced the issue down to a single record in one of my backend tables. Every time I try to look at this record (even if it simply appears on screen) the database will crash and I can't do anything to stop it or prevent it.

I've tried to compile, compress, repair, decompile and none of it does anything. Actually when I try to 'compress and repair' my backend, the thing crashes too.

It's becoming frustrating. Is there anything that you guys can think of to help solve this problem? any suggestions or similiar experiences you can share that might help?

Thanks in advance

[CoopEng]

I try opening and viewing the records just from the backend. So i open the table and scroll down the records. I found some with ASCII characters and deleted them. But there's one spot where as soon as the record appears on screen (or where it would be... it doesn't display the record just a large white bloch) when it appears, access crashes. That might make deleting it a little but trickier.

Any back-up plans, or other ways to delete the record. If it helps I know the record number. Is there a query I can run to delete a specific enrty? Does that work for corrupted records though?

Yep. Seen it, been there, done it. When it happens to me I always have to open the table manually (not with your APP - use Access itself or any tool that can read an Access databse file). Scroll through the table(s) until you see the corrupted record(s) (it will usually have a bunch of ASCII characters in the fields) when you find them delete them.

It has always worked for me. Good luck.

I have never found the Access compress/repair to be of much use.

cheers,

[mshmyob]

Again this has happened to me. Let me try and remember how I fixed it and I will get back to you.

I believe I may still have a corrupted file to work with (lol).

cheers,

I try opening and viewing the records just from the backend. So i open the table and scroll down the records. I found some with ASCII characters and deleted them. But there's one spot where as soon as the record appears on screen (or where it would be... it doesn't display the record just a large white bloch) when it appears, access crashes. That might make deleting it a little but trickier.

Any back-up plans, or other ways to delete the record. If it helps I know the record number. Is there a query I can run to delete a specific enrty? Does that work for corrupted records though?

[CoopEng]

Haha. alright thanks

When the file got too corrupted you just left it there. nice.. haha.

Again this has happened to me. Let me try and remember how I fixed it and I will get back to you.

I believe I may still have a corrupted file to work with (lol).

cheers,

[mshmyob]

No I fixed it I just kept it as a backup until the client said everything was OK and I never got around to deleting it.

Haha. alright thanks

When the file got too corrupted you just left it there. nice.. haha.

[CoopEng]

Here' s what I'm doing (with little success mind you).

So I've narrowed the corruption down to those few records. I copied all of the records that were not corrupt and put them into a new table. When I run the forms from this table everything works fine. The problem is that the ID field for the table has to be an autonumber. However I can't just change the field to an autonumber after i've pasted the new records (i get an error message saying I can't). If I make the field an autonumber before I pastel, then the ID's of the pasted records are lost and become 1,2,3,4 again. Not good.. another kicker is that the ID's of the pasted records aren't sequential. They do go in increasing order but there are large gaps in between some records. For instance the ID's could be; 28124,28125, 123678, 123679. That just complicates things further.

Is there a way to get around this.... Or have you made any progress from your older corruption issue that you think might help.?

[CoopEng]

Hooray. I got it. And by sheer randomness too. I had tried to run a delete query before (one that I had copied from another db and modified for this one) and that didn't do anything.

For some fluke reason I decided to rewrite the delete query from scratch and, of course, it deleted the record just fine.

I did nothing different between the 1st delete query and the 2nd but for some reason the 2nd one took. How access just randomly decides when to work and when not to work I have no idea. Crazy thing.

Thanks for your help though. I appreciate your time and consideration

Best Regards

[mshmyob]

Congrats.

You discovered one of the 'undocumented features' of Access, and they don't even charge extra for it.

cheers,

Hooray. I got it. And by sheer randomness too. I had tried to run a delete query before (one that I had copied from another db and modified for this one) and that didn't do anything.

For some fluke reason I decided to rewrite the delete query from scratch and, of course, it deleted the record just fine.

I did nothing different between the 1st delete query and the 2nd but for some reason the 2nd one took. How access just randomly decides when to work and when not to work I have no idea. Crazy thing.

Thanks for your help though. I appreciate your time and consideration

Best Regards