Access 2002 crashes when I use navigation buttons!!!

[edself]

I've created a number of access databases and never had any problems with
navigation buttons until now.

I have a form which allows viewing of approximately 1000 records of data.
Recently, I noticed that I can use the next record and last record portions
of the standard Access navigation bar without problems. The previous record
button also works fine. However, if I use the First record navigation
button, or if I type in a new record number that goes back MORE THAN one
record, Access will crash almost every time.

Other forms in my database do not seem to have this problem. In fact, even
the form that is crashing will work fine if I specify a filter that reduces
the number of records from around a thousand down to a handful of records.
In the filtered case, the First Record navigation bar works fine.

Have you ever heard of such a problem? This is bizarre.

 

[Allen Browne]

First thought is some kind of corruption, either with the code behind that
form, or with the indexes of the table.

1. Make a copy of the mdb file while it is not in use, and without
overwriting any previous backups.

2. Compact the database to get rid of any temp stuff.

3. Decompile, by entering something like this at the command prompt while
Access is not running. (It's all one line, and include the quote marks.)
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\My
Documents\MyDatabase.mdb"

4. Compact again.

Test. If the database still has the same problem, try removing all relations
between this table and others. Then open the table in design view, and
remove any indexes including the primary key. (Don't remove the fields: just
the indexes.) Compact again. Test.

If the database is repaired at this stage, recreate the indexes and
relations, and compile the code.

 

[Ed]

Thank you Allen for the thorough info. I have a question. One of the steps
you recommended was...

If the database still has the same problem, try removing all relations

between this table and others. Then open the table in design view, and
remove any indexes including the primary key. (Don't remove the fields: just
the indexes.) Compact again. Test.

If the database is repaired at this stage, recreate the indexes and
relations, and compile the code.

I understand what you mean by removing and recreating the relations, but can
you explain more exactly what you mean by removing indexes. How do I do
this? I am fairly new to database programming, so this question may seem
dumb. The key fields have a little key symbol in design view. You said NOT
to remove the field itself, so how do I remove the index without removing
the field?

Thank you for your help!
Ed

 

[Allen Browne]

Open the table in design view.
Open the Indexes box (toolbar icon).
This shows you all the indexes in the table.

For indexes on a single field, you could achieve the same result by setting
their Indexed property to No in the lower pane of the Table Design view.

The point of deleting the relations as well (in the Relationships window) is
that Access creates hidden indexes in order to manage relations that have
Referential Integrity enforced.