INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...I think the forum is a great idea, especially for those of us in consulting engineering. Keep up the good work!..."
Where in the world do Tek-Tips members come from?
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.
Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close