Unexpected State. Microsoft Access can't open database
Posted: 17 Aug 01 (Edited 10 Jul 02)
When attempting to open a database, Access warns that the database was converted on opening instead of with the Convert Database utility (an incorrect diagnosis), and suggests reconverting the database or importing the tables and queries to a new database. Neither of these solutions work, as the database is locked and you cannot access any of the stored data to export it.
One workaround we discovered was to use Excel 2000 to import the tables from the corrupted database. We then opened the latest backup version of the database and replaced the old records in each table with the recoved data from Excel back into Access.
Any changes you made to the database structure, particularly modifications to the forms or reports, will be lost, but your data will be saved. If you have made changes to your table structure in the original database before the lock, you will need to repeat the changes in your backup database's tables prior to importing the data from Excel.
I have also received feedback that some users receive the message in Excel that they do not have permission to open the file, although they have permission for the given database. I did not experience this problem, but my database was not password protected.
To recover a table using Excel, open a new workbook and place your cursor in a blank cell. Choose Data/Get External Data/New Database Query from the toolbar. Choose MS Access datatbase as the source type, and click OK. Then browse to your corrupted database.
Choose one of the tables, then click the > button to get all the columns. Click Next a few times until you come to an option of Returning Data to Excel or veiwing the query in Query Wizard. Choose Return Data to Excel, Finish, and OK.
Now open your backup copy of the database. (You should make a backup of your backup before changing anything, just in case). Delete all the records in the table. Deleting the old records and pasting the new records into the old table preserves the structure and relationships.
You now have the option of either copying the records directly from Excel and pasting them in the old table, or importing the data from Excel as a new table in Access, then pasting between tables. The former is faster if you have a small number of records, but if you have many (I had over 15K), you'll need to do the latter.
To import the Excel data as a table, save the Excel workbook. From Access, choose File/Get External Data/Import. In Files of Type, choose MS Excel, then browse to your Excel file. Pick the correct worksheet and click Next. Be sure First Row Contains Column Headings is checked, and click Next. Choose to import the data as a new table. Don't do anything on field options and click Next. Choose No primary key and click next. Name the new table something and click finish.
Open the new table and copy all the records. Open the old table (where the records have been deleted) and select the first row. Paste. Verify that you want to paste the records.
That's one table down. You will need to go through the same tedious steps for each table, but you may save your data.
I was running a large query in an Access 2002 database this morning when I received an "out of disk space" error and the query stopped. I was then unable to open any of the database objects, and when I closed and restarted Access I got the following error message:
The database is in an unexpected state; Access can't open it.
Following that was some inaccurate text saying I'd converted the database from a previous version (I hadn't) and suggesting using the Convert Database command to restore the data. I tried it anyway but it didn't work. I tried creating a new, blank database and importing the data into it but got the same error message. I went to the MSKB and found Article Number 888634.
This is an unsecured database but when I tried the solution offered, nothing happened. I called my company's Help Desk and they had the same thing happen; the module doesn't do anything at all, even with an undamaged database file.
I've already slapped myself for not having backed up the database file, and my company will not pay for one of the recovery services (nor should they have to since I should have backed up the file). Has anyone ever run into this specific problem? If so, were you able to solve it?