Databases Keep Getting 'corrupted'.

[Robeen]

Hi!

This is the third time this has happened to me in the last week.

I am creating a real simple database [from scratch] that allows Multiple Users to enter the amount of work they do each day [typically two or three different records each day - one for each type of work they are involved in].

I have been getting to a point in creating the DB where I have code in there that allows me to Insert rows into the appropriate table - and open and closing the three forms - and then all of a sudden - I cannot open any of the Code anymore and Access throws up a message asking if I want to Repair & reopen the database . . .

I have tried doing a Compact and Repair - but that doesn't work.
I have to create the DB from scratch - because I can't open ANY of the code behind any of the Forms or buttons.



Any ideas on what I need to do - or what I can try?

Thanks!

Robeen

P.S. I just went back in and verified . . . I can get into the Tables and Queries in design mode - there doesn't seem to be a problem there.
It's just getting into the code behind the Forms that I'm having trouble with.

[pbaldy]

If the db isn't split, it should be:

http://www.granite.ab.ca/access/splitapp/index.htm

[Robeen]

Thanks, Paul.
I am going to split the DB.
Will there be a problem if I have the Front and Back on my local machine for now - and then move them to the Network later?
How do I tell the Front End where the BE has been moved to?
Robeen

[Robeen]

I found the answer to my last question about moving the back end here:
http://office.microsoft.com/en-us/ac...010342026.aspx
Thanks again, Paul.
I will report back to let you know if splitting it ended the problem.

[pbaldy]

No problemo!

[Robeen]

I came in this morning and the Split DB did it to me again . . . I can't open the code behind the buttons . . .
Fortunately, I had made a backup [three times yesterday!] . . .
But . . . I'm at a loss for how to proceed . . .

[ItsMe]

I have, on a couple of occasions, had the same thing happen to me. I still to this day don't now why it happened or what I did to fix it. I would get desperate and just start trying multiple changes/adjustments at a time. I would change VBA to lock records while users were accessing them. Close out queries via VBA if too many were open. Increase RAM on select machines. All of which probably had no or little effect.

If I was to choose one thing that seemed to produce a "change" it would be to rebuild a form in a specific way. The change I noticed was a control would function in a peculiar way or not at all. After I rebuilt the form it would function correctly.

I would create a copy of the entire DB. I would go after a specific form, one form at a time. Using the save as I would create a copy of the form. In the copy, I would delete all of the VBA. I would then copy over the code from the original, one control at a time. All the time being careful to only copy the code and not the ENTIRE sub-routine.

In my form copy, I would create a NEW event. Within the NEW sub-routine I would paste the code. For example, I have a control with a click event. In my form copy I would locate the control's click event and create a NEW click event procedure. This would create the container I want to paste the copied code into.

Like I said, I was desperate. After trying many many things, that DB no longer crashes. It was not a small data base either. In fact, I did not recreate each form. I just went after select ones I suspected. Not positive this is your solution. I just sense you are also desperate.

I might add to see if you are using dbOptimisitic verses dbPessimistic. And having a user that likes to run multiple apps on their 10 year old machine. Check for minimum PC requirements at the client.

[pbaldy]

Other options include decompiling:

http://www.fmsinc.com/MicrosoftAcces...Convention.asp

and importing all the objects into a new database.

[ItsMe]

Other options include decompiling:

http://www.fmsinc.com/MicrosoftAcces...Convention.asp

and importing all the objects into a new database.

Paul,

Would the decompile process catch the same errors I corrected by manually rebuilding a form one control at a time? That seems to be what I am reading in the link you provided. If so, that's very cool.

[pbaldy]

I'm not sure, just know it's in the bag of tricks to solve problems like these. I forgot about SaveAsText/LoadFromText, discussed here about 1/4 down:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

[ItsMe]

Awesome.......

[ssanfu]

After adding 6 report and copying the code from one report

Private Sub Report_Close()
    DoCmd.Restore
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Maximize
End Sub

Private Sub Report_NoData(Cancel As Integer)
   MsgBox "No data found for the year " & Forms!reportlist!cboYear & " !!"
   Cancel = True
End Sub

and pasting the code into the other 5 reports, my FE blew up. It would open in design view but not in normal view.

Tried Compile & Repair.
Tried de-compile
It took me a week to discover it was the VBA project that was corrupt. Panic city. FE corrupt and data was due to be imported!!!!

I deleted the reports, imported the remaining objects into a new dB, all was OK. Imported the new reports... and bam!!! corrupt again.

I deleted the reports, imported the remaining objects into a new dB, all was OK again. Tried SaveAsText/LoadFromText for the reports. Was OK as long as I didn't import the reports with code.

Re-created the reports one at a time, saving, Compile & Repair between each report.
Then added the code to the reports: typed in the no data sub for one report, save, close the report, Compile & Repair, close the dB.
On to the on open sub. Same steps as above.

Then the next report. Same steps for the rest of the reports.

Took a lot of time (week and a half), but no corruption..... so far.

I think copying the 3 subs from one report into 5 other reports REALLY confused Access. Especially since the the sub names were the same.

In the future, I will not be mass copy & pasting code....


I have to add reports to print 1099's..... they are going into a new FE.. Not taking any chances

[ItsMe]

I guess I am sticking to the slow and laborious technique too.

[Robeen]

Thanks, Paul!!
I just did the command line msaccess.exe /decompile . . . and I am able to get into the code behind those buttons again!
Big relief!
It's not like this is a complex DB or anything - just a real pain that it kept happening.
I hope the decompile took care of the problem - I'll post back here if the problem rears its ugly head again.
I'm feeling a lot better about Access again

[pbaldy]

I hope so Robeen. I haven't had a lot of problems with corruption, but I've had a couple that decompiling fixed. However, I had a problem a month ago that it didn't fix, and I ended up having to go to a previous version and redo the updates I had done. Luckily I always keep several versions back as I make updates to apps.