Solved

Error Msg:  Database is in an unexpected state; MS Access can't open it

Posted on 2002-05-22
29
1,914 Views
Last Modified: 2012-06-27
Hello.  I get the following error in Access 2000 on an NT machine:

"This database has been converted from a prior version of Microsoft Access by using the DAO CompactDataas method instead of the Convert Database command on hte Tools menu.  This has left the database in a partialy converted state...."

This database was, in actuality, converted from a 97 to 2000 using the Convert utlitity on the toolbar about a year ago.  It has been working fine every day since.  I was in a report and tring to Send To as an Email Attachment when Access hung.  I rebooted and now get this error everytime I try to get it.

It is on a network drive.  I ride a solution from PsychoDazey back on 6/22/2001 in which it was suggested:

Also-
Have you ran a repair on your database?
If that doesn't work you can download and run this JetComp file-
http://support.microsoft.com/support/kb/articles/Q273/9/56.asp
or, try /decompile and then recompile your code.

I did try the JetComp executable to no avail.

Anybody have any ideas?  This is a pretty important database.

Thanks.

Nnaxor
0
Comment
Question by:nnaxor
  • 8
  • 7
  • 4
  • +5
29 Comments
 
LVL 1

Accepted Solution

by:
n f earned 75 total points
ID: 7028478
Sounds corrupted to me.  You can try creating a new .mdb file and then importing all the objects from the corrupted one into the new one.  

If you have a lot of objects, don't try importing all the forms/reports/modules/queries all at once.  Try importing one or two at a time, then try doing a compile.  That way, if it's just one object that's irretrievably corrupted, you find the corrupted object and just recreate that one.  You'll still be able to import in all the other objects.  If you call global modules from your forms or reports, you may get a compile error when you're in the middle of this process because all of the code isn't in yet, but it's worth the hassle to find out if only 1 out of 30 objects is corrupted.

After you've brought in all the objects and successfully compiled and saved, do a Compact and Repair.

Good luck!

nfeldman
0
 
LVL 1

Expert Comment

by:Bondo
ID: 7028486
If it's on a network drive I would see of you can get a backup copy of the mdb file(pre error).  That would be the clean way to get you back in business.

Bondo
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 7029474
The circumstances seem strange to me. It sounds like it has started to go wrong after you tried to use some automation script. (Sending an E-Mail). Have you made sure that all the references are checked in your VB Editor. Can you post the script that sends the report.

Again, try compact and repair.

Thanks

Jell
0
 
LVL 15

Expert Comment

by:dbase118
ID: 7029791
There are some tools for recovering corrupted files. If nothing else works, it might offer a ray of hope. Let me get my links together.
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7029799
Hello and thanks.  But no to all.  I tried the importing into clean database, but when I choose Import on the File menu and choose that database I get the same error that the database has been converted and can't be opened etc.  I have not heard back from our sysadmin whether or not there is a back up.  I am highly doubting it at this point.  This is mission critical as this database holds all our company's data for the last three years.  Lesson learned, make my own back ups.  However, in the meantime does anybody have any more ideas?  I'm increasing points on successful conclusion.

Thanks to all.

nnaxor
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7029980
any chance that a commando Access developer with Office XP changed it to a 2002 format?
0
 
LVL 1

Expert Comment

by:n f
ID: 7030221
You might want to try the suggestions in this Knowlegebase Article: "ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database (Q306204)".  It's at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306204   and has a few suggestions as well as the names of some third-party vendors who deal with recovering corrupted databases.

0
 
LVL 1

Expert Comment

by:n f
ID: 7030243
Someone I work with pointed me to this thread on the Wrox site:  http://p2p.wrox.com/archive/access/2001-08/65.asp

In it, Joy Adkins suggests:
"Open Excel 2000 and import the tables from Access one at a time (Data/Get External Data). Open the latest backup version of your database. Import the data from Excel, replacing the existing tables so you'll have the
latest data changes. If you've made structural changes since the last backup or modified forms or reports, your changes will be lost, but at least you can salvage your
records."

If you don't have a backup, you may lose all your code, but at least you can try to save your data.

Good luck!
0
 
LVL 1

Expert Comment

by:n f
ID: 7030272
You might want to try the suggestions in this Knowlegebase Article: "ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database (Q306204)".  It's at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306204   and has a few suggestions as well as the names of some third-party vendors who deal with recovering corrupted databases.

0
 
LVL 1

Expert Comment

by:n f
ID: 7030275
sorry about the double-post.  I refreshed the screen and it reposted.
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7030277
nfeldman;

thats blatantly the craziest idea ive ever heard.

access doesn't lose data.

developers and users do.

access does occassionaly get corrupted-- but moving to the latest version (either 2000 with all the patches, or XP), using Source Safe, taking intraday archives of your development applications--

these are all good ways to combat this.

but moving the data to Excel--- are you a beancounter or something?

nobody has said this for a while-- but EXCEL IS THE ENEMY OF ACCESS.

anyone that develops in Excel should be forced to go take a few classes and develop applications that have true value--

develop application

and all you little weenies that say 'can you put this in excel so i can do such and such a calculation-- the answer should be 'no'.  if you want to do more calculations-- let me know and someone else will do them.

friggin beancounters are taknig over the world. excel geeks are ruining the efficiencies that the PC revolution has brought--

all because you aren't smart enough to do it in Access?

drop everything-- convince your beancounters to learn a _real_ analysis platform--

if i owned a company; i wouldn't install excel on peoples machines.. (just because it is such a molasses of time)
0
 
LVL 15

Expert Comment

by:dbase118
ID: 7030307
Notwithstanding the attack on Excel (which I have always felt could and should be used in conjunction with Access),

I submit this link to a free download of a recovery device It works sometimes for certain types of corruption. It is worth a shot
www.officerecovery.com

If that fail...PK Solutions (BIG MONEY)
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7030314
its not an attack on excel-- its an attack on freaky excel dorks that cant do access.

and they want you to 'publish something in excel'

so that they can redo all your work.

so that they can benefit from your work.

so that they can get all the credit.

the answer to excel is NO
0
 
LVL 1

Expert Comment

by:n f
ID: 7030323
aaronkempf -  I am not suggesting that nnaxor or anyone else try to develop database applications in Excel.  I am merely suggesting that nnaxor try to recover data from a corrupted .mdb file using Excel, since he can't get to it from Access.

Please read my post more carefully...and get a life.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:aaronkempf
ID: 7030334
use text not excel.

excel has limits like 64,000 rows.

and if you have less than that much data in access, you might as well just print it out and use a calculator rahter than bastardizing the integrity of this data by bringing it in excel...

i mean-- if you lost one piece of data by bringing your financials into excel-- its not worth it.

pretend just for a second that you lose all rows after 64,000...

what would you do?

what would it do it excel didnt warn you about this?

excel can change data-- excel can change formatting--

excel can ruin your life if you do something like this.

i beg-- use CSV, or text, not XLS.
0
 
LVL 15

Expert Comment

by:dbase118
ID: 7030449
All righty then...Any luck on the office recovery tool?
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7030618
plz verify that it is not in a 2002 format. this error 'unexpected format' is consistent with this type of problem.
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7030667
aaron, how can I confirm that?  It does not open?  No, I have not yet had a chance to try the officerecovery.  When I try to install it I get an error that it can't open a temp file.

Interesting to note, I made two copies of the corrupted database to play with (which, of course, they also are unable to open) and the original is 9998 kb in size, copy 2 is 9336 kb and copy 3 is 9260 kb.
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7030702
just try opening it with a different version of access.

i would assume that this is a problem with an older version of office trying to open a newer file format.

do you have a machine with Office XP installed in the office?

Although the default version (of file format) for Access 2002 is the Access 2000 format (at least i know it is for machines that upgrade to 2002)

it is easy to encounter these types of problems in a mixed environment.

Also-- i think that if you compact & repair while you do not have the db open-- it is a different kind of compact & repair.

Aka-- most people will open the dbs and then go Compact & Repair.  instead of this, you can open Access and (with no mdb open) go Tools Databases Utilities and then compact & repair.

i think that the best practices for avoiding this in the future:

1. Dont keep all your eggs in one basket--  be very firm in keeping forms and data and queries; etc in different databases.  IE -- Run the database splitter; and thus -- even if your VBA project becomes corrupted-- you cant lose the data.

2. Take an archive of whatever you are workign on in Access a couple times per day. I dont mean kick people out-- i mean to take a backup of the local/development MDB every few hours. Dont keep _any_ data in this MDB, and the size will almost never be a problem.  This means that you can always go back to an earlier version...

3. Use MDE on the frontend applications.  it is the only solution to corruption.
0
 
LVL 1

Expert Comment

by:n f
ID: 7030890
nnaxor:  

While I hope that aaronkempf's suggestion about trying to open the file with Access 2002 does solve your problem, I rather doubt that Access 2002 can open a corrupted .mdb any better than Access 2000 can.

If, as I suspect, you are one of the people who have encountered this error due to .mdb corruption completely unrelated to anyone trying to convert the database to a "higher" version (see
http://dbforums.com/archive/106/2002/02/3/300118 ), try one of the suggestions made by dbase118 or by me.

Best of luck.

nfeldman
0
 
LVL 1

Expert Comment

by:n f
ID: 7030909
nnaxor: There is another JetComp utility that MAY work where the first one you tried didn't.  You can download it at http://www.directorworkshop.de/datagrip/jetcomp4.exe
0
 
LVL 15

Expert Comment

by:dbase118
ID: 7031729
If you want to send me a copy, I will try to repair it with my access recovery tool. You will have to zip it if you can or My firewall may block it.
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7032179
Thank you all so much for you ideas.  None of them worked however.  We are starting over from scratch, lesson learned.
0
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7032378
nnaxor;

i was mainly saying that it may be a result of using the wrong version to open mdb... ie one of the commandoes there upgraded to 2002 and we're trying to open in 2000.

im sorry i wish that i could be a better help--

btw, no one has talked about the various JET Sp6 patches available-- is there any chance that you did something unordinary-- like installing the wrong patch on the wrong version of jet; etc?

i dont know how to diagnose this--

but i know that access 2000 has several bugs that are only fixed by installing the latest and greatest JetSP.

If I were you-- I would develop only Access Data Projects-- even if they take you 10x longer to build; they will be worth it-- as they will be in use 5 years from now..

(i know that the figures dont match up; but trust me the ROI works)
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7172515
I was just about to add my thoughts here on helping further when I noticed this question of yours in Community Support, and commented there for you and a Moderator.
http://www.experts-exchange.com/questions/Q_20326980.html
Asta
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7172946
Thanks, nnaxor, for requesting a point split... I have asked a Moderator to process this for you and (of course), exclude me from the split..":0) Asta
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7173167
Points reduced for a split.  You can now accept one experts comment as an answer.  After that, make another question in this topic area for the other experts.  Make the question for the amount of points intended.  Label the question, "Question for (Expert Name) and post this question number in the base of the question, i.e. For your assistance in question # --------


Computer101
E-E Moderator
0
 
LVL 1

Author Comment

by:nnaxor
ID: 7173295
Thanks much, splitting the points as resolved myself - but your idea(s) were great.

nnaxor
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7175714
":0)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now