Recover from possible corruption

[buratti]

I have a database saved on a flash drive. I use this copy whenever I make any updates to it, and keep the production one on my hard drive for actual use and copy it over as the updates are completed. I was making updates last night and quit for the night in the middle of something. I left the database open overnight but I "think" I saved and closed any open forms/modules I was working on before quitting (not absolutely sure though). I returned to it this morning to a message on screen stating , I cant remember the exact message but, something like disk full, unable to continue... This message was refering to my flash drive that the database was running on. I then noticed that my database has grown in size over night from around 20MB to 355MB, which is what filled up the little space I had left on the flash drive. I was able to eventually close the database, without quitting the process in the Task Manager. When trying to re-open it I get the following message:

"The VBA modules in this database appear to have been saved with errors. Access can recover the modules, but you should backup the database first. To cancel so you can make a backupcopy of the database, click Cancel. Then make a backup copy of the database.
If you have a backup copy of the database, click OK. When the database opens, examine the modules to see that they are correct. If they are not, you should revert to a recent backup"

I click Cancel and get this message (well, the short hand version):

"Access has detected corruption in the file. To repair, make a backup then run Compact and Repair. If you are currently trying to repair this corruption you need to recreate the file or restore it from a backup"

I canceled out of everything, closed the database, made a backup copy, reopened it and ran Compact and Repair. Upon completion the errors were gone and my database opened fine, however ALL of my VBA code was competely gone!

My I keep making backups of the corrupted version before I try other things, but in short, how can I fix this without loosing my code?

And also, what would make my database grow in size so much overnight? I did add an on timer event every 15 seconds to check the value of a field in a settings table I have and all it did was change the background color of my main navigation form if the field met a sepcific value, but I dont recall having it "store" and data where it would just contunue to grow and grow.

Any suggestions? I do have a older working backup, but have made many updates since then, and would like to at least try to restore this one before re-doing everything since that version.

 

[CJ_London]

You haven't said which db is corrupted, the one on the HD or the one on the flash drive, I'm assuming the flash drive.

Something like this has happened to me in the past. The way I solved it was to created a brand new db then copy all the objects across in stages.

You also need to do this if you are 2003 or earlier and have exceeded the max db size of 2mb. Later versions seem to recover on compacting. The fact you have run out of space may generate an equivalent issue.

The order I do is 'in db' tables first, followed by any linked tables (by using the linked table manager rather than importing the linked table) then modules followed by queries, forms and macros, compacting between each step, and recompiling after importing modules, forms and macros. Probably not really necessary but each to their own.

Try importing from your original development db first, even if it is corrupted. Not much point using the backups if all the code has gone. If that doesn't work you'll have to start from your latest 'proper' backup - 2nd time around development is usually quicker since you've been there before so there is less experimenting.

You will have to restate any application title, display form and add back any references in the VBA.

If you have any corrupted object it should narrow it down as to where - either by aborting the import or creating an import error table. Assuming you have a corrupted form, try importing a few forms at a time to narrow it down. Once you establish which form is the problem, you'll either need to recreate it or import a version from elsewhere.

Personally I think it is risky to develop on a flashdrive, I would develop on the HD and backup to the flashdrive if necessary. Having learned my lesson in the past:banghead:, I back up at the end of the day using version numbers if I have made even the most simplest of changes.

Backup name is dbname_yyyymmdd+_v1.1a - incrementing the last character. Typically keep 5 most recent plus each copy that has passed UAT

 

[buratti]

Thanks for the suggestions... I have already tried to import objects into a new database before starting this thread, but after I select the "corrupted" database to import from, I get the same error messages stated in my original post. I also was able to open the database while holding Shift to hault any code from running, but as soon as I try to open a form that has any code behind it, I get that same error. Also, if I try to copy and paste into a new db, rather than use the import wizard, I can copy just fine, but as soon as I try to paste in the new db, I get the error. I have tried the /decompile switch but no luck. I fear I will just have to re-create any updates from my most recent backup.

While on the topic of backups, what is a suggested method to "auto backup" with incrimented version numbers? I have a split front end/ back end database, and the general user will never make any changes to the design, only the back end, so something to
A) make a backup of the back end tables on close of the database.
B) Some how determine if the design of any forms, modulas, querys etc. has changed and auto backup the front end with incrimented version numbers as suggested.

I have no "master" close button in my database. The way it is closed is by merely clicking the "X" inthe Access window. Is there any event that will fire before the close of Access itself that I can attach any backup code to?

 

[CJ_London]

I don't actually automatically back up - I just remember to do it manually

but if you wanted some code you could have something like

if msgbox("do you want to backup?",vbyesno)=vbyes then
    FileCopy CurrentProject.fullname, ArchivePath & "\" currentproject.name 'need to expand to change the last character and supply the archive path (I usually use currentproject.path & "\Archive\"
end if

could be behind a button that is hidden to all users except you. If you are in the habit of leaving the system open you could have it on a timer event (without the prompt) - all up to you