Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Unexpected State. Microsoft Access can't open database

Error Messages

Unexpected State. Microsoft Access can't open database

by  JoyInOK  Posted    (Edited  )
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.
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top