none
Access VBA to delete Indexes gets error 424 RRS feed

  • Question

  • My goal is a routine that will delete all of the indexes in this database.  It is all linked tables from a MYSQL database which has existed for many years.  At some point, with a refresh by the linked table manager, the system pulled in all of the indexes from the MYSQL database, and now a query that without indexes took 5 seconds to run, now takes about four hours.  This is what Microsoft calls query optimization.  The best that Microsoft's tech support can come up with is to delete the indexes in the Access database.  Due to the large number of tables and indexes, and a need to refresh these tables regularly, it is not practical to do this manually, hence the code.

    I hacked this a bit from a few things I found on line because I do not understand how to address system objects that well.  I even think I know conceptually what the problem is, but don't know what fixes it.  I want to cycle through all of the tables and all of the indexes, create a DROP INDEX Data definition Query for each, and execute that query.

    In the following code, as soon as I try to work with the objects, I get error 424. (   tname = tbl.Name )


    Sub DeleteIndexes()
        Dim tdf As TableDef
        Dim idx As Index
        Dim num_indexes As Long
        Dim SQLString As String
        Dim iname As String
        Dim tname As String
    On Error GoTo ErrorHandler

        For Each tdf In CurrentDb.TableDefs
            num_indexes = tdf.Indexes.Count
            If Left$(tdf.Name, 4) <> "MSys" Then
                If num_indexes > 0 Then
                Debug.Print num_indexes
                    For Each idx In tdf.Indexes
                   tname = tbl.Name  (this generates the error)
                    iname = idx.Name
                    SQLString = "Drop INDEX [" & idx.Name & "] ON " & tbl.Name & ";"
                    Set db = CurrentDb()
                    Set Q = db.QueryDefs("IndexDeleteQuery")
                    Q.SQL = SQLString
                    Q.Close
                    CurrentDb.Execute "IndexDeleteQuery", dbFailOnError
                    Debug.Print SQLString
                        
                    Next idx
                End If
             End If
        Next tdf

    ExitHere:
        Exit Sub

    ErrorHandler:
        Select Case Err.Number
        Case 3110
            'Could not read definitions; no read definitions '
            'permission for table or query '<Name>'. '
            Debug.Print "No read definitions permission for " _
                & tdf.Name
            num_indexes = 0
            Resume Next
        Case Else
            Debug.Print Err.Number & "-> " & Err.Description
            GoTo ExitHere
        End Select
        
        GoTo skipit

    dbFailOnError:
       
        MsgBox "The error is: " & Error(Err)
    Stop
       
    skipit:
    End Sub

    Thursday, January 12, 2017 8:12 PM

Answers

  • Hi Phul -

    This jumped out at me; it's only part of the answer, though - tbl instead of tdf...
    Shouldn't the line:

    tname=tbl.name

    be:

    tname=tdf.Name?

    I also see it in the line

    SQLString = "Drop INDEX [" & idx.Name & "] ON " & tbl.Name & ";"

    which should be:
    SQLString = "Drop INDEX [" & idx.Name & "] ON " & tdf.Name & ";"


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    Friday, January 13, 2017 4:24 AM
  • No, that actually was the answer.  Amazing what a second set of eyes can do when the first set is suiffering from lack of sleep.

    Thank you very much!

    Friday, January 13, 2017 6:28 PM

All replies

  • Hi Phil Smith of NHS,

    This forum mainly focus on Office client related question and feedback. Since your issue is more related to developing issues involving Access, I would move this thread into the following dedicated MSDN forum for

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.





    Friday, January 13, 2017 2:00 AM
  • Hi Phul -

    This jumped out at me; it's only part of the answer, though - tbl instead of tdf...
    Shouldn't the line:

    tname=tbl.name

    be:

    tname=tdf.Name?

    I also see it in the line

    SQLString = "Drop INDEX [" & idx.Name & "] ON " & tbl.Name & ";"

    which should be:
    SQLString = "Drop INDEX [" & idx.Name & "] ON " & tdf.Name & ";"


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    Friday, January 13, 2017 4:24 AM
  • Thank you.  I have bookmarked that forum for next time.  I thought there was another one more suited to the question, but couldn't seem to find it.
    Friday, January 13, 2017 6:27 PM
  • No, that actually was the answer.  Amazing what a second set of eyes can do when the first set is suiffering from lack of sleep.

    Thank you very much!

    Friday, January 13, 2017 6:28 PM
  • What was the answer?

    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

    Friday, January 13, 2017 6:35 PM
  • Likek I said, I do not understand quite how to address system objects.  I thought tbl.Name was a object convention.  I trued everything up and it runs perfectly, deleting all of the indexes. 

    Thank You

    Monday, January 16, 2017 6:01 PM
  • I thought tbl.Name was a object convention.


    In your code you declared an object variable tdf, of tabledef type.  As you loop through the current database's tabledefs collection a reference to each tabledef object is returned at each iteration of the loop by the tdf object variable.  A property of the tabledef object is the Name property, whose value is, not surprisingly, the name of the table.  The DDL statement needs to reference the table by name, so to concatenate the name of the table into the string expression you return the name property of the tabledef object with tdf.Name.  QED.

    Ken Sheridan, Stafford, England

    Monday, January 16, 2017 6:31 PM
  • Ah yes, Phil. It can be a more of an art when it comes to deciphering what is convention or property or method and what is the variable. Two things have helped me over the years:

    (1) Putting an "Option Explicit" statement at the top of every module and class that I write or even look at for testing or debugging; and

    (2) As I look at sample code, I first look at the Dim statements so I can distinguish between an object (or structure) variable and it's properties/methods.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    • Edited by MainSleuth Tuesday, January 17, 2017 1:42 PM
    Tuesday, January 17, 2017 1:39 PM