3 Instant Fixes To Resolve MS Access “The Database In An Unexpected State” Error

Does your Access Database frequently showing “The database is in an unexpected state” error message while you tries to open up your database in Access 2000 or other later version of Access?

When this “unexpected state” error pops up on your screen you are not allowed to perform any operation on your database. To do so you need to repair your database. If you don’t know how to perform this access database repair task then just go through this complete post.

As this contain complete fixes to resolve down this “The database is in an unexpected state”  error  whether it is occurred in any of the access database version 2007/2010/2013/2016. But do this as soon as you can; otherwise this will end with the loss of crucial Access database data. 

What’s This “The Database Is In An Unexpected State” Error Is?

Well this error is encountered at the time of opening a database in Microsoft access 2000 or a later version; you may receive the following error message:

The database is in an unexpected state.

What Are The Possible Causes Of Encountering “Unexpected State” Error In Access?

  • This specific error message pops-up when user tries to convert Access Database created in Microsoft Access 97 or an earlier version, by using the DOA CompactDatabase method. This will left your access database in partially converted state.
  • Somehow the Access Database is severely got corrupted.

How To Fix Access Database Unexpected State Error ?

Manual Method To Resolve "The Database In An Unexpected State" Access Error

Solution 1: Retrieve Access Database data and database queries when you don’t have the original non-secured database.

This method is applicable when you don’t have a copy of the original non-secured database in its original format and you have tried standard corruption troubleshooting techniques. Try to recover the database data and database queries.  To perform this, just follow down these steps:

  1. First of all make a backup copy of your original access database.
  2. Now start up your access 2000 or later version.
  3. For Access 2000, Access 2002, or Access 2003
    • Tap to the Blank Access database, type the new database name in the File name box, and then hit the Create.

Access 2007

  • Hit the Office button, click New, click Blank Database, and then click Create to make a new database.
  1. For Access 2000, Access 2002, or Access 2003
    • On the Insert menu, click Module option. This will start the Microsoft Visual Basic Editor, and a create a new module.

You may also like:

How To Resolve Microsoft Access “Cannot Find The Referenced Form” (Error 2450)

How To Fix The Access Runtime Error 3847: “ODBCDirect Is No Longer Supported” In Windows 7/8/10 PC?

Access 2007

  • On the Create tab, click the down arrow below Macro, and then click Module. The Microsoft Visual Basic Editor starts, and a new module is created.
  1. From the Tools menu, tap to the References
  2. From the Available References list, locate Microsoft DAO 3.6 Object Library, and then hit the Microsoft DAO 3.6 Object Library check box.Note:  DAO 3.6 is now also available on Windows XP Home Edition.
  3. To close the References dialog box, click OK.
  4. In the new module which you have created, paste the following code.

Sub RecoverCorruptDB()
    Dim dbCorrupt As DAO.Database
    Dim dbCurrent As DAO.Database
    Dim td As DAO.TableDef
    Dim tdNew As DAO.TableDef
    Dim fld As DAO.Field
    Dim fldNew As DAO.Field
    Dim ind As DAO.Index
    Dim indNew As DAO.Index
    Dim qd As DAO.QueryDef
    Dim qdNew As DAO.QueryDef
    Dim strDBPath As String
    Dim strQry As String
    
    ‘ Replace the following path with the path of the
    ‘ corrupted database.
    strDBPath = “C:\My Documents\yourDatabase.mdb”
    
    On Error Resume Next
    Set dbCurrent = CurrentDb
    Set dbCorrupt = OpenDatabase(strDBPath)
 
    For Each td In dbCorrupt.TableDefs
        If Left(td.Name, 4) <> “MSys” Then
            strQry = “SELECT * INTO [” & td.Name & “] FROM [” & td.Name & “] IN ‘” & dbCorrupt.Name & “‘”
            dbCurrent.Execute strQry, dbFailOnError
            dbCurrent.TableDefs.Refresh
            Set tdNew = dbCurrent.TableDefs(td.Name)
            
            ‘ Re-create the indexes on the table.
            For Each ind In td.Indexes
                Set indNew = tdNew.CreateIndex(ind.Name)
                For Each fld In ind.Fields
                    Set fldNew = indNew.CreateField(fld.Name)
                    indNew.Fields.Append fldNew
                Next
                indNew.Primary = ind.Primary
                indNew.Unique = ind.Unique
                indNew.IgnoreNulls = ind.IgnoreNulls
                tdNew.Indexes.Append indNew
                tdNew.Indexes.Refresh
            Next
        End If
    Next
        
    ‘ Re-create the queries.
    For Each qd In dbCorrupt.QueryDefs
        If Left(qd.Name, 4) <> “~sq_” Then
            Set qdNew = dbCurrent.CreateQueryDef(qd.Name, qd.SQL)
        End If
    Next
    
    dbCorrupt.Close
    Application.RefreshDatabaseWindow
    MsgBox “Procedure Complete.”
End Sub

Note:

The above code will import all tables and all queries from corrupted database into the current database.

Replace C:\My Documents\yourDatabase.mdb with correct path and file name of your database.

  1. To run the code, click Run Sub/User Form on the Run

Solution 2: Use The Convert Database Command When You Have The Original Database

If still you have a copy of original database in original format, then use the convert database command.  To perform this, just follow down the complete step:

Access 2000, Access 2002, or Access 2003

  1. Prepare a backup copy of original database
  2. Now start Access 2000 or a later version.
  3. From the tool menu, tap to the database utilities, click convert database and then tap to the Access 2000 File Format.Note: if you are an access 2000 user only, To Current Access Database Version appears on the Convert Database
  4. In the Database to Convert Fromdialog box, tap to the database file name which you want to convert, and then hit to the Convert option.
  5. In the Convert Database Intodialog box, just assign new name to your database file, and then hit to the Save option.

Access 2007

  1. Keep one copy of the original database.
  2. After then try to open up your database.
  3. After opening the Access 97 or Access 95 file format .mdb database, Access displays the Database Enhancementdialog box. You are prompted to upgrade your database.
  4. Tap to the yes option to upgrade the database to whatever file format you have selected. After converting to such make a design changes to the file in Access 2007.
  5. However, you can no longer open the database using the version of access earlier than version to which you have converted the database.

Solution 3: Automatic Solution To Fix “The Database In An Unexpected State” Access Database Error!

Features of MS Access Database Repair & Recovery Tool

Access Repair And Recovery software is particularly designed to repair corrupt or damaged mdb or accdb files. It comes with powerful algorithm that scans even severely corrupted access database files. It also has highly interactive GUI that helps user to easily operate and repair Access database without making changes in the original structure of the file. It easily restores damage or corrupt tables, queries, indexes and other data.

This tool can easily fix all minor and major database errors and problems of MS Access 2013, 2010, 2007, 2003, 2002, 2000 on almost all version of Windows operating system such as Windows 8/7/Vista/XP/2003. Free trial version is available that you can install to check the features and reliability of the software.

3 Instant Fixes To Resolve MS Access “the Database In An Unexpected State” Error

Leave a Reply