?
Solved

AOIndex is not an index in this table

Posted on 2009-02-19
7
Medium Priority
?
906 Views
Last Modified: 2012-05-06
I have tried the fix mentioned here:

http://allenbrowne.com/ser-47.html#AOIndexIsNotAnIndex

But the database in question has a simple password.  I can manage to access the database and remove the password. But then, when do the

Open the Immediate Window (Ctrl+G), and enter:
    FixBadAOIndex("C:\MyPath\MyFile.mdb")

I get an error 3049  Cannot optn database '.  it may not be a database that your application recognizes or the file may be corrupt.

When I debug that message it takes me to the line

Set tdf = dbBad.TableDefs("MSysAccessObjects")

in the fix code.

I'm wondering if I need to be able to run the part

FixBadAOIndex("C:\MyPath\MyFile.mdb")  and pass on the password THERE rather than trying to open the database and remove the password first.  

Can someone help me with this.
0
Comment
Question by:Mixmangle
  • 4
  • 3
7 Comments
 
LVL 2

Author Comment

by:Mixmangle
ID: 23686709
BTW. this is an Access 2003 database
0
 
LVL 58

Expert Comment

by:harfang
ID: 23687510
I'm not familiar with this technique, but I don't quite understand how you can get the symptoms you describe.

The message is about not being able to open the database, but the debugger stops at the line:

    Set tdf = dbBad.TableDefs("MSysAccessObjects")

Is that correct? Then the previous line, running a query on that very table just succeeded. That's what I don't get.

Perhaps we can backtrack and tackle the problem from the start. What is the problem with the database? Can you import objects from it to a new database?

(°v°)
0
 
LVL 2

Author Comment

by:Mixmangle
ID: 23687833
Good idea.  I got the AO problem and went from there.  I will start from the beginning.

My computer crashed.  I tried to use XP repair, but expanding the missing file did not work.

I reinstalled XP on a new Hard Drive and then Installed Office 2003 SP3 and also Access 97 in a separate folder (have done this before- no problem) a few old 97s db hanging around.

I was able to get the database on the original HD and I have been using it with no problem for a few days.

This morning, I started seeing erros in two tables.

If I open the original database again, I get an error saying Microsoft jet database engine stopped the process because you or another user are attempting to change the same data at the same time.

if I say OK to that error, the database opens and appears fine until I go into corrupted tables.  At that point I get an arror saying the database is not recognized.

I get the AO error by going into original (copy) and try to run Compact/repair.
 thats wwhen I get the AOIndex error and from that point on, I can't access the database!

I have been able to export tables from the original file . . .except for the two that seem corrupted.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:Mixmangle
ID: 23687860
http://support.microsoft.com/kb/828956

This time I installed in a different order. A jet problem????  I
0
 
LVL 58

Expert Comment

by:harfang
ID: 23687955
It's clearly corruption.

> This morning, I started seeing erros in two tables.

In Access 97, I used to copy-paste the retrievable data above and below the corrupted records to a new table, in a new database. Very often, compact and repair doesn't work when that happens (not often, luckily), so starting from an empty table was best. Then I deleted the entire table and reimported it from my new database.

Apparently, the attempted repair only worsened the problem. Can you revert to the version prior to that, and retrieve as much data as possible from the table?

When dealing with a corrupted database, you should concentrate on the data first. It's often easier to reconstruct a corrupted form from a backup that to figure out what editing took place since the last backup. So work first with the tables, and don't even try to open any interface object.

Once you have as much data as you could retrieve, switch to the other objects. At every stage, import (or copy-paste) to a new database. You will want to delete the buggy one entirely when you are done.

When importing interface objects, the process will stop at corrupted objects. Restart Access, restart the import, skipping over the first item that didn't get imported.

More about that if needed once you have dealt with the data.

Good luck!
(°v°)
0
 
LVL 2

Author Comment

by:Mixmangle
ID: 23688086
Actually, This database was created in Access 2003. There was no database upgrade involved, ever. I only mentioned the Access 97 because I did have Access installed from such upgrading on other databases.

I mentioned it because i thought maybe a Jet problem was causing the problem since it seemed too coincidental that it happened shortly after a reinstall.

When the drive crasjed the database in question was not opened and I was able to get it off AFTER the crash.  But I thought that the problem might be because I installed 2003 first and then Access 97.  So I just uninstalled both, rebooted, intalled access 97 and then Installed Office 2003, leaving 97 as is.  Opened a copy of the original database (untouched, never opned, etc . . .) and got the same error.

I Have been able to fo a print of one of the corrupt tables before it chokes. So at least I can re enter the data (yuk!).  When I open the corrupted table in a default datasheet it tries to open and does what looks like a "real slow draw" of the datasheet, and then errors start popping up on this line or that. Fortunately, if I send to printer before I wait until it craps out, I can get the data . . . . . . but I sure wish I knew what caused it!!!

I have been working with databases for a couple years and I successfully up[date from 97 to 2003/  split FEs and BEs and installed FEUpdate with local FE copies . . . . . . all of that.   But this hit me hard!  It remionds me like once when i found out someone was stealingh mail from my mailbox.  I just didn't want to trust my mailbox again. sort of the same thing now with Access!!!


0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 23688317
Yeah, these things are always bad for the morale...

Could it be that you opened the database before patching your Office installation? The symptoms could very well fit with an unpatched version of Jet. Else, the drive crash has left a bad sector right in the middle of an index. That would explain it also.

If you can print but not open the table, you can try a snapshot-type recordset (create a query based on the table, possibly even on a linked table from a read-only version of your bad data). If the table is read-only, it should be identical to report conditions. If it works, you can at least copy-paste some of your data instead of retyping it.

Sorry I can't be more help. If the workload to reconstruct the data is heavy enough, look for recovery services in any search engine. There are many that claim to specialize in corrupted databases; you could ask for a bid.

(°v°)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

839 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