Live Chat
The Database In An Unexpected State

The Database In An Unexpected State Error – Can’t Open it?

by

Computers are like complex machines that do many things at once. Sometimes, they make mistakes that we didn’t expect. It’s really annoying when you try to get information from a database, but you can’t because something went wrong. If you see the message “The database is in an Unexpected State” a lot, this whole blog post will help you figure out how to fix it.

This blog will discuss the detailed steps to resolve Access Database unexpected state Error 35012. It’s necessary to address this error promptly, as it is prone to corruption or disrupts the database. For users dealing with a corrupted or damaged database, a solution is available through Access Database Recovery, which enables database recovery. Without any further delay, let us get started.

Try For Free Buy Now

Why Does Error 35012 Encounter?

This issue can arise when you utilize the Data Access Object (DAO) library to convert a database. In fact, it was initially created in Microsoft Access 97 or an older version using the Compact database method. The result of the CompactDatabase method might leave the new database in a partially converted condition.

Methods to Resolve The Database in an Unexpected State

The following section will discuss three procedures to resolve the error message “The Database in an Unexpected State.” Each method is tested and verified by our testing team. Let’s dive into the various approaches to resolve the error that help to open and access the database.

Method 1: Use Convert Database

If you have the original database in its original form, use the Convert Database command. Here’s how:

For Access 2000, Access 2002, Access 2003

  1. Create a backup copy of the original database.
  2. After that, launch Access 2000 or a newer version.
  3. Go to the Tools menu, select Database Utilities, choose Convert Database, and click To Access 2000 File Format.
  4. In the Database to Convert From the dialog box, pick the database file name you wish to convert, and then click Convert.
  5. In the Convert Database Into dialog box, enter the new name for the database file, and click Save.

Access 2007

The Database in an Unexpected Error in Access 2007 can effortlessly resolve by following these steps:

  1. Create a backup copy of the original database.
  2. Attempt to open that database.
  3. When you try to open an Access 97 or Access 95 file format .mdb database, a dialog box called “Database Enhancement” will appear in Access. You’ll ask if you want to upgrade the database.
  4. Click “Yes” to upgrade the database to the file format you have chosen as the default in Access 2007. Once the conversion is complete, you can alter the database design within Access 2007.
  5. After the conversion, you won’t be able to open the database using an earlier version of Access than the one you converted it to.

Method 2: Retrieve both the database data and queries in a situation where the original unsecured database is not available

If you don’t have the original unprotected database in its original form and you’ve already tried basic troubleshooting. The following steps can resolve the issue of The Database in an Unexpected State. Try these steps to recover the database data and queries:

  • First, make a backup copy of the original database.
  • Launch Access 2000 or a later version.

For Access 2000, Access 2002, or Access 2003:

 – Click Blank Access database, enter the new database name in the File name box, and then click Create.

For Access 2007:

 – Click the Office button, then click New, choose Blank Database, and click Create to make a new blank database.

  • Next, for Access 2000, Access 2002, or Access 2003:

– Go to the Insert menu, click Module. It will launch the Microsoft Visual Basic Editor and create a new module.

For Access 2007:

– On the Create tab, click the downward arrow below Macro, and then select Module. It will open the Microsoft Visual Basic Editor and create a new module.

  • On the Tools menu, select References.
  • In the list of available references, find Microsoft DAO 3.6 Object Library and check the box to select it.

Note: DAO 3.6 is also available on Windows XP Home Edition.

  • Click OK to close the References dialog box.
  • Then, copy and paste the provided code into the new module 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
  • To run the code, click the Run Sub/UserForm tab on the Run menu.

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

If you are unable to open an Access Database and receive the error message, try this method to restore the database data.

  1. First, create a backup copy of the original database.
  2. Open the Access 2000 or a newer version of the Database on your local screen.
  3. For Access 2000, Access 2002, or Access 2003:

– Click Blank Access database, enter a new name in the File name box, and click Create.

For Access 2007:

– Click the Microsoft Office Button > New, choose Blank Database, and click Create to make a new blank database.

  1. Now, depending on your version:

For Access 2000, Access 2002, or Access 2003:

– Go to the Insert menu, click Module. It will open the Microsoft Visual Basic Editor and create a new module.

For Access 2007:

– On the Create tab, click the downward arrow below Macro, and then select Module. It will launch the Microsoft Visual Basic Editor and create a new module.

  1. From the Tools menu, choose References.
  2. In the list of available references, find Microsoft DAO 3.6 Object Library and check the box to select it.
  3. Click OK to close the References dialog box.
  4. Now, simply copy and paste the provided code into the new module, you created.

Option Compare Database
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
 Dim SourceRec As DAO.Recordset
 Dim DestRec As DAO.Recordset
 ' 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.
 DBEngine(0).CreateUser
 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.
             Set SourceRec = tdfSecure.OpenRecordset(dbOpenTable, dbReadOnly)
             Set DestRec = dbsTemp.OpenRecordset(strTableName)
             AppendRecordsFromOneRecordSetToAnother SourceRec, DestRec 
             SourceRec.Close
             DestRec.Close
        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
Function AppendRecordsFromOneRecordSetToAnother(SR As DAO.Recordset, DR As DAO.Recordset)
Dim x As Integer
Do While Not SR.EOF
DR.AddNew
 For x = 0 To SR.Fields.Count - 1
     DR(x).Value = SR(x).Value
 Next x
DR.Update
SR.MoveNext
Loop
End Function
  1. Choose the function called BackupSecureDatabase from the list. 
  2. To execute the code, click Run Sub/UserForm in the Run menu.

VariableDescription
strSecurePathToDatabaseLocation of secured database file
strSecurePathToWorkgroupFileLocation of workgroup file
strSecureUserSecured user logon name
strSecurePwdSecured user logon password
strTempPathToDatabaseLocation of temporary database file
strBackupPathToDatabaseLocation of backup database file
strLogPathLocation of log file

Sometimes, after the error “The database in an unexpected State” is resolved it cause the damage or corruption of some database files. For users dealing with a corrupted or damaged database, a solution is available through Cigati Access Database Recovery, which enables database recovery. 

Conclusion

As we conclude another informative blog, let’s take a moment to recap our discussion. Throughout this article, we addressed the “The Database in an Unexpected State” error. We delved into its causes and thoroughly explored three methods for resolving this problem. 

Leave a Comment