Add index to existing table - is it auto indexed (1 Viewer)

bignose2

Registered User.
Local time
Today, 13:31
Joined
May 2, 2010
Messages
174
Hi,
I am adding index's to a few fields in a tables that already exists & has done so for a long time, perhaps 5000 records
They are date fields, few questions.

1) Is the existing data automatically indexed once I change the setting in the table, I only say as when I added them & closed the table there was no delay.
If as I understand when using normally there can be a slight overhead in creating the index I would have thought indexing the whole table would at least be a half second or more.

2) Is there even the tiniest risk to the data being corrupted adding late on.

3) It is for a booking system, I have date in & date out. Lots of query that search &/Or add up who is in between those dates.
Do you think is better to have a multiple index or Indate & Outdate separately.

I will do lots of testing but good to get a handle on most likely to work fastest.
thanks I/A
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Today, 07:31
Joined
Feb 28, 2001
Messages
17,009
To your questions:

1. If you open the table in design view, establish the index, and click SAVE then at that moment, the deed is done. If it is a big enough table, you might see a momentary delay.

2. Opening the database AT ALL exposes it to the tiniest risk of corruption. In life, even waking up risks death - a teenie little bit. There are no guarantees in existence. However, the solution is to ALWAYS make a copy of the DB from outside Access. Perhaps a Windows-based copy/paste operation to a secondary folder is adequate. Then diddle with your DB as needed, knowing you can revert to the copy if need be. The rationale behind a non-Access copy operation is that disk-to-disk copying doesn't try to interpret what is being copied. It copies what you've got, warts and all. Even a Compact&Repair CAN damage a database, unlikely though that is. So if I tell you open the DB to do indexing is PERFECTLY safe, I would be lying through my teeth. Since this isn't a poker table, I don't like like that.

3. Separate indexes on dates that can be searched separately. Definitely.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom