troubleshooting Question

Delete all indexes and primary key from ADOX index collection for specified table

Avatar of lbussey
lbussey asked on
Visual Basic Classic
5 Comments1 Solution430 ViewsLast Modified:
I have developed a few functions that take a source db and compares it to a destination db. If the destination db is missing any of the tables or columns in those tables from the source db then it appends them using ADOX. This is for somewhat simplistic db synchronization.

This works great, however, one thing it's missing is the synchronization of indexes and primary keys. My logic for attempting this is to create a function that deletes all the indexes from a specific table in the destination db. After this is recreates the indexes in the destination db based on the schema of the source db.

Below is my source for the function CheckIndexes(). This attempts to follow the logic in the previous paragraph.


'==============================================
' Function Name: CheckIndexes
' Input Vars: sTblName, adoSrc, adoDest
' Returned Type: Long
' Description: Synchronizes the indexes in the client table based on the master table definitions
' Last Modified: Feb/06
'==============================================
Public Function CheckIndexes(sTblName As String, adoSrc As ADODB.Connection, adoDest As ADODB.Connection) As Long

    Dim catSrc As New ADOX.Catalog
    Dim catDest As New ADOX.Catalog
    Dim tblSrc As ADOX.Table
    Dim tblDest As ADOX.Table
    Dim idxLoop As New ADOX.Index
    Dim keyLoop As New ADOX.key
    Dim i As Long
   
    'On Error Resume Next
   
    'Connect to the catalogs
    catSrc.ActiveConnection = adoSrc
    catDest.ActiveConnection = adoDest
   
    'Grab the tables defined by sTblName
    Set tblSrc = catSrc.Tables(sTblName)
    Set tblDest = catDest.Tables(sTblName)
   
    'Delete all the indexes and relationships in the destination table
    For Each idxLoop In tblDest.Indexes
        Debug.Print "Deleting idx " & idxLoop
        tblDest.Indexes.Delete idxLoop
    Next idxLoop
   
   'Append all indexes in destination table based on source table schema
    For Each idxLoop In tblSrc.Indexes
        Debug.Print "Appending idx " & idxLoop
        tblDest.Indexes.Append idxLoop
    Next idxLoop

    CheckIndexes = Err.Number

    'Clean up
    Set catSrc = Nothing
    Set catDest = Nothing
    Set tblSrc = Nothing
    Set tblDest = Nothing
    Set idxLoop = Nothing
    Set keyLoop = Nothing

End Function


The problem is that this function does NOT find any indexes when attempting to delete them. Then, when the For loop runs to append the new indexes it gives the following run-time error: 3367: Object is already in collection. Cannot append.

Please help.

lbussey
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros