Attention: The forums will be placed on read only mode this Saturday (Oct. 20, 2018)

During this outage (6:30 AM to 8 PM PDT) the forums will be placed on read only mode. We apologize for this inconvenience. Click here to read details

Office & Productivity Software forum

General discussion

Severely corrupted Access 2002 database

by Josh K / February 27, 2006 2:03 AM PST

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?

Discussion is locked
You are posting a reply to: Severely corrupted Access 2002 database
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Severely corrupted Access 2002 database
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
I found this
by Rick S / February 27, 2006 2:27 AM PST

Unexpected State. Microsoft Access can't open database
faq181-1005
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.

Collapse -
Fortunately....
by Josh K / February 27, 2006 3:42 AM PST
In reply to: I found this

.....the tables were mostly linked tables, so the data itself was never in any danger of being lost. I've just had to start rebuilding all of my queries, reports, etc.

Collapse -
Sounds bad.
by R. Proffitt Forum moderator / February 27, 2006 2:38 AM PST

Years ago I jumped off that wagon to MySQL. It's one sweet ride and the price is right.

-> Sorry to read about your loss and that recovery is not working. By your company not paying for recovery software, tools or service they have told you how much the database is worth.

Time to go to lunch?

Bob

Collapse -
Had a nice sandwich
by Josh K / February 27, 2006 3:31 AM PST
In reply to: Sounds bad.

Fortunately the database tables were mostly linked tables, and the underlying SQL database is fine. It's all of my queries, reports and macros that I'm busy rebuilding.

I guess it could be a lot worse.

Collapse -
Such bruises remind us of something.
by R. Proffitt Forum moderator / February 27, 2006 3:42 AM PST
In reply to: Had a nice sandwich

Bob...

PS. _____ is cheap... Insert favorite word or person of the day.

Collapse -
Re: corrupted database
by Kees Bakker / February 27, 2006 3:05 AM PST

Josh,

A very sad story. Once again, it's proven that in a company environment all data should be on a server, and there should be a routine dayly scheduled backup. You can't trust busy and stressed employees to do it with the necessary discipline if it's on their local PC. That's basic system management, and a company big enough to have a help desk should know it.
And it doesn't harm at all, speaking of reliability, to have the data in SQL Server or MySQL or any real database other than MS Jet. Even having the Access application seperated in a 'data' database and a 'program' database might have averted this desaster.

Companies who don't do all of that have to pay for data recovery sometimes. Or accept the cost of reentering the data. But I wonder if standard data recovery will do the job here. You might need to hire a Access insider from Microsoft itself. It's easy, in principle, to choose the cheapest solution.

Also note that the MS coding (if it should work) only recovers the data (at most, reading the code I get the strong impression it only recovers the data definition) and the query definitions, and doesn't recover forms, reports, macro's and modules.

All I can suggest: go into debug mode, and do a step by step execution of the code of #2 to see where it fails. But also note that the coding is meant strictly for cases where the conversion from Access 97 to Access 2000 failed. There's no reason at all to believe it should work in all possible cases of corruption.
It's strange that a query failing on a lack of disk space should corrupt the database if it's a read-only (select) query, by the way. I've always thought that select queries don't change the database and only use RAM and virtual memory. But apparently I'm wrong here.

Kees

Collapse -
You weren't wrong
by Josh K / February 27, 2006 5:03 AM PST
In reply to: Re: corrupted database

It was an update query, but there is tons of space on the server and on my local drive.

I'll be backing up regularly from now on.

Collapse -
Beware some limits.
by R. Proffitt Forum moderator / February 27, 2006 5:08 AM PST
In reply to: You weren't wrong

From memory Access can toss it's owners for a ride at 2GB or so in a table. What we forget is that when all this started, that was a large disk.

Bob

Collapse -
Access Limits? Nah.
by richardp123 / February 27, 2006 7:26 AM PST
In reply to: Beware some limits.

Sorry to rain on the Access lambasting parade, Bob - but as a major fan of Access, for all its faults, it does deserve a little defence.

As an integrated desktop database application product, Access is incomparable to MySQL - each are ultimately targeted at two different markets.

Regardless, anyone getting anywhere near 2Gb in an Access database would very likely be completely misusing the product, and clearly would be best to upsize to a professional RDBMS best suited to that sort of data volume.

Having used Access productively since version 1 in a variety of different tactical enterprise projects, I don't ever recall the need to store as much data as that.

I really can't imagine just how badly a database/table would have to be structured, to ever place the user in a 2Gb table constraint situation. It's either completely unrealistic, or a reflection on the incompetence of the database developer.

Richard

Collapse -
Richard, meet Jake.
by R. Proffitt Forum moderator / February 27, 2006 9:24 AM PST
In reply to: Access Limits? Nah.

Jake did that to a few people and helped me land a few MySQL migrations.

Gotta love those Jakes.

Cheers,

Bob

Collapse -
Is this the BBB theory being put into practice?
by richardp123 / February 27, 2006 6:51 PM PST
In reply to: Richard, meet Jake.

Care to elaborate on the specifics/particulars, data volumes, etc of "Jake"s storage specifications, that necessitated a migration from Access to MySQL, even for entertainment purposes?

Collapse -
In short...
by R. Proffitt Forum moderator / February 27, 2006 11:18 PM PST

They loaded up phonebooks into Access. You can imagine how big such are...

Hope this answers your question.

Bob

Collapse -
Subjective
by richardp123 / February 28, 2006 12:53 AM PST
In reply to: In short...

Sorry Bob not really - slightly too ambiguous.

If you're talking about corporate phonebooks, can't see any data volume worries there, sounds relatively minor.

If you're referring to creating offline replicas of entire phone book for countries, then it'd be a toss-up as to who sounds more stupid - the guy who came up with the concept of doing it in the first place or the one who suggested it should be upscaled to a larger platform.

Collapse -
Try ... country size.
by R. Proffitt Forum moderator / February 28, 2006 1:01 AM PST
In reply to: Subjective

Sorry, but I try to keep replies short. It's been nice that there are more than a few Jake's out there.

Another upside of MySQL is now we can use Open Office and MyODBC to connect it all up without budget worries.

Bob

Popular Forums

icon
Computer Newbies 10,686 discussions
icon
Computer Help 54,365 discussions
icon
Laptops 21,181 discussions
icon
Networking & Wireless 16,313 discussions
icon
Phones 17,137 discussions
icon
Security 31,287 discussions
icon
TVs & Home Theaters 22,101 discussions
icon
Windows 7 8,164 discussions
icon
Windows 10 2,657 discussions

FALL TV PREMIERES

Your favorite shows are back!

Don’t miss your dramas, sitcoms and reality shows. Find out when and where they’re airing!