Corrupt Access Table

[Mokil]

Hi all, this is an unusual one that I haven't come across in ten years of using Access.

A customer runs our application, which is written in VB6 and uses an Access 97 database as its back-end.

Recently, the customer attempted to log in to the system and received a 3343 error. This is normally a corrupt database problem, but running a compact utility failed to solve the problem - the utility returned the same 3343 error.

I determined that two of the tables are corrupted - one sub-table (AttFiles), and the main database table (Cases). AttFiles returns a "Not a valid bookmark" error when attempting to open it, Cases returns a "Unrecognised database format [PATH]".

I have ran the following repair processes with the following results:

1. Access 97 - Corrupt/Unrecognised database
2. Access 2000 - Successful repair, Cases table deleted
3. Access 2002 - Successful repair, Cases table deleted
4. ODBC - Successful repair, Cases table deleted
5. JetComp - Successful repair, Cases table deleted

I've also tried the various compact utilities (including some I wrote myself), all with the same results as above.

The customer has numerous back-ups, but it turns out all the back-ups have the corruption.

I'm curious as to what causes this - I suspect a dodgy LAN connection or a bad hard disk cluster. But does anyone have any idea how to solve this? It will be much appreciated!

Thanks,
Mokil.

 

[DonQuichote]

If the database format isn't recognized anymore, then there's nothing I know of that you can do. But you state that this error only occurs when opening the "Cases" table. I think this table is lost...

However, the other table gives a bookmark error. Is this from VB? If so, you might be able to open it in the database itself. I encountered this once with an autonumber field. Only one field in one record was bad. The field was easily recognized, because it didn't even look like a number. I was able to copy the record with a query (all fields except for the bad one) and reroute all the related records to the copied one. The bad record could be deleted as long as you didn't refer to the bad field.

Good luck!

 

[SRQuadir]

Recently I have the same problem. Is there any way to avoid this in the futer?

Thanks

 

[roccorocks]

I had this same problem recently. The way I worked around this was to create a "structural" copy of the table (in access, if you can open the DB). Next, in the table with the error you will see #error all the way accross the columns. If you click on one of these columns you will get the "Not a valid bookmark" error. To get around this, sort any one of the columns in the table. You will now notice that those #error records are all gone (they are still there, but access excludes them!). Now you can copy all the data in the table. Paste it in your new "copy" table, then delete the old and rename the "copy" to what the old was named.

I believe the underlying error has to do with primary keys/index and some corruption that occurs on a record. I think have proper indexes and primary key are vital.

Rocco

 

[willir]

Friday the thirteenth -- there have been several such postings in the last few days...

Rocco and DonQuichote have given you some direction.

To answer your question on corruption, I see two possible issues...
- Primary key mess - do you use autonumber? Can you open the table in design mode and remove the primary key index?? In a multiuser system, it seems the general line of though is to create your own primary key, and not use the autonumber.

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=184

- Imporper closing the record / table / database. With legacy systems, this was a real problem (eg. dBase III EOF error). With the new systems, this is less of an issue, but the problem still does occur. The system still has to know when a file is open type of thing. Ensure your users exit gracefully, and the system is stable -- training, hardware, network topology. Avoid slow WAN access and other potential noisy network situations.

One issue not discussed is number of records and database size. A lot of users have commented that their databases have tens of thousands of records, over 100 MB in size, but I have seem too many databases (other than Access) blow up after they reached a critical size, or critical number of records. One thing I am not certain about is how well Access handles memo fields for instance and OLE's -- looks cool, really convenient, but how does it work when you have a 100,000 records with mutlitple memo fields (with lots of text). Technical information on this seems to be a little thin. With Access 97, memo fields are not indexed. With Access 2000 and 2002, the first 255 characters can be indexed. From a database perspective at the gut level, pointers have to be used to "point" - this record to this data, and how well does this work with Access??

Sorry for rambling...
Richard