Corrupt Access DB

  • Anyone know of a free tool to fix Access mdb?

  • You can always copy all objects into a new acces mdb... That usually works.  I don't have anything automated however.

  • Try opening Access but not the file that's corrupt.  From the Tools menu choose Database Utilities / Compact and Repair Database option.  You'll be prompted for the database you want to repair.  I hope this helps!

    This is a good idea to run on Access DB files now and then if you're adding and deleting a lot of data.  Access doesn't automatically reclaim the space used, even for dropped tables.  You can have a file grow to the Access file-size limit (2GB, I believe) without actually having much data in the file.

    Carter



    But boss, why must the urgent always take precedence over the important?

  • have you tried the Jet Compact Utility http://support.microsoft.com/default.aspx?scid=kb;EN-US;273956    Sometimes it works.  I have not see any freeware software, most are shareware around $150. Whats you data worth? Here is a decent article with some options you should try http://www.jamiessoftware.tk/articles/accesscorruption.html

  • Try decompiling also. Start_Run and use the following command (using your info where needed).

     

    "C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" /decompile "C:\Documents and Settings\UserName\Desktop\YourDatabase.mdb"

  • Make sure you have a backup of the corrupted db... might come in handy after the decompile (can save you, but it can crush whatever's left of the db too, that's why it's unsupported).

  • Thanks for all the help. I can not seem to get this thing to work. when I run JetCompact utitlity I get AOIndex issue. Then it just errors out. Then I try all the recommendations you gave and it will not come back around. When I try the utilities you have to pay for it fixes it. Problem is they only show a sampling data unless you pay. Any other ideas?

  • Are you using the correct version of JetCompact? I think the link I sent you was for 2000 here is one for 2003 and Access xp http://support.microsoft.com/kb/295334

  • Yeah that is the one I downloaded. it works with all versions of access it looks like

  • I've worked with Access since 2.0 and still have an app using Access XP that's in the process of being converted to Winforms and SQL 2005. I've used all of the mentioned methods with varying success depending on the severity of the problem. When all else has failed, including things like using a shortcut with the /decompile switch, and you can't interactively open or link to the corrupted file, I've had success writing a little VBA that links to the MDB in question and imports its tables programmatically. This worked in an instance where the user managed to completely run out of disk space and crashed the file. No guarantees.

  • So do you still have the code to programatically import the objects into another mdb?

  • 'create / open a module and paste this code in

    ' you can run in the immediate window by entering

    'ImportTables("[your db full 'path and name]")

    'and pressing enter - no need to create a form

    Public Sub ImportTables(strXDB)

    On Error GoTo ErrX

    Dim tdf As TableDef

    Dim ws As Workspace

    Dim xdb As Database

    ' strXDB is the full path and name of your corrupted mdb

      Set db = CurrentDb()

      Set ws = DBEngine.Workspaces(0)

      Set xdb = ws.OpenDatabase(strXDB)   ' Open database.

     

    For Each tdf In xdb.TableDefs

        DoCmd.TransferDatabase acImport, "Microsoft Access", strXDB, acTable, tdf.Name, tdf.Name

    Next tdf

    Exit_ImportTables:

        xdb.Close

        ws.Close

        Exit Sub

    ErrX:

     

      Resume Exit_ImportTables

    End Sub

    ' good luck

  • Sorry for the lack of comments... but this is better than nothing :

    Public Sub test(ByVal sFolder As String)

    On Error GoTo Gestion

        Dim MyApp As Access.Application

        Set MyApp = New Access.Application

        Dim MyTargetApp As Access.Application

        Set MyTargetApp = New Access.Application

        Dim MyObj As Access.AccessObject

        Dim TargetPath As String

        Dim CopyPath As String

        Dim MyRef As Access.Reference

        Dim FS As Scripting.FileSystemObject

        Set FS = New Scripting.FileSystemObject

        Dim MyFiles As Scripting.Files

        Dim MyFile As Scripting.File

        Dim MyFolder As Scripting.Folder

        Dim sTargetFolder As String

        sTargetFolder = "Rebuilt\"

        If FS.FolderExists(sFolder) Then

            Set MyFolder = FS.GetFolder(sFolder)

            Set MyFiles = MyFolder.Files

            If Not FS.FolderExists(sFolder & sTargetFolder) Then

                FS.CreateFolder sFolder & sTargetFolder

            End If

            For Each MyFile In MyFiles

                If Len(MyFile.name) > 4 Then

                    If LCase(Right(MyFile.name, 4)) = ".adp" Then

                        CopyPath = MyFile.Path

                        MyApp.OpenAccessProject CopyPath

                        TargetPath = "C:\" & sTargetFolder & MyFile.name

                        MyTargetApp.CreateAccessProject TargetPath

                        MyTargetApp.OpenAccessProject TargetPath

                        MyTargetApp.CurrentProject.OpenConnection MyApp.CurrentProject.BaseConnectionString & ";Application Name=" & MyFile.name

                        'previous operation changes the string to this__ trying to reset it WITH Application name :

                        MyTargetApp.CurrentProject.OpenConnection "PROVIDER=SQLOLEDB.1;Application Name=" & MyFile.name & ";INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=Documentation;DATA SOURCE=SERVEUR4"

                        On Error Resume Next 'cannot delete the default references and cannot add an existing referrence

                       

                        For Each MyRef In MyTargetApp.References

                            MyTargetApp.References.Remove MyRef

                        Next

                        For Each MyRef In MyApp.References

                            MyTargetApp.References.AddFromFile MyRef.FullPath

                        Next

                        MyTargetApp.CloseCurrentDatabase

                        For Each MyObj In MyApp.CurrentProject.AllForms

                            MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acForm, MyObj.name

                        Next

                        For Each MyObj In MyApp.CurrentProject.AllMacros

                            MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acMacro, MyObj.name

                        Next

                        For Each MyObj In MyApp.CurrentProject.AllModules

                            MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acModule, MyObj.name

                        Next

                        For Each MyObj In MyApp.CurrentProject.AllReports

                            MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acReport, MyObj.name

                        Next

                        On Error GoTo 0

                        MyApp.CloseCurrentDatabase

                    End If

                End If

            Next

            Set MyTargetApp = Nothing

            Set MyApp = Nothing

        End If

    Exit Sub

    Gestion:

        Select Case Err.Number

            Case Else

                Select Case ErrHandler(ModuleName, "ModGlobals", "Test", Err, Erl())

                    Case ErrResume

                                Resume

                    Case ErrResumeNext

                                Resume Next

                    Case ErrExit

                                MsgBox Err.Description & " : " & Err.Number

                                Exit Sub

                End Select

        End Select

    End Sub

  • I wil have to run the code tonight since it is a rather large db. thanks for the input

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

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