Results 1 to 5 of 5
  1. #1
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30

    ParentID is not an index in this table error when opening the database

    Using an Access 2003 format database, opening in Access 2007



    When I try to open my database I get two errors and it will not open.

    <error>
    ID is not an index in this table
    ParentId is not an index in this table.
    </error>

    I get the error when it opens with autoexec and when I bypass autoexec. I have a master copy of the database that I tried to link to the first database to import tables but I still get the error.


    What might be causing this and how do I correct?

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    771
    Hi,

    could be a corruption of the Msysobject tables. You could try to write VBA code in a new database that connects to the corrupt database and removes and recreates the corrupt indexes.

  3. #3
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30
    I don't see my previous reply, so if your seeing this twice I apologize. Thanks for your response. This may be beyond my skillset. I would ask the question... If I can not link to the database using the normal link process, how would I link using VBA to bypass the index? I will do more research but if you can point me in the right direction, I would appreciate it.

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    771
    Hi,

    the following function will delete an index from the current database (fCurrentApp = true) or another database (fCurrentApp = false and strDatabase contains path + name of the other db)

    Code:
     
    Public Function DeleteIndex(fCurrentApp As Boolean, strTable As String, strIndexName As String, Optional strDatabase As String = "") As Integer
    On Error GoTo Err_DeleteIndex
    
    
    'strDatabase: path and name of the access database where you want to work - only used when fCurrentapp = false, when fCurrentapp = true set the parameter to ""
    'fCurrentApp: true if the index has to be deleted from the current app, false if it has to be deleted in another access database; in this case fill in strDatabase
    'strTable: name of the table where the index has to be deleted
    'strIndexname: name of the index
    
    
    
    
        Dim cat As New ADOX.Catalog
        Dim cnn As New ADODB.Connection
        Dim strConn As String
        Dim tdf As ADOX.Table, idx As ADOX.Index
        Dim intPos As Integer, intPos2 As Integer
        
        
        If fCurrentApp Then 'build the index in the current database
            Set cnn = CurrentProject.Connection
        Else 'build the index in another access file
            'build the connection string by getting the current connection string and replace the database path + name with the given path and name in strDatabase
            'this way the function works for different versions of Access
            strConn = CurrentProject.Connection.ConnectionString
            intPos = InStr(strConn, "Data Source=")
            intPos2 = InStr(intPos, strConn, ";")
            strConn = Left(strConn, intPos - 1) & "Data Source=" & strDatabase & Right(strConn, Len(strConn) - intPos2 + 1)
            'open the connection
            cnn.ConnectionString = strConn
            cnn.Open
        End If
        Set cat.ActiveConnection = cnn
        For Each tdf In cat.Tables
            'Debug.Print tdf.Name
            If tdf.Name = strTable Then
                
                For Each idx In tdf.Indexes
                    If idx.Name = strIndexName Then tdf.Indexes.Delete idx.Name
                Next idx
            End If
        Next tdf
        cnn.Close
        DeleteIndex = 1
    
    
    Exit_DeleteIndex:
        Set cnn = Nothing
        Set idx = Nothing
        Exit Function
        
    Err_DeleteIndex:
        DeleteIndex = -1
        Debug.Print Err.Number & ": " & Err.Description
        Resume Exit_DeleteIndex
    
    
    End Function
    Beware if you use the code that the references "Microsoft ADO Ext. x.y for DDL and Security" and "Microsoft ActiveX Data Objects x.y Library" are checked in the VBA -> Tools -> References window.

    Succes
    NG

  5. #5
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30
    Wow! I was only expecting a reference on how to write the code but I greatly appreciate this module. For expediency, I had to restored the database from a tape backup. However, this is not the first time I've seen this issue. I kept a backup of the broken database and I am going to dissect this code, learn how it works and see if I can get this to work for future use. Once again, many thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-03-2014, 06:38 PM
  2. Replies: 7
    Last Post: 03-08-2013, 02:13 AM
  3. Replies: 2
    Last Post: 02-13-2013, 04:09 PM
  4. error message when opening the database
    By dollygg in forum Access
    Replies: 1
    Last Post: 10-04-2010, 08:34 PM
  5. Replies: 1
    Last Post: 10-15-2008, 01:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums