Results 1 to 4 of 4
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Removing and creating Indexes in VBA

    I am looking to drop all indexes in a table, then prioritize some new ones.



    How would I delete all, do I have to name all of them, or can I use a special character?
    Code:
    DoCmd.RunSQL "DROP INDEX  Table1 ;" ' Remove indexes from Fields
    DoCmd.RunSQL "CREATE INDEX  Table1 [unique_id] Ascending, [equipment_item_id] Ascending, [platform_id] Ascending, [Role / FE / Node ID] Ascending;" ' Adds Index to Fields
    Below I want to remove the red boxed ones and reprioritize the remaining, or remove all and make a new one prioritized in sequence as in the 2 line.

    Thanks
    Attached Thumbnails Attached Thumbnails Indexes.JPG  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Edit the table in design mode,
    Remove index,
    save,
    add index,
    save.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I have the indexes removed by:
    Code:
        DoCmd.RunSQL "DROP INDEX [Arch CAT ID] on Table1;"              ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [Bumper # / Plat ID] on Table1;"       ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [PARA ONODE ID] on Table1;"            ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [Parent Node ID] on Table1;"           ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [parent_equipment_item_id] on Table1;" ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [Proponent Code] on Table1;"           ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [TOE ONODE ID] on Table1;"             ' Removes index from Field
        DoCmd.RunSQL "DROP INDEX [Unit ID] on Table1;"                  ' Removes index from Field
    I don't need to drop any. Is there a way to combine the rows?

    Thanks

    It looks like I need to set the sort on the import. More to come.

  4. #4
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Figured the indexing out. Thanks

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

Similar Threads

  1. Table Indexes
    By Joetoben in forum Queries
    Replies: 1
    Last Post: 05-21-2015, 12:14 PM
  2. Indexes in a Table
    By FB93 in forum Access
    Replies: 2
    Last Post: 03-18-2014, 07:27 PM
  3. Too Many Indexes
    By cbrsix in forum Database Design
    Replies: 22
    Last Post: 11-07-2011, 11:12 AM
  4. Indexes limited
    By DanW in forum Access
    Replies: 6
    Last Post: 11-12-2009, 03:12 AM
  5. Table Indexes
    By AnthonyT in forum Access
    Replies: 3
    Last Post: 06-04-2009, 06:16 AM

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