Error: 'id' is not an index in this table. Unable to open the VBA code editor.

[Axel van Moorsel]

I have a access 2016 application that has been working for several years. Today I have been changing some code and tables, and now I am unable to use the database. On opening I get the error: 'id' is not an index in this table. I get it three times, and then I only see my tables. I am unable to open the VBA code editor. I tried to compact and repair the database several times, but the problem remains. I tried to create a new (blank) database and import a table, but get the same error. I can open a table in design view and then change a field name. I cannot open any table. If I try to open a table I keep getting the error message, and I have to close the database through the Task Manager. I am working wit Windows 10 release 1803, and Access 2016.



On internet I found some references to converting from an old database (access 2003 or even access 97) but that is not the case here. As mentioned before, it has been working for several years.

Any idea how to open the database and find the cause? I can restore an old version, but that means several hours of work gone.

Any help is appreciated.

[June7]

Can try a decompile then recompile. http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp

But I think will have to go back to the good version and try again. Make changes incrementally and save after each change confirmed working.

[isladogs]

It sounds like corruption or you've been hit by a bug related to the 1803 update with access 2016 / 365.
Hopefully one of these will help you fix it:

1. Try DECOMPILING your database to remove any corrupted code. It only takes a few seconds. See http://www.fmsinc.com/microsoftacces.../decompile.asp
Make a backup first. Afterwards RECOMPILE from the Debug menu in the VBE
If you still can't use the VBE, try opening using the shift to bypass startup code

2. Whilst in the VBE, check for missing references

3. Read this link documenting major unresolved issues with the Win1803 update: https://www.devhut.net/2018/06/13/ac...gnized-format/

4. After you fix it, make sure you backup more regularly!

[Axel van Moorsel]

Hi guys,

Thanks for the suggestions. Most of them are good practice and is what I do already. The "Decompile" option was new to me, but unfortunately it didn't work. So I will use a previous version and do the work again.

Point is not that I don't want to use a previous version. I make backups regularly, and I have had to use them often enough. The frustrating point is that I am unable to open the VB editor. This morning I have tried several things, like deleting all tables and queries, and trying to export the form. Deleting tables and queries didn't help, and I was unable to export the form.

I noticed another post from 2013 about the same problem (Access 2010) with the same conclusion: VB code corrupt and unable to export the code.

Anyway, thanks again. I'll close the post.

[davegri]

Did you try creating a new empty DB and importing everything into it from the bad DB?

[isladogs]

One other possible course of action which may be helpful.
Repair errors in the system table MSysObjects.
See this item on my website http://www.mendipdatasystems.co.uk/r...cts/4594424204
Make a backup first if you try this

[Axel van Moorsel]

@davegri,
Yes, I have tried that and it didn't work.

@isladogs
I have looked in some of the system tables, but couldn't find anything strange. Didn't know of the fantom objects however, so thanks for the link. Unfortunately is my problem that I can't write any VBA code, since that is just my problem. If I could open the VBA editor, I could copy all of my code and test it in new DB's, and my problem would be solved. The tables I can export from the corrupt database, or copy them from a previous, good working version.

[isladogs]

OK in that case create a new blank database and import all tables and queries.
Make a backup just in case.
Next try and import the forms and reports. If successful, is the code imported as well?
If yes, repeat with macros and modules.

If the form import failed, go to your backup you made and import forms, reports, macros and modules from that.
Then you'll have the latest data and just need to redo recent changes to those items.
If you really do backups very regularly, that shouldn't take that long!

If even that fails, you can try the undocumented save as text then load from text options.
However I doubt you will be able to do that as its likely too late.

One last thing and I hope this doesn't come across as condescending.
You say you can't open the VBE but haven't actually described what does happen.
Just as a check, you haven't converted your database to an ACCDE by any chance?

[CJ_London]

from your description it sounds like your db is not split - this is probably the reason for the file becoming corrupted.

Here is another link with suggestions as to how you may be able to fix your db http://allenbrowne.com/ser-47.html. Your error is mention about half way down

[isladogs]

Ah yes I remember that issue now but have never had a chance to test the fix in MSysAccessObjects on a real world database

Axel:
Would you be willing to upload a zipped copy of your database so I can also try that fix.
If it contains confidential data in tables remove or amend that data but leave the rest intact