Cannot compact

[Hank]

I have been compacting my backend databases on a daily basis for
years. Today the compact operation hangs up both internally or when
run from a shortcut using the /Compact switch. Older copies will
compact. This one db had a couple of forms and queries which I
delteted, just leaving me with tables but that did not help.

I am aware than you cannot compact an open database but as long as
no table is being accessed you can do it on the tools menu.

I start the compact, it seems to run and then hangs up part way
through with the status bar about halfway full. I "X" out and then
see that db1.mdb has been created but not deleted.

Any ideas?

Hank Reed

[teddysnips]

Hank wrote:

I have been compacting my backend databases on a daily basis for
years. Today the compact operation hangs up both internally or when
run from a shortcut using the /Compact switch. Older copies will
compact. This one db had a couple of forms and queries which I
delteted, just leaving me with tables but that did not help.

I am aware than you cannot compact an open database but as long as
no table is being accessed you can do it on the tools menu.

I start the compact, it seems to run and then hangs up part way
through with the status bar about halfway full. I "X" out and then
see that db1.mdb has been created but not deleted.

Possibly corrupt? Is the uncompacted backend usable? I had a similar
problem a while ago and I just added a code module, added a dummy
function e.g.

Public Sub DoNowt

DoEvents

End Sub

I then compiled, and this seemed to sort things out.

I am aware that this might seem a rather haphazard approach, but when
backends go pear-shaped, sometimes you have to resort to desperate
measures. You could also try to decompile (msaccess /decompile <your
database name>) but this should only be attempted with a recent safe
backup. You could also try importing the db objects into a new
database.

Good luck

Edward

[Jim M]

Good advice. I would also consider trying the Microsoft utility
"JetComp.exe". You can find it on their web sites. It's a little more
robust than the internal compact and repair in Access.

If you import the tables one at a time into a new back end data file,
try compacting after each one. If you find a table you can't import or
that makes the compact choke, try to eyeball it and see if there is a
corrupt record you can manually delete... I once created a new empty
table and either ran queries or cut and pasted all the records before
or after my corrupt record...

Good luck. If worse comes to worse, you can always try one of the
commercial data recovery services.

Jim

[MacDermott]

Check out db1.mdb - does it have everything you need?
Can you just rename it and use it?

"Hank" <ha********@aol.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I have been compacting my backend databases on a daily basis for
years. Today the compact operation hangs up both internally or when
run from a shortcut using the /Compact switch. Older copies will
compact. This one db had a couple of forms and queries which I
delteted, just leaving me with tables but that did not help.

I am aware than you cannot compact an open database but as long as
no table is being accessed you can do it on the tools menu.

I start the compact, it seems to run and then hangs up part way
through with the status bar about halfway full. I "X" out and then
see that db1.mdb has been created but not deleted.

Any ideas?

Hank Reed

[Hank]

Hello,
Thanks for all the good ideas. I tried JetComp and it aborted
everytime. I took Jim's advice about importing the tables into a new
database. You can actually import them all at once with
File\GetExternalData\Import. I selected all tables and started the
import. The import aborted on a table named [Audit Trail]. When I
eliminated that table (Don't worry guys, I'm using a backup copy) the
database would compact OK.
Then I started reading [Audit Table] (saving the record number in
another table) until it broke. The bad record was the next one after
13900. When I tried to view the table, its was OK until I hit the
corrupted record, and then aborted. So I wrote a query to delete the
offending record.
Everything is now OK.
Thanks again,
Hank Reed

[David W. Fenton]

"Hank" <ha********@aol.com> wrote in
news:11*********************@o13g2000cwo.googlegro ups.com:

Thanks for all the good ideas. I tried JetComp and it
aborted
everytime. I took Jim's advice about importing the tables into a
new database. You can actually import them all at once with
File\GetExternalData\Import. I selected all tables and started
the import. The import aborted on a table named [Audit Trail].
When I eliminated that table (Don't worry guys, I'm using a backup
copy) the database would compact OK.
Then I started reading [Audit Table] (saving the record
number in
another table) until it broke. The bad record was the next one
after 13900. When I tried to view the table, its was OK until I
hit the corrupted record, and then aborted. So I wrote a query to
delete the offending record.

Is there a memo field in that table?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Hank]

Hello David,

No, there is no memo field. What would be the significance of that?

Thanks,
Hank Reed

[David W. Fenton]

"Hank" <ha********@aol.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:

No, there is no memo field. What would be the significance
of that?

That's a very common cause of exactly the kind of error you
encountered, because it's easy for the memo pointers to be
corrupted(memos are not stored in the same data pages as the actual
records; only a pointer to the correct data page is stored with the
record; this is natural, since memo fields can be huge or small or
anything in between). And usually the only way to correct it is to
get the non-memo data out of the record, delete it and recreate it.
The memo data itself is almost always lost.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc