'ID' not a valid index in this table <error 3800>

[Demerit]

I have an access 2010 database application which i have been working on for 1 month now.
I have 2 days to supply to my client.

Some hours ago i opened the application and it keeps telling me 'id' is not an index in this table.


It's a split application. The backend has no problem but the front end seems corrupt. All the objects dont work
(forms, queries, reports, modules)

I have tried:
- compact and repair to no avail.
-Created another access file to import but it keeps saying <error 3800> 'id' is not an index in this table, and stops the importing
-I have tried exporting the objects one after another to another access file but to no avail
-I have tried to delete some entries in "MsysObjects" and to rebuild but this tells me "No delete permissions on MsysObjects"

I am totally confused. The last backup of this application is really old and there is no time left for me to do all i have already done.
Any suggestions please.

[aytee111]

Here are some things to try: http://www.allenbrowne.com/ser-47.html

[Demerit]

Here are some things to try: http://www.allenbrowne.com/ser-47.html

I have toiled through the proposals there but none works.

[CJ_London]

perhaps check the indexes in 'this table'. Also ID is not a good name for a field. What is the backend? if linked to sql server, perhaps you have specified a field as the index and it is not indexed?

try recreating 'this table'

this statement

Created another access file to import but it keeps saying <error 3800> 'id' is not an index in this table, and stops the importing

does not make sense - front ends do not have tables, and better to create new linked tables rather than importing existing ones

[Demerit]

does not make sense - front ends do not have tables, and better to create new linked tables rather than importing existing ones

From my research, its a problem with an access system table. This makes it impossible to open forms, reports, modules etc.
I am not trying to import or export tables. I want to export forms, reports, modules but it does not permit.
But if i had a way to repair the current access front end file then i will not need to export anything.

Basically i need a tool i can use to do a repair a corrupted access file.

I am using MySQL and Access as database. All the tables in the backend file are all safe and fine.

[CJ_London]

if you have identified which form/report/module is causing the problem, create a new db and import all but that one object then recreate that one object. Do not try to export to a new db.

if you can't identify which object then the only thing to do is create a new db and import one object at a time.

If you have a corrupted db there is little you can do to repair it - although if you have not tried it yet, try decompiling the db to get rid of all the rubbish - easiest way to decompile is to create a shortcut to ms access then add the /decompile switch, so your shortcut target becomes something like

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

(will vary depending on access version/bit)

Open access with this shortcut then open the problem file (ideally with any start form/autoexec macro disabled). As soon as you open, compact and repair, then recompile again.

Also, I presume your db is .accdb, you cannot export objects from a .accde

[Demerit]

I have attached a copy of the the front end application. When you open it, you will see the error it displays. I even deleted all the link tables from it but nothing changed. I tried the method mentioned above but it did not work.

Its based on Access 2010


cgpap1.zip

[CJ_London]

hmm think you have big problems.

When I tried to open it I get this error


and if I click on the help function I get
This error can appear if you try to convert an Access 97 database to a more recent Access database. If the database was not compacted prior to performing this operation it is highly recommended that you compact it as soon as possible.
If the database is corrupt or has many references to entries that are marked as deleted but have not yet been removed from the database, the database will usually not convert properly. Performing a compact and repair ensures that the database is free of defects that could negatively affect the conversion process.

To compact and repair the database, click the File tab, and then click Compact and repair this database.

If necessary, you can import the data from the Access 97 database into a new database. To do so, create and open a new database. Then, on the External Data tab, in the Get External Data group, click Access Database. In the Get External Data dialog box, browse to the Access 97 database that has the data that you want to import, and select the option Import tables, queries, forms, reports, macros, and modules into the current database.

I don't know whether you have been converting from 97, but your ODBC linked tables have ID as the primary key (at least in those I checked) which is bad practice - the name should be more meaningful (e.g. ActivityID) and it may be that access has been trying to assign the same index to many tables. The error message 'id' is not an index in this table.' appears three times before the db opens which supports this theory.

There is also a compact error table you should check out. The error being

'You tried to assign the Null value to a variable that is not a Variant data type.'

Queries appear to be accessible in that the can be opened in design view but I suspect the db is not recoverable and I would go back to recent backup. But I would also rename all your ID fields before continuing.

Sorry I can't be more help - good luck!

[aytee111]

Sadly it is the VBA that has got corrupted. In messing around I have been able to import your local tables, the queries, and forms that don't have VBA, but that is all.

[aytee111]

I guess you want to know how I did it!

The following is the code;
(1) queries - I print the SQL as the queries whose names being with "~" come from VBA (so I have heard) so by printing the sql you can recreate the query manually
(2) tables work
(3) forms without VBA
(4) modules - no luck

Public Function aaa()

Dim appAccess As Access.Application, qdf As QueryDef, tdf As TableDef, obj As AccessObject, doc As DAO.Document
Dim db As Database
Dim tdefs As TableDefs, tdef As TableDef
Set db = DBEngine.OpenDatabase("c:\users\downloads\cgpap1.accdb")

For Each qdf In db.QueryDefs
    DoCmd.TransferDatabase acImport, "Microsoft Access", db.Name, acQuery, qdf.Name, qdf.Name
    Debug.Print qdf.Name & " " & qdf.SQL
Next

'For Each tdf In db.TableDefs
'    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And tdf.Connect = "" Then
'        Debug.Print tdf.Name
'        DoCmd.TransferDatabase acImport, "Microsoft Access", db.Name, acTable, tdf.Name, tdf.Name
'    End If
'Next

'For Each doc In db.Containers("Forms").Documents
'    Debug.Print doc.Name
'    SaveAsText acForm, doc.Name, "c:\users\taylor\downloads\" & doc.Name & "txt"
'    'DoCmd.TransferDatabase acImport, "Microsoft Access", db.Name, acForm, doc.Name, doc.Name
'Next

'For Each doc In db.Containers("Modules").Documents
'    Debug.Print doc.Name
'    DoCmd.TransferDatabase acImport, "Microsoft Access", db.Name, acModule, doc.Name, doc.Name
'Next

End Function