Can't Create An Indexed Field in Access

[PeterB]

Hi,

I've got a table in access with around 600,000 rows. I need to make many
many lookups from the table and so indexing it is important for runtimes.

I can index the other 6 fields in the table but one field I can't index.
The one in question has around 60 distinct values in it and a datatype of
integer.

Whenever I try to index it Access freezes (one blue box appears in the
progress bar on the status bar). I've tried indexing it using SQL, through
the table design view and through the Indexes dialogue. I've also tried
changing the field name.

Anyone got any ideas what might be happening?

I'm using Access 2003.

Many thanks

Peter

 

[Stefan Hoffmann]

hi Peter,

Whenever I try to index it Access freezes (one blue box appears in the
progress bar on the status bar). I've tried indexing it using SQL, through
the table design view and through the Indexes dialogue. I've also tried
changing the field name.
Anyone got any ideas what might be happening?

The only thing I can imagine: How big is your database, size in MB.
Maybe it exceeds 2GB limit while indexing.


mfG
--> stefan <--

 

[PeterB]

Thanks for replying Stefan.

I wondered about that so deleted all the other tables in the DB and
Compacted/Repaired it and then tried again - no joy!

The database size at the time was around 20mb so the 2gB limit can't be an
issue, can it?

 

[Stefan Hoffmann]

hi Peter,

I wondered about that so deleted all the other tables in the DB and
Compacted/Repaired it and then tried again - no joy!

Try creating a new .mdb and import only this table.

The database size at the time was around 20mb so the 2gB limit can't be an
issue, can it?

If it is 20MB after compressing, it should not be a problem at all.

btw, the database is on your local drive?


mfG
--> stefan <--

 

[John W. Vinson]

Whenever I try to index it Access freezes (one blue box appears in the
progress bar on the status bar). I've tried indexing it using SQL, through
the table design view and through the Indexes dialogue. I've also tried
changing the field name.

This might be some sort of subtle corruption. Compacting might not repair it
in all cases! You might want to try creating a new database, with all the
tables and indexes defined, empty; then link to this one and run Append
queries to migrate the data into the tables.

Is there anything peculiar about the contents of this field? Is it an Integer
or a Long Integer field?

John W. Vinson [MVP]