Database Needs to be Repaired!

[Captain Chaos]

There is a front-end A2k database on a central server used by about 60 people. It works fine until it has been compacted and then after a short time it becomes corrupt. People logged in can carry on but no one new can get in.
It's not caused by crashes because on testing, one user has got in and the second has got the error message.
Any Ideas Please? The database has got to 190mb and could compact to 130mb.

 

[Mile-O]

One front end for all 60 people or one frontend per person?

 

[Captain Chaos]

One front end used by all. The program is constantly evolving so weekly deployment to individuals would be an issue.

 

[ghudson]

Your issue (problem) is the fact that you have multiple users accessing the same front end. You can not open a db object [form, report, etc.] in design view and save it while somebody else has that object open. Microsoft stopped that after Access 97. You will continue to have db problems [corruption] for as long as you allow more than one person to share the same front end as well as making design changes while the db is in use by another user. You should only make design changes when you have opened the front end 'exclusively'. You will need to create a routine to force a copy of the new front end onto the users PC each time you have published a new version to the front end.

 

[Captain Chaos]

There are no changes made to the live version. Users view, report and sometimes edit the data. Why do all these users have no problems until we compact?

 

[ghudson]

Have your tried the free Microsoft Jet Compact utility? Click on the link below for the version of Access you are using. For more details, search the Microsoft Knowledgebase for Jet Compact. I have had a lot of success using Jet Compact to repair a db when I thought it was corrupted and Access could not repair itself.

Access 97: Updated Version of Microsoft Jet 3.5 Available for Download

Access 2000: Jet Compact Utility Available in Download Center

Access 2002: Jet Compact Utility Available in Download Center

You have to ensure that everybody is out of the db before you attempt to compact the db. Since you have so many users you should turn off the auto compact on close option.

 

[Captain Chaos]

Have tried a 3rdParty compacter without success. Have downloaded this one and will try it.
To give you a bit more meat, There are two identical databases Merlin and MerlinR. They are linked to Avalon and AvalonR Datasources respectively. Merlin is worked on, upgraded etc and contains code which copies itself as MerlinR and links itself to AvalonR. Thus only changes need to be made to the one database.
Strangely we can compact MerlinR, an exact copy of Merlin except for links, but can't compact Merlin. Have tried removing all tables, compacting and relinking without success.
I may have to type it all again!
Thanks for you help so far

 

[ghudson]

If the Jet utility does not help you should import all of the db objects into a new database. That process will prompt you with an error message and list the object that will not import for it is probably corrupt if that is the problem.

I have learned that a db can has just a little bit of corruption [inivalid indexes are common] and work fine for months...then one day the moon is in the wrong position and the db starts to misbehave or not open. Regulary compacting, repairing and decompiling the db will prevent those types of problems.

 

[Captain Chaos]

Have tried the new database idea but that too fails.
The clue seems to be in the tables themselves. It's just one of life's mysteries.
Thanks again

 

[ghudson]

Can you export the tables to another db? Or do a transfer text via VBA of the tables. There must be a corrupt record(s) within a table.

 

[Captain Chaos]

Have tried an empty database and importing everything. No joy.
There are 4 local tables in the database and about 30 linked to the backend.
Have tried deleting them, compacting and reconnecting them looping thru a table of connect strings and sourcetable names.
No joy though.
about 25 of the backend tables are replaced with new data every night.
I'm beginning to suspect the data/index integrity probably of the unchanging linked tables.
Somebody must have seen this before?