CORRUPT DATABASE? I'VE TRIED EVERYTHING, PLEASE HELP!!!!

[franksirvent]

Hi there

I am trying to make changes to a form and when I try to save it the whole database stops while the HourGlass is there, supposely doing something...
Stays like that for several minutes
The only way to recover is by pressing alt+ctl+del then deleting the MDB from the list (which says 'Not Responding')

I've tried everything:
1. Used /decompile from within DOS (OK)
2. Ran Compile from within Visual Basic Window (OK)
3. Ran Compact and Repair (OK)
4. Imported al ltables, forms, queries, etc to new database. (OK)

But as soon as I do any changes to any form on the database it stops...Not Responding.

This is only a Front End (the data is on a separate database)

None of the above works.

I've tried doing same on two different PC's but same problem, so I assume the problem is within the database...

Any more ideas before I pull out the few hairs I got left ???

thanks

 

[Remou]

I half remember having a problem like this. It was one particular form or piece of code that was causing the problem, but I cannot remember which :-(. If you do not have a huge number of forms, I would suggest importing them in small groups, to see if the problem starts after any particular group.

 

[franksirvent]

Thanks for your input,

Yes, I thought it was too a form which I've been working on lately.

However I also did some testing on other forms, even if I have not touched those forms for months, and some of them save and close ok, but others don't. They also have the same problem and locks the database.

The current form I've been working with I deleted it and imported again from a previous/recent back up, but still does the same thing...weird.

Even previous back ups are doing the same thing too!
I am only going into a form, moving an object from one side to another (small change) and it locks itself as well.
So I can't even start again from a recent back up!

So I am really looking for a 'miracle' cure..if there is one...

 

[franksirvent]

Further more, does anyone know if there is a limit to number of objects (fields, buttons, etc) you can put on a Form?

My form is quite 'congested' with objects...

Can this be the reason ???

 

[Remou]

I only have the vaguest memory of this problem, so bear with me. I think is was a small piece of code, perhaps not even in a form's module. This is why I was suggesting importing the forms one by one. Perhaps what I should rather have suggested, is removing all the code from a copy, and seeing if you still have a problem. Sorry to be even more vague than usual.

 

[RoyVidar]

Yup - even set the HasModules property to no - to completely remove any code from the form - then add it afterwards.

754 controls? Challenge is that it's through the lifetime - so if you've added and deleted, you can still get that high without actually still having them on the form. Also, form sections, attached labels, lines ... all adds up ...

One thing I didn't se mentioned, is the SaveAsText thingie

application.saveastext acform, "nameofform", "c:\nameofform.txt"

then delete it and try

application.loadfromtext acform, "nameofform", "c:\nameofform.txt"

Roy-Vidar

 

[Remou]

There are various limits.

The maximum number of controls allowed on a single form depends on the type of controls used and available system resources. However, there is a fixed limit of 254 control names per form.

 

[franksirvent]

Thanks to all for your input
I think I must have between 150-200 objects on the form so I guess I have not reached the 254 fixed limit?
Nevertheless, I agree that something stays there even after deleting the fields, so probably is not helping...

Roy, what's this about
application.saveastext acform, "nameofform", "c:\nameofform.txt"

I never heard of this? How do I acomplish this?
Where do I put that code ???

remou, I will try your way as well, I have nothing to loose, it takes time (going form by form, etc) but if there is no other way...

thank you all

 

[jsteph]

Frank,
What version of Access? I ask because 2000 had some extreme corruption issues with regard to forms and/or reports that had code behind them. Something to do with the pointer to the code getting trashed and nothing could ever be the same after--even copying to new db, compacting, etc.

Anyway, I'm not sure if this was even fixed by a patch, but it was a huge problem for that version.
--Jim

 

[franksirvent]

Yes Jsteph, I have Access 2000 and yes, ia have LOTS of code behind most forms.

That's really bad news what you are telling me...
Is there some patch that fixed the problem, anyone ????

From what you are saying, that seems to be the problem...it is not logical...
It is a problem I have found every now and then and somehow managed it (by recopying the database, importing forms, etc) but this time seems like 'it had it'.
Any info regarding this Access 2000 problem is appreciated.

thanks

 

[franksirvent]

Further more, anyone can confirm Access 2003 doesn't have this kind of problems ?
Also can I create .MDE files in Access 2003 but for Version 2000 ????

I have over 30 users which all have Access 2000
I know for a fact my IT staff will not upgrade all of the machines to Access 2003 but perhaps they could update mine only...but need to be compatible backwards...

Any ideas are welcome....I am really tired of putting up with this problem...until now I've been able to bear it (redoing things wasn't that bad) but it is obvious to me thhat my application is more and more unstable and, as jsteph says, if copying, compiling, etc doesn't fix the problem it is time to look somewhere else....

thanks

 

[Remou]

I have used Access 2000 for some time, and while I have had problems from time to time, they have always been fixable. I think it is worth putting in some effort to find the problem, before moving on.

 

[lameid]

If you have a backup of the FE does importing the form from it after deleting the 'corrupt' one fix it?

Speaking of backups, if you don't have one, make one before you start playing with the files.

Beyond that it's time for shots in the dark...

Import definition only of your backend only into a new database. Rename the original and replace with the copy. Can you save now? If it does try all the steps from your original post on your backend.

Try deleting controls from the form in small groups until it will save. Then start over from a backup and isolate the control. Try recreating the control and/or checking the data behind it for error results (#Name? or #Error).

I hope one of these ideas helps or spurs something that does. Everytime I run into something really weird, I keep trying stuff even if I think it shouldn't make a difference.

 

[franksirvent]

thanks again for your additional input

I have a front end and back end database. The data found on the back end, has no problems at all.
It is the front end which is giving me hassles.
The thing is that I am constantly updating the front end with new forms, queries, reports, etc.
Yes, I do have daily/weekly backups however because I add new things almost daily, I've gone back 7 days ago and I am missing a lot of stuff I've added on the weekend.
I have tried to recover from the latest back up (Sunday night) and, guess what, it has the same problem as latest (MOnday) copy!
The thing is that on Sunday I didn't added anything new!

As jsteph advises earlier on, when Access 2000 has the problem as he mentions, I need to find other solutions...
I cannot be coding applications with the fear of 'will I loose it all when I try to save it?'
I prefer to find a logical solution...I think trying over and over again is fine...but I prefer to find a total solution to this problem.

For your info lameid, I have taken a 2 weeks old backup, simply moved a control from one side to another of the form (that's all) and gives me the same problem. This is not normal as I have been adding new controls/code after that with no problem, so why this now ?
Is like what jsteph says: "to do with the pointer to the code getting trashed " and there is nothing you can do.
Doesn't make sense.

I will try to:
1. take all code from the form, then import it separately
2. delete recently added fields/objects (but this doesn't fix my problem because I need them there...)
2. I will try to use application.saveastext acform, "nameofform", "c:\nameofform.txt" but I need to know how to do this...

Once again thank you all for your input and ideas...

 

[franksirvent]

Dear all,

For those interested and for those who might find the same problem in future, please note that I found 'part' of the problem!

When I am connected to my office server (where everyone is connected too) is when I have the problem.

But if I disconnect the cable, and work off-line (from a laptop version I have to work from home and/or offline) then I don't have the problem anymore!

I assume from this that being connected to a server is causing some of the problem...somehow when saving the forms it seems like there is a 'conflict' with the back end/office server and causes the problem.

Anyway, for now I will do all my updates etc OFFLINE.
Nevertheless I will take your advise and copy all forms, code etc as it will surely help in cleaning up this mess.

Further more, any new input will have appreciated as the idea is to be able to work off and on line without any problems...

 

[lameid]

If the problem happens on old copies as well, something in the environment changed and not the database. And from the sounds of it, something that effects networking changed (as local copies are working). I think you should talk to your networking guys while it is still fresh in their minds. My hunch is that something effecting file locks or timeouts has changed (maybe a client patch effected networking or the server). It could also be a change in a program with a shared dll or some other update for office that was applied. Maybe you were moved to a segment further from the server, and the added latency is killing you. Maybe they upgraded from Windows 2000 to 2003 and the more restirctive timeouts are causing you problems.

Happy Hunting.

 

[franksirvent]

hi lameid

thanks for input

yes, I have already been asking my IT guys to see what might affect this...they are looking into it...

thanks again to all for your always appreciated help, comments and hits!

 

[lameid]

I'm guessing you have already done so, but sometimes networking guys need a nudge in the right direction. That is why my last post covered some different kinds of things to look for. They may not have any clue what they should be looking for. It depends how much they really know about how Access works (not a network admins priority and usually not knowledgeable about it).

 

[franksirvent]

thanks, you are right, they don't know much about Access.

I have forward them your comments so they can check the right stuff.

Thanks again !

 

[psemianonymous]

Back in the day (Access 97) I had occasional code (not data) corruption issues. The only thing I could do to fix it was to make a backup copy 3 times a day. Yes, I'm serious.

You can quickly build a batch file (or of course .VBS file) to make a directory (on your network share) with the current date and copy your database frontend into that directory with the date_time_databasename.mdb name. You can also run compacts, /decompiles, etc. as they're all command-line parameters so this shouldn't be extremely space-wasteful.


Of course this doesn't help you this time, but it will help you next time. I think in the two years of Access dev I did, I experienced code corruption about 5 times. It happens.