Acc2000: Database in unexpected state

[Marcel de Haan]

Greetings,

Some weeks ago we've converted an Access97 database to Access2000 and have
been working with it since. Last week a collegue of mine had the database
open when I also tried to open it. The following error occured:

"The database is in an unexpected state; Access can't open it. The database
is converted from a prior version by using the DAO compact database method
instead of the Convert Database command. This has left the database in a
partially converted state."

Since then I have not been able to open the database (I also tried with MS
Query via Excell) nor have I been able to repair it (not even with the
Database Repair Utility 4.0). Does anybody have a solution as how to repair
this database (or at least retrieve the information from the tables)?? I
have the standard version of Access installed (so without any service pack).
Is this part of the problem (in the MS knowledge base I saw a reference to
other database opening errors which would be solved in SR1)?

It would be rather silly (to put it mildy) if a simple opening action could
corrupt an entire database like this!!

Many thanks in advance,

Marcel de Haan.

[Frank Miller]

Hi Marcel,

My name is Frank Miller. Thank you for using the Microsoft Access
Newsgroups.

Simply opening the database file would not corrupt it unless the file was
being opened in another application such as Word, which would definitely
corrupt the database.

When opening a database in the Access user interface, if we receive the
error...

"The database is in an unexpected state. Microsoft Access can't open it.
This database has been converted from a prior version of MS Access by using
the DAO compact database method instead of the convert database command on
the tools menu (database utilities sub menu). This has left the database in
a partially converted state. If you have a copy of the database in its
original format, use the convert database command on the tools menu
(database utilities submenu) to convert it."

..we may not be able to convert the database. This message is usually due
to some corruption that has occurred in the database's project (forms,
reports, macros, modules).

To address this issue we should use standard corruption troubleshooting to
see if the problem can be corrected. However, more than likely, all
attempts to recover it will fail with the same error. In some cases, you
can recover the Jet database objects (tables and queries) by creating a new
database and using DAO code to bring the objects over. Even though you
cannot save the project items, at least the user may be able to recover
their data and queries. To use this technique, follow these steps:

1. Make a backup copy of the original database.

2. Start Microsoft Access.

3. Create a new, blank database.

4. Press ALT+F11 or on the Insert menu, click Module to launch the Visual
Basic Editor in a separate window, and will create a new module for you.

5. On the Tools menu, click References. This will display a References
dialog box.

6. Scroll down through the list, and check the box next to "Microsoft DAO
3.6 Object Library".

7. Click OK to close the References dialog box.

8. Copy and paste the following code into the new module that is open on
the screen:

Sub RecoverCorruptDB()
Dim dbCorrupt As DAO.Database
Dim dbCurrent As DAO.Database
Dim td As DAO.TableDef
Dim tdNew As DAO.TableDef
Dim fld As DAO.Field
Dim fldNew As DAO.Field
Dim ind As DAO.Index
Dim indNew As DAO.Index
Dim qd As DAO.QueryDef
Dim qdNew As DAO.QueryDef
Dim strDBPath As String
Dim strQry As String

'Replace the path below to the path of the corrupted database

strDBPath = "C:\My Documents\Appraisals.mdb"
On Error Resume Next
Set dbCurrent = CurrentDb
Set dbCorrupt = OpenDatabase(strDBPath)
For Each td In dbCorrupt.TableDefs
If Left(td.Name, 4) <> "MSys" Then
strQry = "SELECT * INTO [" & td.Name & "] FROM [" & td.Name &
"] IN '" & dbCorrupt.Name & "'"
dbCurrent.Execute strQry, dbFailOnError
dbCurrent.TableDefs.Refresh
Set tdNew = dbCurrent.TableDefs(td.Name)

'Recreate the indexes on the table

For Each ind In td.Indexes
Set indNew = tdNew.CreateIndex(ind.Name)
For Each fld In ind.Fields
Set fldNew = indNew.CreateField(fld.Name)
indNew.Fields.Append fldNew
Next
indNew.Primary = ind.Primary
indNew.Unique = ind.Unique
indNew.IgnoreNulls = ind.IgnoreNulls
tdNew.Indexes.Append indNew
tdNew.Indexes.Refresh
Next
End If
Next

'Recreate the queries

For Each qd In dbCorrupt.QueryDefs
If Left(qd.Name, 4) <> "~sq_" Then
Set qdNew = dbCurrent.CreateQueryDef(qd.Name, qd.SQL)
End If
Next
dbCorrupt.Close
Application.RefreshDatabaseWindow

MsgBox "Procedure Complete."
End Sub

This code should import all tables and queries from the damaged database
into the current database. Note that this solution does not take secured
databases into effect, so additional code to create workspaces, etc may be
needed if the database has been secured.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Regards, Frank Miller
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

[Lynn Trapp]

Have you tried Jetcomp.exe? I had the same problem and, after using Jetcomp
have had no further problems. You can download it at:

http://support.microsoft.com/support/kb/articles/Q273/9/56.ASP

--
Lynn Trapp
Programmer/Analyst
Wells Dairy, Inc.
rlt...@bluebunny.com

Marcel de Haan <m.de...@checkmark.nl> wrote in message
news:ts1febo...@news.demon.nl...

[Marcel de Haan]

Hello Lynn,

Thanks for your reply.

>Have you tried Jetcomp.exe? I had the same problem and,
after using Jetcomp
>have had no further problems.

Indeed I have tried Jetcomp. It compacted the database
allright, but the error messages kept appearing when I
opened the 'repaired' database.

I also tried decompiling the database with the /decompile
option, but this also didn't work. Somehow the database
got corrupted in such a way that no part of the file can
be accessed.

Greetings,

Marcel de Haan.

[Lynn Trapp]

The next think I would try, if I were you, is to attempt importing all the
objects from your corrupted database into a new one. If that fails, then you
should try the code that Frank Miller has posted.

--
Lynn Trapp
Programmer/Analyst
Wells Dairy, Inc.
rlt...@bluebunny.com

Marcel de Haan <m.de...@checkmark.nl> wrote in message

news:582e01c1503f$7c0504f0$37ef2ecf@TKMSFTNGXA13...

[Marcel de Haan]

Hello Frank,

Frank Miller <Frank-NoS...@microsoft.com> wrote

> Simply opening the database file would not corrupt it unless the file was
> being opened in another application such as Word, which would definitely
> corrupt the database.

I agree with you - at least in theory. I still don't know what happened to
the database.....

> [...] In some cases, you

> can recover the Jet database objects (tables and queries) by creating a
new
> database and using DAO code to bring the objects over. Even though you
> cannot save the project items, at least the user may be able to recover
> their data and queries. To use this technique, follow these steps:

...but your suggestion worked! Thanks very much! I was able to retreive all
but some tables from the corrupt database and copied them into an older
(production) version of the database. From the tables I couldn't retrieve,
only two actually contained user-data and luckily not very important
user-data.

Again, thank you,

Marcel de Haan.

[ccronin]

I've had the same thing happen to a database that I converted from Access
95. The database has been working well for the last year and a half, but it
has been completely corrupted three times in the last two weeks, and I've
gotten the "database is in an unexpected state" message each time.

Each time the database has been completely unrecoverable. I've had to
detroy the corrupted file and use a back-up copy of the database. I have no
idea why this happening.

"Marcel de Haan" <m.de...@checkmark.nl> wrote in message

news:tsc3sri...@news.demon.nl...