Results 1 to 3 of 3
  1. #1
    FB93 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2014
    Posts
    5

    Indexes in a Table

    Hello

    New to Access (2007).

    Quick question: I am trying to consolidate a few tables I made to make them less numerous and hopefully easier to handle. At one point I received an error message that said I had exceeded the maximum number of indexes in a table (32) and thus it could not be saved. When I scroll through my table in design mode every box that has "Indexed" as an option is marked at "no". Many boxes do not have that option and thus I am not sure if those are automatically indexed(?), not indexed(?) or that indexing is not relevant for those values(?). I definitely do not have any, let alone 32+ boxes, that have "indexed" checked to "yes" though. They are all "no".

    How do I figure out which columns in my table are indexed otherwise so that I can reduce those numbers and get more columns on my tables (by the way, I am no where near the limit of 255 columns on any of my tables)



    Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Any field that will have a join (relationship) with another table should be indexed. Fields that are of the data type "Text" are generally NOT indexed. The exception would be a text field that will be used in search functions. If users will search for an alphanumeric job number often, you will want to index this field to help speed up searches.

    Indexing a field tells Access to pay close attention to that given field. Access will allocate additional memory for indexed fields.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a small routine to list all indexes in all your tables.
    Code:
    Sub IndexesAllTables()
              Dim db As dao.Database
              Dim tdf As dao.TableDef
              Dim idx As index
    
    10        On Error GoTo IndexesAllTables_Error
    
    20        Set db = CurrentDb
    30        For Each tdf In db.TableDefs
    40            Debug.Print tdf.name; "   " & tdf.Indexes.Count
    50            For Each idx In tdf.Indexes
    60                Debug.Print "  ---- " & idx.name & "  " & IIf(idx.Primary, "Primary.", "Not PK")
    70            Next idx
    80        Next tdf
    
    90        On Error GoTo 0
    100       Exit Sub
    
    IndexesAllTables_Error:
    
    110       MsgBox "Error " & Err.number &  "  on Line " & ERL & " (" & Err.Description & ") in procedure IndexesAllTables of Module AWF_Related"
    
    End Sub
    It sounds like you may have some corruption -- just a guess.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-24-2012, 07:11 AM
  2. Too Many Indexes
    By cbrsix in forum Database Design
    Replies: 22
    Last Post: 11-07-2011, 11:12 AM
  3. Indexes limited
    By DanW in forum Access
    Replies: 6
    Last Post: 11-12-2009, 03:12 AM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 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