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

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

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

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.

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

any chance that a commando Access developer with Office XP changed it to a 2002 format?

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.

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!

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.

sorry about the double-post.  I refreshed the screen and it reposted.

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)

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)

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

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.

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.

All righty then...Any luck on the office recovery tool?

plz verify that it is not in a 2002 format. this error 'unexpected format' is consistent with this type of problem.

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.

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.

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

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

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.

Thank you all so much for you ideas.  None of them worked however.  We are starting over from scratch, lesson learned.

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)

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.
https://www.experts-exchange.com/questions/20326980/Not-sure-what-to-do-here.html
Asta

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

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

Thanks much, splitting the points as resolved myself - but your idea(s) were great.

nnaxor

":0)