Getting error about index when I open my DB

[natatbh]

When I open my db I get an error saying 'c:\dbname isn't
an index in this table. Look in the indexes collection of
the tabledef object to determine the valid index names'.
I tried compacting and repairing but I get the same error.
I also tried creating a new db and importing all the
tables from my db but by doing so I also got the same
error.

Any idea, Please let me know
thanks
natatbh

 

[WildHare]

First off, make sure you don't have any oddly named tables or indexes..no Access reserved words, no VBA functions as index names, etc etc etc..

If all that looks ok, you've got a couple of options.

1) Remove all the indices on your tables and attempt a compact/repair. Then re add the indices, one table at a time. If you get all of them out and then rebuilt with no problem, you should be ok.

2) IF you have a problem rebuilding one of the table's indices, it's probably due to a bad chunk of data.

Unfortunately, by "bad" I mean a field with a high-order ASCII byte value stuck in it somewhere like a Carriage Return (ASCII 10) or a Square Dot (ASCII 223) ▀

They're almost impossible to find unless you scroll through the data one by one. The tough ones are the non-printable characters, like the LineFeed guy.

There's no good explanation of how these folks get into our records, except bit decay, or a bad load process from an external source.

But I have seen this happen before.
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[RickSpr]

I got this message the other day when I was making some code changes behind a form while the form was open in Form View. After I switched to Design View, saved, and went back to Form View it went away, so it seemed to have something to do with the compiled code being out of sync with the VBA source code.

On that basis, it might work for you to decompile the database. You can do that by starting Access with the /decompile command line switch, e.g.
"C:\Program Files\Microsoft Office\Office\msaccess.exe" /decompile "C:\My Documents\My Database.mdb" Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[natatbh]

Thanks for your advice,

I have found a great solution to this problem myself. I will first explain the other options I usually use to fix this problem and the reason why it didn't work for me. I will then tell you what I did to fix it.

First I tried compacting and repairing the db. This didn't
seem to work. I then created an empty db and tried to import all the tables from my corrupted db. This didn't seem to work either. After searching around I found a utility called JetComp. This utility can be downloaded for free. (I forgot the url to the site but if you make a google search for jetcomp you'll find it). This utility compacts your db. When you try to compact a db with the access compact utility, it first attempts to open the db and then compacts it. Since my db was corrupted and could not be opened it was impossible to compact it. Importing the tables to a new db didn't work for the same reason. You can't import data from a db that can't be opened. The JetComp utility compacts your db without attempting to open it and can compact even a badly corupted db that will not open at all. The size of my corrupted db before using JetComp was a gig. After using the JetComp compacter it was 25 megabytes and the db was in perfect working condition. It did wonders for me and I hope it can help someone else too.

natatbh