Can't open Visual Basic

Question

I have created a .accdb database which has been working fine for months.  It was working right up to close of play last night.  Today it is throwing up an error " 'Id' is not an index in this table".  It doesn't say what table and it doesn't say what to do about it.  After clicking OK many times I can get all tables to open and all queries, but I cannot open any forms or reports.  I can't even create any new forms.

I may be wrong but I have come to believe that the problem may be in a Visual Basic procedure which may somehow have got corrupted.  But here is the problem:  Visual Basic won't open.  If VB won'topen I can't debug the code, so where do I go from here? 

Help please!

Answer 1

Only the 7 tables I mentioned should have an index named "Id".  They should also have a field named "Id", on which the index is set.  If any of those 7 tables does not have an index named "Id", it is a result of corruption.

Do not under any circumstances try to create a new field in any of the system tables.  Do not, even as an attempt to repair corruption, try to create a new index on any system table that has all the indexes it's supposed to have.

In an attempt to repair corruption, working on a copy of the database, it is conceivable that you could repair the damage just by creating the "Id" index on one of those 7 tables, if it is missing.  In all of those 7 tables, the index name "Id" should be on the "Id" field, and should be the primary key of the table, with the following characteristics:

• Primary = Yes
• Unique = Yes
• Ignore Nulls = No

In all of these 7 tables except for MSysObjects, the Id field itself is an autonumber field; in MSysObjects, it is a Number/Long Integer field.

In my experience, there is usually small chance of repairing the corruption in this situation just by creating the missing index.  The absence of the primary key field may have allowed the creation of duplicate values, in MSysObjects in particular, preventing you from creating a unique index on that field.  Also, because the various system tables are related to each other by this Id field -- the Id (primary key) in one table being related to a foreign key field in another table -- the whole structure may be difficult, or even impossible, to sort out.

That said, there's no harm in seeing if you can create the necessary index, if it has been lost, so long as you are only working on a copy of the database.  If the index can't be created, Access will tell you so.

If you can't create the index, then your best bet is to send your data to a company that specializes in Access database repair and recovery.  I can recommend these, if they are still in business:

    Wayne Phillips

    Everything Access  http://www.everythingaccess.com/accessdatabaserepair.htm

    Peter Miller

    PK Solutions  http://www.pksolutions.com/t4.htm

It's been quite a while since I last checked them out, so I can't vouch for their current status.  Both are paid services, but will not charge you if they can't repair the database.

Answer 2

"I have not done anything yet becasue I don't know what the consequences might be."

I can tell you...The consequences will be disastrous. Unless you know what you are doing, don't do anything until someone like Dirk gets back to you

Answer 3

This is very interesting.  On looking at all the hiddent tables I find there are inconsistences.

Of the ones you mention all have an "Id" field but MSysAccessStorage has no Primary key and MSysObjects "Id" field data type is number, not autonumber.

I also have 6 other hidden tables which I will itemise in  a shorthand way which I hope you can decipher:

M SysACEs:  No Id;  No primary

MSysComplexColumns:  No Id but field called "Complex ID" is primary key

MSysQueries;  No Id but 3 primary fields - "Attribute"; "ObjectId"; "Order"

MSysRelationships;  No Id;  No primary

MSysNameMap:  Id;  No primary but "Id" field data type is autonumber

MSysNavPaneObjectIDs: Id;  No primary; data type is number, not autonumber

Finally, I have another Sys file which is not hidden:

MSysCompactError:  No Id;  No primary

This is a very complex picture.  I find in most cases I could not add a field or create a primary key but in the one unhidden table I could do both.  I have not done anything yet becasue I don't know what the consequences might be.