Post reply

Access Corruption - Form changes wont save.

  • October 10, 2005 at 2:13 am

    #68497

    Hi

    Some element of corruption has crept into an Access database that was working fine. I've made quite a few changes recently nad now find that the changes made to forms will not save and the program may abend.

    I suspect the VBA modules are someway corrupt.  If anyone has pointers as to how to fix the problem - I'd really appreciate.

    Richie

     

  • October 10, 2005 at 2:48 am

    #596226

    1) take a backup of the current version

    2)Try compacting & repairing in Access + recompile the code

    3)See if everything got back to normal

    4)Try decompiling your vba modules (start Access with the  msaccess.exe /decompile)

    5)If possible migrate your current application to a fresh Access database piece by piece.

  • October 10, 2005 at 3:10 am

    #596228

    Thanks Jo

    I've tried all that - still the same answer.

    My next option is to copy out all the code modules to text files, set the form property to No Module , compile and then reintroduce the code modules back in.  I was hoping someone might have a quicker solution.

    Many Thanks

    Richard

  • October 10, 2005 at 11:40 am

    #596342

    One other thing you might try is to create a new db and then try and import all your objects into the new db. This may or may not work, but it is something that Microsoft always seems to suggest when there is corruption in an access db.

  • October 10, 2005 at 12:33 pm

    #596357

    In a worst case scenario, you may have a object (report,form, etc) that is corrupted beyond repair and causes the import to fail or corrupts the new db. If that's the case, you need to identify the corrupt object, then import all the noncorrupt objects into a new db. Then you need to rebuild the corrupted one, either from an old uncorrupt version of the object or from scratch.

    I've had this happen on a few occasions over the years. Not too often fortunately. Also, when importing to a blank db remember to rebuild your references as they were.

  • October 11, 2005 at 2:34 am

    #596461

    Some other tricks ive had success with in the past where importing forms and databases doesnt work....

    - Go into your code modules and simple Ctrl-A (select all) and cut/paste the code back ontop of itself - it seems to refresh all the code. For example, in one case I had a code that completely crashed access if it was run (you could view/edit ok) and no matter what i did to fix it - including importing - nothing worked - there seemed to be some corruption in there. Cut/Paste fixed it! Im guessing cutting the code into the clipboard processes everything into a purly textual basis and any buggered up bytes you cant see that might be doing something naughty get stripped out - so when you paste back in again, bingo.

    - Exactly the same as the above but with forms. Select all, and Cut/Paste back into the form, and then with the code behind the form (all your events should automatically still point to relevant controls). If this doesnt work, it may work doing it between your form and a new empty form.

    - You can try making a copy of your database/form then, go through your form and delete 1/2 of it. If the problem is still there its probably in the 1/2 of your controls that are still left. Delete 1/2 of them, etc. until you find the problem area. If it is part of the collection of controls you deleted, then try on another copy of the database/form and delete the other 1/2 of the controls that you haddent touched before - wittle your way down to the problem control! You can use this with the point above to copy out all the good stuff and recreate the bad stuff - or just delete the bad stuff and recreate it.

    - queries - sometimes recreating a query from scratch can fix a problem - i.e. go into your query, SQL view, cut and paste the sql out, new query, cut paste the sql back in again! I had a similar problem yesterday where a table got renamed and a load of queries borked. I couldnt extract the SQL from the query designer to fix them, but a quick curentdb.querydefs("queryname").SQL style code block extracted it all for me

    Best of luck!

    martin

  • October 11, 2005 at 2:34 am

    #596460

    Some other tricks ive had success with in the past where importing forms and databases doesnt work....

    - Go into your code modules and simple Ctrl-A (select all) and cut/paste the code back ontop of itself - it seems to refresh all the code. For example, in one case I had a code that completely crashed access if it was run (you could view/edit ok) and no matter what i did to fix it - including importing - nothing worked - there seemed to be some corruption in there. Cut/Paste fixed it! Im guessing cutting the code into the clipboard processes everything into a purly textual basis and any buggered up bytes you cant see that might be doing something naughty get stripped out - so when you paste back in again, bingo.

    - Exactly the same as the above but with forms. Select all, and Cut/Paste back into the form, and then with the code behind the form (all your events should automatically still point to relevant controls). If this doesnt work, it may work doing it between your form and a new empty form.

    - You can try making a copy of your database/form then, go through your form and delete 1/2 of it. If the problem is still there its probably in the 1/2 of your controls that are still left. Delete 1/2 of them, etc. until you find the problem area. If it is part of the collection of controls you deleted, then try on another copy of the database/form and delete the other 1/2 of the controls that you haddent touched before - wittle your way down to the problem control! You can use this with the point above to copy out all the good stuff and recreate the bad stuff - or just delete the bad stuff and recreate it.

    - queries - sometimes recreating a query from scratch can fix a problem - i.e. go into your query, SQL view, cut and paste the sql out, new query, cut paste the sql back in again! I had a similar problem yesterday where a table got renamed and a load of queries borked. I couldnt extract the SQL from the query designer to fix them, but a quick curentdb.querydefs("queryname").SQL style code block extracted it all for me

    Best of luck!

    martin

  • October 11, 2005 at 2:36 am

    #596462

    oops appologies for the double reply, i guess this forum can accidentally allow your browser to double post the same message entered once

    martin!

  • October 11, 2005 at 2:40 am

    #596463

    Thanks Martin,

    Sounds like you 've been down the same road.  I hadn't thought of the form objects themselves so thats a fresh idea and also just copying to clipboard and back.  I think you're onto something there - will give it a try today and will get back.

    Thanks again

    Richard

     

     

  • October 11, 2005 at 4:10 am

    #596482

    Goto Start ->Run, Then type

    MSACCESS.EXE /DECOMPILE YourAccessDatabaseHere... and enter(OK)

    This should decompile it. You now have the cnahce to correct whatever is the problem.

    Good Luck

  • October 12, 2005 at 1:02 am

    #596717

    Finally solved the problem.  I had renamed a field in a table from "USER" to "USERNAME" because of reserved words.  A connected query in my forms held the original name and prevented me saving changes to a form.

    Thanks for all your help - it is wonderful to have this resource to fall back on when in need.

    Martin - you mentioned some code to extract the SQL from a query - I'd appreciate if you could post that snippet as I have a query that abends and closes Access when run - maybe your code will allow me extract the syntax.

    Thanks again to All

    Richard

     

  • October 12, 2005 at 4:57 am

    #596751

    its extremly ugly

    Public Sub getQuerysSQL(QueryName As String)

        Dim qd As QueryDef

        Dim result As String

        On Error Resume Next

        Set qd = CurrentDb.QueryDefs(QueryName)

        result = "Bad Query name"

        result = qd.sql

        Debug.Print result

    End Sub

    Setting the result = "Bad Query name" is just a real sheap ass error catch if the result = qd.sql fails

    so really you only need the currentdb.querydefs line and thats it!

    many thanks

     

    martin

  • October 12, 2005 at 5:01 am

    #596752

    Thanks Martin - I'll forgive the kludge

    Richard

  • October 12, 2005 at 4:07 pm

    #596954

    This type of problem is not unusual in a production development environment.  I try the easiest test first which is simply copying the form, renaming the old form, and then renaming the new form to the old form name.  Frequently this will resolve the problem. and if it doesn't you sure haven't wasted much time.  Then go into the more detailed analysis if that doesn't fix it. 

    I've also had the situation where the main menu form was corrupted and the bypass key was disabled in the properties.  This was a more severe problem which was resolved when I created an application to instantiate the offending application and then unset the bypass property.  There is usually a way

     

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply