Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making indexes (2003)

    I had a few problems with a database recently where the indexes of some of the tables just disappeared and stopped the application working as it should. I rebuild the indexes and all was OK for a couple of days until the same happened again. I've since rebuild the database and all is OK. It got me thinking about indexes/primary keys - is there a method by which I could store details of the indexes/primaries required and use some code to apply them to tables within a database, rather than having to add them back manually.

    Any thoughts?

    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making indexes (2003)

    Although it is possible to create indexes using code, it shouldn't be necessary. That indexes disappeared twice within a few days indicates that the database had become corrupt. How did you rebuild it?

    Do you have a split frontend-backend design? If not, you should - see Why Split a Database?.

    When you've got a correctly working copy of the database, you could create a copy of the backend (the database with all the tables) and remove all records from the copy. If something goes wrong, you can use this 'empty' copy as a starting point for rebuilding the database. Of course, regularly creating backups of the full database is very important too!

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making indexes (2003)

    The database seemed to be complete apart from a number of tables where the index/primary key information was missing. I have a print of the table structure from Tools...Analyze...Documenter so had the information for all the indexes. I just went through each table and recreated any indexes which were missing.

    I DON'T have it split as frontend-backend - one of those things I know I should do but never got round to for this one. It's used by a few people who populate various tables within the database.

    I do backup the database each night (and tested the restore too), but as the data was intact it seemed better (quicker) to reindex rather than reverting to data from the previous days backup.

    I was surprised that the indexes were removed in such a "clean" way - all the indexes from a numbr of different tables. The relationships between the tables remained and I didn't need to do anything with these to get the database to work again. I presume that somewhere within the database structure Access holds a secret table of index information and this is what was corrupted - or is this too simple a desciption of what happens? My only other thought is that someone removed the indexes manually (accidently or not), hence my concern about the clean way in which the indexes disappeared, but to do this across a few tables would be less than accidental!

    I am also presuming that re-entering the index information will have cured the problem, though as it has happened twice I can't be 100% sure of this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making indexes (2003)

    Indexes are indeed stored in a (completely) hidden system table; if that table becomes corrupt, it may be deleted completely.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making indexes (2003)

    Is it enough to simply re-enter the index information - or should i remove then re-enter more information that this? If the system table has been damaged once is more likely to corrupt again, or are such errors fixed by compact/repair ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Making indexes (2003)

    Compact/Repair rebuilds the indexes.

    Splitting the database into a frontend and backend reduces the probability of database corruption.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •