'AOIndex' is not an index in this table (1 Viewer)

[Stefan.Kauw]

Hi Guys,

Anyone know how to fix the " 'AOIndex' is not an index in this table" error?
This stops all the work on the database.

Fortunately enough i split the GUI and physical database. The problem is in the GUI. Not sure why it occurs since i was only doing simple vba programming without touching the database.

I've tried to fix it with "FixBadAOIndex" but this only accepts MDB where my file is ACCDB.

Filesize is to big to upload :banghead:

 

[arnelgp]

compact and repair the BE.
relink the FE. compact and repair also.

 

[Stefan.Kauw]

compact and repair the BE.
relink the FE. compact and repair also.

I'm sorry, what do you mean by BE and FE?

 

[arnelgp]

FE front end, the gui. BE, back end.

 

[Stefan.Kauw]

FE front end, the gui. BE, back end.

I'm feeling dumb.... can't seem to achieve that.
ACCESS needs me to open the file. I dont see the info tab that the manual is refering to. Using the windows CMD to say /compact to the database doesn't work either. It just tries to open the database.

 

[CJ_London]

depends on the version you are using, but open the BE, select the Database Tools tab, you should see the compact option on the left of the ribbon. Or click on the File option and you will see it there as well

Either way, you need to open the file

 

[isladogs]

I'm feeling dumb.... can't seem to achieve that.
ACCESS needs me to open the file. I dont see the info tab that the manual is refering to. Using the windows CMD to say /compact to the database doesn't work either. It just tries to open the database.

Which file can't you open directly? FE or BE? What happens when you try?
It is possible to compact an external database from another database
i.e. You can compact the FE from the BE or vice versa.

You can also decompile the FE to remove any corrupt compile code if needed

 

[Stefan.Kauw]

Which file can't you open directly? FE or BE? What happens when you try?
It is possible to compact an external database from another database
i.e. You can compact the FE from the BE or vice versa.

You can also decompile the FE to remove any corrupt compile code if needed

The front end won't open correctly.
I'll try compacting from BE this afternoon.

Thanks for the advice so far.

 

[The_Doc_Man]

Just as a precaution, since you are having trouble opening the file: Always make a copy to another folder for safe-keeping BEFORE you start recovery efforts. That way, if things go from bad to worse, you have that backup copy in a safe place. If recovery goes out on you, there is always the method of copying the copy to return you to where you were when you started. Then you can try a different method of recovery.

 

[Stefan.Kauw]

Just as a precaution, since you are having trouble opening the file: Always make a copy to another folder for safe-keeping BEFORE you start recovery efforts. That way, if things go from bad to worse, you have that backup copy in a safe place. If recovery goes out on you, there is always the method of copying the copy to return you to where you were when you started. Then you can try a different method of recovery.

Unfortunately nothing works. I made a back-up already of the faulty file.

Anyone interested in trying to fix it? I'm out of ideas.

 

[The_Doc_Man]

Did a search of the great Google brain, found these articles:

http://www.utteraccess.com/forum/index.php?showtopic=507360

https://www.pcreview.co.uk/threads/...-index-is-not-an-index-in-this-table.2763453/

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

For the Allen Browne reference, the AO Index is not the top of the list of things he shows, so you have to scroll down a bit to get to it. However, you referenced using the FixBadAOIndex subroutine, so obviously you have found this article. It is still a good reference. I'm not sure why it won't work for an .ACCDB because I have never had to actually use this code. (Not that I don't get corrupt DBs, but I have never gotten that particular error.)

 

[Stefan.Kauw]

I've uploaded a screenshot of the error.
The direct translation is: "ID Is not a index in this table".

Meanwhile i'll search through the links you gave.
Thanks a lot

 

[The_Doc_Man]

That error is interesting because that error says that a table of that name exists. (You would get a different error number if it didn't.)

The only .ACCDB databases I have on which to work at the moment do not have MSysAccessObjects in them (though they have other system tables such as MSysAccessXML) so I can't examine that particular table in an .ACCDB file to test for you. Perhaps another forum member who has such a table available to look at the design could offer comments on its structure.

I have an old .MDB file that DOES have MSysAccessObjects and there are only two fields in it - Data and ID, so that explains what Allen Browne's code is doing. But I cannot tell and do not know if something changed for that table when Access changed from JET (for .MDB) vs. ACE (for .ACCDB).

 

[The_Doc_Man]

Looking more closely at Allen's code, your error is intriguing because I don't see how the code would cause that error. If [ID] exists at all in that table, the DELETE query would not try to treat it as an index-name. It is CLEARLY in the context of a field name.

That error specifically says the INDEX doesn't exist but implies that the field DOES. (Again, a different error would occur if the field didn't exist.) I would double-check that when you copied Allen's code, you copied it correctly.

Also, given what Allen's code tries to do, you might be able to just open the table (DEFINITELY ON A COPY) and manually look for any rows that contain a Null in either the [DATA] field or the [ID] field.

While it might not be a super-short table, it doesn't seem to be that complex. You would have to make the System objects not hidden to do this, but that is easy enough. Right-click on the top of the Navigation panel to get the options.

The MSysAccessObjects table on .MDB files is two columns, with as many rows as needed for the objects you have. I'm GUESSING that for .ACCDB files, the same structure is used. Manually delete those rows that appear to have Nulls in either field. Then close it and try Allen's code again.

If you have another .ACCDB file and can make a COPY of it for the examination, you might also see if you can examine that MSysAccessObjects table in your other DB. If it still has only two fields, that's good. You can also examine the indexes on the table to see if it really still uses AOIndex as the name of the index for the ID field. Since I don't have one of those tables in my extant .ACCDB files, I can't tell you.

The fact that I don't have one of the tables tells me it is a "situational" table that Access would create if it thought it needed it and would not create it if it didn't. I don't know what situation triggers its creation, so can't take that much further.

 

[isladogs]

I understood this to be an ACCDB file
So I'm somewhat confused with mention of MDB files and the system table MSysAccessObjects (which was last used in Access 2000!).

The similarly named MSysObjects table does of course still exist but I'm not sure how or if that fits into this discussion

Perhaps it's best if you upload both FE and BE files after removing most of the data and compacting. However, is the FE mainly in Dutch?

 

[The_Doc_Man]

@Colin,

I was unsure of whether the table in question is merely obsolete and hidden (or more than hidden, considering a thread not so long ago about really obscured objects) or whether it never exists in an ACCDB file. But my question is two-fold.

1. Why would the table still exist in an .ACCDB if it has "fallen off the grid"? Because Stefan's error says that table exists but is damaged. Otherwise, how would Access even know about it? Why would it CARE?

2. Why does Stefan get the error he gets and NOT either "No such table" or "No such field"? The error he gets strongly suggests that the table is still there but structurally not quite right.

@Stefan,

I now need to ask other questions as follow-up to your comment in your lead-off post:

The problem is in the GUI. Not sure why it occurs since i was only doing simple vba programming without touching the database.

WHAT were you doing in VBA? Is there a chance that this started when you tried to upgrade an existing .MDB front-end in-place? Or better yet, when did this problem first pop up? We need some history.

If Colin says MSysAccessObjects is no longer in use, I can understand that. And I begin to recall what those objects really were. They were the original "collections" that were the heart of the database: The Tables, Queries, Forms, Reports, Macros, and Modules collections.

When I looked at my old .MDB files again, I saw the ID values of the objects and they correspond to the "big 6" collections - the first things created in an empty database hence numbered objects 1 through 6 - which of course are no longer in use AS SUCH in .ACCDB files. (They exist now, but differently than they used to exist.)

So if you have an .ACCDB with that table in it, the question becomes "How did it get there?" An "upgrade-MDB-to-ACCDB-in-place" is one possible answer. I'm also wondering WHY the ACE engine would even CARE that such things existed since that table isn't used any more, or at least no longer has the same meaning.

 

[isladogs]

Hi Doc
No - MSysAccessObjects is not a deep hidden table - it doesn't exist in versions later than 2000.

I confirmed this by saving an Access 97 MDB file as A2000 then as A2002.
The MSysAccessObjects table is removed in the 2002 version.
My guess is that the data it contained is transferred to MSysAccessStorage which is in v2002 onwards

I expect the FixBadAOIndex procedure will have done nothing at all in this case as its designed to fix that issue in MSysAccessObjects

If you download my SystemTableInfo app from this thread, you will get a full list of all 35 system tables that I've found so far together with what I know about each table

You may find this link useful as well:
https://www.insomniasec.com/downloads/publications/Access-Through-Access.pdf

 

[The_Doc_Man]

Colin, I expect that the table is at least obsolete, but that still begs the question: How did it get referenced in order to determine that the AOIndex was hosed? Which is why I asked Stefan what he was doing with his VBA.

I think we can agree that the table really isn't necessary in an .ACCDB file, but the errors say it was there and was referenced in a way that detected a structural/content error. How did that happen?

 

[isladogs]

No idea - need to see the database to understand & fix it ... hopefully.

I don't believe the OP has ever stated it was originally an Access 97 or 2000 file.
If it was then the data related to the AOIndex issue (which I don't fully understand) must now exist in another table such as MSAccessStorage.

If it is that table, BINGO ...as it can be edited using a query

Of course, the usual caveats apply about editing system tables - only do so on a backup & only if you know what you are doing.
However if the database is unusable, as in this case, its worth the risk ...if no other solution exists