You receive the "The database is in an unexpected state" error message when you try to open a database in Access 2000 or a later version (888634)

RESOLUTION

To resolve this problem, use one of the following methods.

Method 1: Use the Convert Database command when you have the original database

If you still have a copy of the original database in its original format, use the Convert Database command. To do this, follow these steps:

1. Make a backup copy of the original database.
2. Start Access 2000 or a later version.
3. On the Tools menu, click Database Utilities, click Convert Database, and then click To Access 2000 File Format.
   
   Note If you are using Access 2000, only To Current Access Database Version appears on the Convert Database menu.
4. In the Database to Convert From dialog box, click the database file name that you want to convert, and then click Convert.
5. In the Convert Database Into dialog box, type the new name of the database file, and then click Save.

Method 2: Recover the database data and the database queries when you do not have the original nonsecured database

If you do not have a copy of the original nonsecured database in its original format and you have tried standard corruption troubleshooting techniques, try to recover the database data and the database queries. To do this, follow these steps:

1. Make a backup copy of the original database.
2. Start Access 2000 or a later version.
3. Click Blank Access database, type the new database name in the File name box, and then click Create.
4. On the Insert menu, click Module. This starts the Microsoft Visual Basic Editor and creates a new module for you.
5. On the Tools menu, click References.
6. In the Available References list, locate Microsoft DAO 3.6 Object Library, and then click to select the Microsoft DAO 3.6 Object Library check box.
7. To close the References dialog box, click OK.
8. Paste the following code into the new module that you created.

   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 code will try to import all tables and all queries from the corrupted database into the current database. Replace C:\My Documents\yourDatabase.mdb with the correct path and file name of your database.
9. To run the code, click Run Sub/UserForm on the Run menu.

Method 3: Recover the database data when you do not have the original secured database

If you do not have a copy of the original secured database in its original format and you have tried standard corruption troubleshooting techniques, try to recover the database data. To do this, follow these steps:

1. Make a backup copy of the original database.
2. Start Access 2000 or a later version.
3. Click Blank Access database, type the new database name in the File name box, and then click Create.
4. On the Insert menu, click Module. This starts the Visual Basic Editor and creates a new module for you.
5. On the Tools menu, click References.
6. In the Available References list, locate Microsoft DAO 3.6 Object Library, and then click to select the Microsoft DAO 3.6 Object Library check box.
7. To close the References dialog box, click OK.
8. Paste the following code into the new module that you created.

   Function BackupSecureDatabase()
       
      On Error GoTo Err_BackupSecureDatabase
      Dim wrkDefault As DAO.Workspace
      Dim dbsNew As DAO.Database
      Dim dbeSecure As DAO.PrivDBEngine
      Dim wrkSecure As DAO.Workspace
      Dim dbsSecure As DAO.Database
      Dim tdfSecure As DAO.TableDef
      Dim strSecureUser As String
      Dim strSecurePwd As String
      Dim strSecurePathToDatabase As String
      Dim strSecurePathToWorkgroupFile As String
      Dim strTableName As String
      Dim strSQL As String
      Dim dbsTemp As DAO.Database
      Dim strTempPathToDatabase As String
      Dim strBackupPathToDatabase As String
      Dim strLogPath As String
          
   
      ' Set the variables (change for environment).
      strSecurePathToDatabase = "C:\MyDatabases\Northwind.mdb"
      strSecurePathToWorkgroupFile = "C:\MyDatabases\Secured.mdw"
      strSecureUser = "Administrator"
      strSecurePwd = "password"
      strTempPathToDatabase = "C:\MyDatabases\Temp.mdb"
      strBackupPathToDatabase = "C:\MyDatabases\Backup.mdb"
      strLogPath = "C:\MyDatabases\Backup.log"
      
      ' Open the log file.
      Open strLogPath For Output As #1
      Print #1, Time, "Log file opened"
      Print #1, Time, "Variables set"
      
      ' Delete old files.
      If Dir(strTempPathToDatabase) <> "" Then Kill strTempPathToDatabase
      If Dir(strBackupPathToDatabase) <> "" Then Kill strBackupPathToDatabase
      Print #1, Time, "Old backup files deleted"
      
      ' Create the new temp database.
      Set wrkDefault = DBEngine.Workspaces(0)
      Set dbsNew = wrkDefault.CreateDatabase(strTempPathToDatabase, dbLangGeneral)
      Set dbsNew = Nothing
      Print #1, Time, "Temp database created"
      
      ' Open the secured database.
      Set dbeSecure = New PrivDBEngine
      dbeSecure.SystemDB = strSecurePathToWorkgroupFile
      dbeSecure.DefaultUser = strSecureUser
      dbeSecure.DefaultPassword = strSecurePwd
      Set wrkSecure = dbeSecure.Workspaces(0)
      Set dbsSecure = wrkSecure.OpenDatabase(strSecurePathToDatabase)
      Print #1, Time, "Secured database opened from " & strSecurePathToDatabase
      
      ' Open the temp database.
      Set dbsTemp = DBEngine(0).OpenDatabase(strTempPathToDatabase)
      Print #1, Time, "Temp database opened from " & strTempPathToDatabase
      
      ' Loop through the tables in the secured database.
      For Each tdfSecure In dbsSecure.TableDefs
          strTableName = tdfSecure.Name
          If Left(strTableName, 4) <> "MSys" Then
              Print #1, Time, "Export of " & strTableName
              ' Copy the table definition to the temp database.
              If CopyTableDef(tdfSecure, dbsTemp, strTableName) Then
                  ' Then append all the data into the table.
                  strSQL = "INSERT INTO [" & dbsTemp.Name & "].[" & strTableName & "] SELECT * FROM [" & strTableName & "]"
                  dbsSecure.Execute strSQL
              End If
          End If
      Next tdfSecure
   
      ' Close open objects.
      dbsSecure.Close
      Print #1, Time, "Secured database closed"
      dbsTemp.Close
      Print #1, Time, "Temp database closed"
          
      ' Compact the database into the backup database.
      DBEngine.CompactDatabase strTempPathToDatabase, strBackupPathToDatabase, dbLangGeneral
      Print #1, Time, "New backup database created at " & strBackupPathToDatabase
      
      ' Delete the temp database.
      If Dir(strTempPathToDatabase) <> "" Then Kill strTempPathToDatabase
      Print #1, Time, "Temp database deleted"
      Print #1, Time, "Log file closed"
      Close #1
   
   Exit_BackupSecureDatabase:
   
      Set wrkDefault = Nothing
      Set dbsNew = Nothing
      Set dbeSecure = Nothing
      Set wrkSecure = Nothing
      Set dbsSecure = Nothing
      Set tdfSecure = Nothing
      Set dbsTemp = Nothing
      Exit Function
   
   Err_BackupSecureDatabase:
      Print #1, Time, "     ***ERROR: " & Err.Number, Err.Description, strTableName
      Resume Next
   
   End Function
   
   
   Function CopyTableDef(SourceTableDef As TableDef, TargetDB As Database, TargetName As String) As Integer
   Dim SI As DAO.Index, SF As DAO.Field, SP As DAO.Property
   Dim T As DAO.TableDef, I As DAO.Index, F As DAO.Field, P As DAO.Property
   Dim I1 As Integer, f1 As Integer, P1 As Integer
     
     If SourceTableDef.Attributes And dbAttachedODBC Or SourceTableDef.Attributes And dbAttachedTable Then
       CopyTableDef = False
       Exit Function
     End If
     Set T = TargetDB.CreateTableDef(TargetName)
     
     ' Copy Jet Properties.
       On Error Resume Next
       For P1 = 0 To T.Properties.Count - 1
         If T.Properties(P1).Name <> "Name" Then
           T.Properties(P1).Value = SourceTableDef.Properties(P1).Value
         End If
       Next P1
       On Error GoTo 0
       
     ' Copy Fields.
       For f1 = 0 To SourceTableDef.Fields.Count - 1
         Set SF = SourceTableDef.Fields(f1)
         
         ' DAO 3.0 and later versions. ****
         If (SF.Attributes And dbSystemField) = 0 Then   
           Set F = T.CreateField()
           ' Copy Jet Properties.
             On Error Resume Next
             For P1 = 0 To F.Properties.Count - 1
               F.Properties(P1).Value = SF.Properties(P1).Value
             Next P1
             On Error GoTo 0
           T.Fields.Append F
         End If ' Corresponding End If ****
       Next f1
       
     ' Copy Indexes.
       For I1 = 0 To SourceTableDef.Indexes.Count - 1
         Set SI = SourceTableDef.Indexes(I1)
         
         ' Foreign indexes are added by relationships.
         If Not SI.Foreign Then         
           Set I = T.CreateIndex()
           ' Copy Jet Properties.
             On Error Resume Next
             For P1 = 0 To I.Properties.Count - 1
               I.Properties(P1).Value = SI.Properties(P1).Value
             Next P1
             On Error GoTo 0
           ' Copy Fields.
             For f1 = 0 To SI.Fields.Count - 1
               Set F = T.CreateField(SI.Fields(f1).Name, T.Fields(SI.Fields(f1).Name).Type)
               I.Fields.Append F
             Next f1
           T.Indexes.Append I
         End If
       Next I1
   
   ' Append TableDef.
     TargetDB.TableDefs.Append T
     
     ' Copy Access/User Table Properties.
       For P1 = T.Properties.Count To SourceTableDef.Properties.Count - 1
         Set SP = SourceTableDef.Properties(P1)
         Set P = T.CreateProperty(SP.Name, SP.Type)
         P.Value = SP.Value
         T.Properties.Append P
       Next P1
       
     ' Copy Access/User Field Properties.
       For f1 = 0 To T.Fields.Count - 1
         Set SF = SourceTableDef.Fields(f1)
         Set F = T.Fields(f1)
         For P1 = F.Properties.Count To SF.Properties.Count - 1
           Set SP = SF.Properties(P1)
           Set P = F.CreateProperty(SP.Name, SP.Type)
           P.Value = SP.Value
           F.Properties.Append P
         Next P1
       Next f1
       
     ' Copy Access/User Index Properties.
       For I1 = 0 To T.Indexes.Count - 1
         Set SI = SourceTableDef.Indexes(T.Indexes(I1).Name)
         
         ' Do not copy foreign indexes. They are created by relationships.
         If Not SI.Foreign Then      
           Set I = T.Indexes(I1)
           For P1 = I.Properties.Count To SI.Properties.Count - 1
             Set SP = SI.Properties(P1)
             Set P = I.CreateProperty(SP.Name, SP.Type)
             P.Value = SP.Value
             I.Properties.Append P
           Next P1
         End If
       Next I1
     CopyTableDef = True
   End Function

   Note The code will try to import all tables from the corrupted database into a backup database. Replace the variables in the table after step 10 with your database file locations and your user settings.
9. Double-click BackupSecureDatabase.
10. To run the code, click Run Sub/UserForm on the Run menu.

Variable	Description
strSecurePathToDatabase	Location of secured database file
strSecurePathToWorkgroupFile	Location of workgroup file
strSecureUser	Secured user logon name
strSecurePwd	Secured user logon password
strTempPathToDatabase	Location of temporary database file
strBackupPathToDatabase	Location of backup database file
strLogPath	Location of log file