Compact and Repair causes problems 1

[SteveCop2]

I have a large Access system (Office 2003, Windows XP Pro) which comprises the main program file with all forms, queries, reports and VBA and then 4 data files which share the 50+ tables feeding the system each table being linked into the prog file. I experience unpredicatble problems when doing compact and repair on the data files. For example I compacted all 4 yesterday but from the table view in the program file one of the tables was garbled with apparently random data and some fields showing a missing data error. However when I look at the table direct on it's own file there is no problem. This has happened previously and there seems no pattern as to which, or how many, files will be affected on each occasion. To solve the difficulty I have to use the linked table manager to re-link in the tables. Whilst that is OK (sort of) I would really like to understand why this is happening. Can anyone help please?

 

[Trevil]

First, do you compact the "prog" file after compacting the four data files? If not, you may want to try that.

I assume you use the standard "Tools | Database Utilities |Compact & Repair"? We have a VB6 app that calls the Jet engine and that seems to recover more corrupted databases than "Tools...".

Since you see the corruption in the "prog" and not the back-end, it seems related to the link needing to be refreshed. I have never seen random data, but do see rows with pound-signs.

Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[SteveCop2]

Thanks Trevil for replying.

I must admit I did not compact the "prog" file too. I think I may have a copy of the non-working file at the office so I'll try that next week.

Yes I was using the standard Tool/Database Utilities/Compact and Repair function. Someone else suggested that there might be an issue because I compacted on the server over a network connection and that I should in future try running only on a local machine.

That might indeed be a reason for just one table on a file apparently failing. The random data is certainly worrying and this all points to some form of record pointer being affected and because the effect is isolated to just one table, and has previously affected other tables, it points to some form of glitch, the network seems a reasonable explanation!

 

[SteveCop2]

More feedback, Trevil....

I found a copy of the uncompacted system so just tried compact and repair on a local machine. This time the problems were worse - they affected more tables, the data was very garbled. even to the extent of a date showing in a number field etc. Access then crashed. Another table showed only 5 of its 360 lines.

So I tried your idea of then compacting and repairing the program file and success!!! Everything back to normal and problems gone away.

Many thanks for your help and a valuable lesson learned.

 

[Trevil]

Glad it's working now! Another thought, depending on how much usage the "prog" database gets, you could go to "Tools | Options | General Tab" and set "Compact on Close". However, if people are FREQUENTLY opening and closing the "prog", I would NOT do this.

You could also create a VB App to Compact the database(s) and have a scheduled task on your server to run at whatever frequency you want. (DBEngine.CompactDatabase....)

Learn from the mistakes of others. You won't live long enough to make all of them yourself.