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 ?
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:
- First of all make a backup copy of your original access database.
- Now start up your access 2000 or later version.
- 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.
- Hit the Office button, click New, click Blank Database, and then click Create to make a new database.
- 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:
- 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.
- From the Tools menu, tap to the References
- 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.
- To close the References dialog box, click OK.
- In the new module which you have created, paste the following code.
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
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.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
‘ 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)
MsgBox “Procedure Complete.”
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.
- 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
- Prepare a backup copy of original database
- Now start Access 2000 or a later version.
- 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
- 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.
- In the Convert Database Intodialog box, just assign new name to your database file, and then hit to the Save option.
- Keep one copy of the original database.
- After then try to open up your database.
- 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.
- 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.
- 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!
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.