Making indexes (2003)

Topic

New Reply

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

Reply | Quote

Replies

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

Compact/Repair rebuilds the indexes.

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

Reply | Quote