Corrupt Form - Rescue or Remake?

Question

During my work on this database application, I've apparently managed to corrupt a form in the application - attempting to save any edit to any field on the form will cause Access to crash, and for the database file to report corrupted when Access attempts to re-open it.

I've tried exporting the entire form + controls as text, then re-importing them using VB code (from Allen Browne's website) but it will not re-import without either crashing Access or telling me the form isn't import-able due to an error (no error number or description given).

The form is rather complex, hence I am hesitant to just remake it from scratch, so is there a way to save it? If I do manage to recover it, does this mean I should transfer everything to a new MDB file (in case it's a cascading failure effect)?

To be honest, I've never managed to corrupt an Access database object before, so I don't know if this is something that signals the end of that MDB file, or just something I can correct and continue as before.

Answer 1

Have you looked at the full set of methods for dealing with corruption from Allen Browne: http://allenbrowne.com/ser-47.html ? In particular, decompile.

It may be worth try a copy and paste of the controls into a new form and gradually add back in the code.

Answer 2

Decompile is a good thing to try once you've made a copy of the database. Have you tried saving the form under a different name using File >> Save As? Also try copying and pasting the form with a different name from the database window.

Also it's been my experience that one corrupt form/report does not spread to the rest of the database. That said it doesn't hurt to clean things up. Compact and repair only fixes up tables and related data such as indexes and relationships. To clean up corrupted other objects such as forms and reports you must import them into a new MDB/ACCDB. Tip: Close the database container window if you have a lot of objects. Access wastes a lot of time during the import refreshing the database container window.

Answer 3

I have had that happen to me many times. Here are a couple things that have saved my bacon. I am assuming you are using Access 2003 or higher. Try converting the database to Access 2002 or 2000 format. Then convert that database back to your current version.

Here is some code that I created to combat bloat in previous versions. It also solved this issue for me 95% of the time.

Option Compare Database Option Explicit

Private Sub cmdCreateDuplicate_Click()
'********************************************************
' Author        Daniel Tweddell
' Revision Date 10/27/05
'
' To Combat bloat, we are recreating the a new database
'********************************************************
On Error GoTo Err_Function
    Dim strNewdb As String
    Dim AppNewDb As New Access.Application 'the new database we're creating to manage the updates
    strNewdb = CurrentProject.Path & "\db1.mdb"
    SysCmd acSysCmdSetStatus, "Creating Database. . ."
    With AppNewDb
        DeleteFile strNewdb 'make sure it's not already there
        .Visible = False 'hear no database see no database
        .NewCurrentDatabase strNewdb 'open it
        ChangeRemoteProperty "StartupShowDbWindow", AppNewDb, , dbBoolean, False
        ChangeRemoteProperty "Auto compact", AppNewDb, , dbBoolean, True
        ImportReferences AppNewDb, Application
        .CloseCurrentDatabase
    End With
    Set AppNewDb = Nothing
    Dim ao As AccessObject
    For Each ao In CurrentData.AllTables
        If Left(ao.Name, 4) <> "msys" Then
            DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acTable, ao.Name, ao.Name
            SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
        End If
    Next
    For Each ao In CurrentData.AllQueries
        DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acQuery, ao.Name, ao.Name
        SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
    Next
    For Each ao In CurrentProject.AllForms
        DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acForm, ao.Name, ao.Name
        SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
    Next
    For Each ao In CurrentProject.AllReports
        DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acReport, ao.Name, ao.Name
        SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
    Next
    For Each ao In CurrentProject.AllMacros
        DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acMacro, ao.Name, ao.Name
        SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
    Next
    For Each ao In CurrentProject.AllModules
        DoCmd.TransferDatabase acExport, "Microsoft Access", strNewdb, acModule, ao.Name, ao.Name
        SysCmd acSysCmdSetStatus, "Exporting " & ao.Name & ". . ."
    Next
    MsgBox "Creation Complete!" & vbCrLf & "Reset Password", vbExclamation, "New Database"
Exit Sub
Err_Function:
    ErrHandler Err.Number, Err.Description, Me.Name & " cmdCreateDuplicate_Click()"
End Sub


Function DeleteFile(ByVal strPathAndFile As String) As Boolean
'***********************************************************************************
' Author        Daniel Tweddell
' Revision Date 04/14/03
'
' Deletes a file
'***********************************************************************************
On Error GoTo Err_Function
    DeleteFile = True                   'default to true
    If UncDir(strPathAndFile) <> "" Then   'make sure the file is there
        Kill strPathAndFile             'delete a file
    End If
Exit Function
Err_Function:
    ErrHandler Err.Number, Err.Description, "DeleteFile()", bSilent
    DeleteFile = False                  'if there is a problem, false
End Function

Public Sub ChangeRemoteProperty(strPropName As String, _
                                appToDB As Access.Application, Optional appFromDB As Access.Application, _
                                Optional vPropType As Variant, Optional vPropValue As Variant)
'********************************************************************************
' Author        Daniel Tweddell
' Revision Date 01/13/04
'
' Changes/adds a database property in one db to match another
'********************************************************************************
On Error GoTo Err_Function
    Dim ToDB As DAO.Database
    Dim FromDB As DAO.Database
    Dim prpTest As DAO.Property
    Dim bPropertyExists As Boolean
    Set ToDB = appToDB.CurrentDb
    If Not appFromDB Is Nothing Then Set FromDB = appFromDB.CurrentDb
    bPropertyExists = False 'flag to see if we found the property
    For Each prpTest In ToDB.Properties 'first see if the property exists so we don't error
        If prpTest.Name = strPropName Then
            If IsMissing(vPropValue) Then vPropValue = FromDB.Properties(strPropName) 'in case we want to assign it a specific value
            ToDB.Properties(strPropName) = vPropValue 'if it does set it and get out or the loop
            bPropertyExists = True
            Exit For
        End If
    Next
    If Not bPropertyExists Then ' Property not found.
        Dim prpChange As DAO.Property
        If IsMissing(vPropValue) Then
            With FromDB.Properties(strPropName)
                vPropValue = .Value 'in case we want to assign it a specific value
                vPropType = .Type
            End With
        End If
        Set prpChange = ToDB.CreateProperty(strPropName, vPropType, vPropValue) 'add it
        ToDB.Properties.Append prpChange
    End If
Exit Sub
Err_Function:
    ErrHandler Err.Number, Err.Description, "ChangeRemoteProperty()", bSilent
End Sub

Public Sub ImportReferences(AppNewDb As Access.Application, appUpdateDB As Access.Application, Optional iStatus As Integer)
'********************************************************************************
' Author        Daniel Tweddell
' Revision Date 01/13/04
'
' Copies the current references from the one database to another we're building
'********************************************************************************
On Error GoTo Err_Function
    Dim rNewRef As Reference
    Dim rUpdateRef As Reference
    Dim bReferenceExists As Boolean
    Dim rToAdd As Reference
    Dim sReference As String
    If iStatus <> 0 Then ProgressBarUpdate iStatus, "Referencing Visual Basic Libraries. . ."
    For Each rUpdateRef In appUpdateDB.References
        bReferenceExists = False
        For Each rNewRef In AppNewDb.References
            sReference = rNewRef.Name
            If rUpdateRef.Name = sReference Then
                bReferenceExists = True
                Exit For
            End If
        Next
        If Not bReferenceExists Then
            With rUpdateRef
                Set rToAdd = AppNewDb.References.AddFromGuid(.Guid, .Major, .Minor)
            End With
        End If
    Next
Exit Sub
Err_Function:
    ErrHandler Err.Number, Err.Description, "ImportReferences(" & sReference & ")", bSilent
    Resume Next
End Sub

Answer 4

Others have supplied you with various approaches to possibly recover your corrupted form. Sometimes an code-bearing Access object will become irretrievably corrupt and none of these methods will work. In that case, you'll have to look= at backups to find a non-corrupt version as a starting point and import that and then revise it back to the current state of the object.

I'm posting an answer to suggest that you probably need to change your coding practices if you're encountering corruption in code-bearing objects.

1. First, you need to make sure you keep regular backups and do not overwrite them. Rolling back to an earlier version is always a last resort.

2. Always turn off COMPILE ON DEMAND in the VBE options. Read Michael Kaplan's article on The Real Deal on the Decompile Switch for the explanation of why.

3. In the VBE, add the compile button (and the call stack button) to your regular VBE toolbar, and hit that compile button after every few lines of code, and save your code.

4. Decide on a reasonable interval to backup and decompile your app. If you're doing heavy-duty code pounding, you might want to do this every day. If you've experienced an Access crash during coding, you likely want to make your backup and decompile/recompile. Certainly before distributing to users, you should decompile and recompile your app.

If you follow these practices, the causes of corruption in code-bearing Access objects will be minimized as much as possible, while you will also have plenty of backups (multiple levels of redundant backups are a must, because when backup failures happen, they almost always cascade through multiple levels -- have several types of backup and don't depend on an automatic backup).

But the key point:

Compile often, decompile reasonably often and icky stuff will never have a chance to accumulate in the p-code of your application.

Answer 5

What I ended up having to do was re-create the form, and copy element by element until I discovered that the strSupplierID combo box itself was the cause of the crashing. I re-created it from scratch, manually giving it the same properties, and replacing the VB from stored copies I cut and pasted to the clipboard. The form now works, and I removed the corrupted form, and compacted the database. Thanks for the help, everyone! :)