Cannot Complete Compact and Repair

  • Thread starter Thread starter DP
  • Start date Start date
D

DP

Am running Access 2000 under XP Pro. The data base
has been running for 3-4 years and is accessed every day.
(I'm retired!) At has <4000 records/rows of about 70
fields each. It's up to 18mb at this moment.

Suddenly I have been unable to compact it as it runs
several seconds then stops without regenerating the db.
It has created the db1.mdb file and done nothing else.
When I repeat the action it creates db2.mbd, db3.mdb, etc.
Another data base of about 8mb runs Compact ok.

What can cause this? Is the code and data getting old and
tired? I know I am. Thanks for your help.
 
Hi DP,

It sounds like you could have some corruption going on. Try creating a brand new database
and importing all objects (tables, queries forms, reports, macros, DAP's and modules) into
it. Use File > Get External Data > Import... to accomplish this operation. I recommend
importing objects one group at a time (all tables, then all queries, then all forms, etc.)
instead of trying to import everything in one operation.

You may need to set references appropriately in the new database. Press Alt F11 to open
up the Visual Basic editor in the 18 MB database. Click on Tools > References. Make a
note of the references you see, and the order that they are shown listed. Then close the
Visual Basic editor in this database and open it in your new database. Verify that you
have the same references selected, and that they are in the same order (just in case any
code that might be present is not disambiguated). Click on Debug > Compile to see if the
new database will compile properly. Then close the Visual Basic editor. See if your new
database will compact properly.

Please let us know the results!

Tom
____________________________________


Am running Access 2000 under XP Pro. The data base
has been running for 3-4 years and is accessed every day.
(I'm retired!) At has <4000 records/rows of about 70
fields each. It's up to 18mb at this moment.

Suddenly I have been unable to compact it as it runs
several seconds then stops without regenerating the db.
It has created the db1.mdb file and done nothing else.
When I repeat the action it creates db2.mbd, db3.mdb, etc.
Another data base of about 8mb runs Compact ok.

What can cause this? Is the code and data getting old and
tired? I know I am. Thanks for your help.
 
In addition to what the others have said, you really need to split the
database into tables and evrything else. Even when running on a single
computer, you always want the data to reside in its own container. There is
even a wizard (Tools ... Database Utilities ... Database Splitter) to d it
for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks for the ideas.
1. I don't believe the database was readonly (properties
didn't show it) and I had been updating data (I assume
that's a clue).
2. I began to split the db apart and ran into troubles
dealing with the main table.

The narrative:

Steps Taken to Build New Database: File New. Get external
data from 18mg file. Quickly and successfully loaded
everything into new db except main table.
Can close and reload new mdb. Can open all tables. Seems
ok to this point.
(Didn't open forms as there was no main table.)

Tried to load main table. Does not complete action.
Completion scale sits at about 2/3 across and quietly
grinds away.

Can do other work, such as, open tables.
However, when trying to close any table and click x in
upper right get the msg: "This action will reset the
current code in break mode. Do you want to stop the
running code?
* To halt the execution of the program so the Module
window can be closed, select Yes
* To leave the code in the current state, select No"

If No, goes back to the Table display.
If Yes, gets beep and message comes back : "This action
will reset..." Looping would continue.

Way out is to minimize open Table, but start to accumulate
several min windows. To get out of it completely have to
abort MSAccess.exe

At this time, it apparently is still trying to load the
main table. MSAccess.exe is using the entire machine (97-
99% CPU). Using 21.2mb avail. memory out of 384mb
available.

Started process at 0420. It is now 0431. Will wait. Played
some games not too successfully.
It is continuing to "peg the cpu", so will go back to bed
and leave the business running.
Can't tell if any progress is being made, but other
applications continue to run, so is not frozen.

Later the same day: 14:41 task is still running full out
(having started at 0420).
Cannot tell if anything is being done. The pc is working
around this very well, albeit nominally slower than usual.

Any ideas: (1. if anything is being done? (2. How long
this could last? (3. Is it worth continuing? (4. Is there
another way?

Thank you for your guidance. DP
 
Hello DP,
1. I don't believe the database was read-only (properties
didn't show it) and I had been updating data (I assume
that's a clue).
Click to expand...
Sounds reasonable to me.

It sounds like the corruption is in your "Main Table", or perhaps a hidden system table
that holds information related to this table. I don't think that waiting any longer is
going to be helpful, given that you've already waited for several hours. Try the
following method to see if you can recover this table:

How to Recover Data from a Corrupted Table by Using the DAO Method
http://support.microsoft.com/?id=815280

Tom
_________________________________________


Thanks for the ideas.
1. I don't believe the database was readonly (properties
didn't show it) and I had been updating data (I assume
that's a clue).
2. I began to split the db apart and ran into troubles
dealing with the main table.

The narrative:

Steps Taken to Build New Database: File New. Get external
data from 18mg file. Quickly and successfully loaded
everything into new db except main table.
Can close and reload new mdb. Can open all tables. Seems
ok to this point.
(Didn't open forms as there was no main table.)

Tried to load main table. Does not complete action.
Completion scale sits at about 2/3 across and quietly
grinds away.

Can do other work, such as, open tables.
However, when trying to close any table and click x in
upper right get the msg: "This action will reset the
current code in break mode. Do you want to stop the
running code?
* To halt the execution of the program so the Module
window can be closed, select Yes
* To leave the code in the current state, select No"

If No, goes back to the Table display.
If Yes, gets beep and message comes back : "This action
will reset..." Looping would continue.

Way out is to minimize open Table, but start to accumulate
several min windows. To get out of it completely have to
abort MSAccess.exe

At this time, it apparently is still trying to load the
main table. MSAccess.exe is using the entire machine (97-
99% CPU). Using 21.2mb avail. memory out of 384mb
available.

Started process at 0420. It is now 0431. Will wait. Played
some games not too successfully.
It is continuing to "peg the cpu", so will go back to bed
and leave the business running.
Can't tell if any progress is being made, but other
applications continue to run, so is not frozen.

Later the same day: 14:41 task is still running full out
(having started at 0420).
Cannot tell if anything is being done. The pc is working
around this very well, albeit nominally slower than usual.

Any ideas: (1. if anything is being done? (2. How long
this could last? (3. Is it worth continuing? (4. Is there
another way?

Thank you for your guidance. DP
 
DP said:
2. I began to split the db apart and ran into troubles
dealing with the main table.

The narrative:
Click to expand...

Thanks for the detailed steps. There are some alternatives to doing
an import at the Access Corruptions FAQ at my website.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Gentlemen, thank you for your help -- I'll keep your
references in my library of "Access Tips" -- never can
have too many of those.

I cancelled the rebuild and decided to see how much good
and bad data I was dealing with. I cut the file in half
and was able to complete a compact so figured I was on to
something.

By the process of selecting, compacting and deleting, I
found 2 records: 3203 and 3421 (if your interested) which
caused my problems. It was good to have long wall-to-wall
football games on during this...

Everything's fine now. Thanks again.

Hope you read this closure!

Dick Pape
 
Hi Dick,

Thanks for updating us on your progress. I'm guessing that the affected table included
one or more memo fields. These are known for being a bit more susceptible to corruption
versus other data types. Memo data types are not stored within the same record, since
they can become quite large (65,535 characters when entered via a form), while the maximum
size for a record is 2 KB. Instead, a pointer is stored that points to the memo data.

To help minimize problems in the future, you might want to consider moving the memo field
to a new table, and creating a 1:1 relationship with your base table. This makes recovery
of the data easier in the event of corruption of a memo field.

By the way, what size did it compact down to? You indicated 18 MB as a starting size.

Tom
____________________________________


Gentlemen, thank you for your help -- I'll keep your
references in my library of "Access Tips" -- never can
have too many of those.

I cancelled the rebuild and decided to see how much good
and bad data I was dealing with. I cut the file in half
and was able to complete a compact so figured I was on to
something.

By the process of selecting, compacting and deleting, I
found 2 records: 3203 and 3421 (if your interested) which
caused my problems. It was good to have long wall-to-wall
football games on during this...

Everything's fine now. Thanks again.

Hope you read this closure!

Dick Pape
 
Tom --

1. My db compressed to 10.5mb from 18mb. 3854 records.

2. You said "the maximum size for a record is 2 KB".
Didn't know that. I do have 70 fields with full text
values of more than 10 chars in each so could easily be
pushing that number. What happens when one gets bigger? Is
there a way to find out how big any record is? Is there a
reference to that subject that you know of?

3. On this problem got general comments to separate data
from queries, forms and so on. Did that some time ago and
it got complicated to compress, backup and restore. What
are some other reasons to have multiple files? Again, do
you know of a reference for this?

Thanks.

Dick Pape
 
Hi Dick,

70 fields in a table sounds a bit excessive. It sounds like you might be storing data in
a manner which is not optimized for a relational database. Do your tables store
information on a single subject? I suspect that you might be attempting to store one or
more "one-to-many" (1:M) relationships in your table. As a point of reference, Total
Access Analyzer (http://www.fmsinc.com/products/analyzer/index.html), an FMS product, is
set by default to flag tables with 32 or more fields.
What happens when one gets bigger?
Click to expand...
I don't know for sure, but I suspect that Access will simply puke.
Is there a way to find out how big any record is?
Click to expand...
Not that I know of. I suppose one could calculate it, based upon the various datatypes,
how many characters were stored in text fields, and whether Unicode compression was
enabled for text datatypes.
Is there a reference to that subject that you know of?
Click to expand...
You can learn the specifications for an Access database, by opening the Help file &
selecting the Index tab. Enter "specification" (without the double quotes) as a search
term.

Regarding splitting your database into a back-end (tables only) and front-end (queries,
forms, reports, macros, modules and any static tables), this is a very good suggestion.
It is most essential if you have a shared (multiuser) database, as each user should have a
copy of the front-end database on their own PC. It is also quite useful for single-user
applications. For one thing, you should only need to make regular backups of the back-end
database, so your backups will be smaller in size. The front-end would only need to be
backed up if you made a change to one of the objects in this database. I'm not sure why
you state that it got complicated to compress, backup and restore....can you elaborate on
that?

If you are willing to do this, make a copy of your front-end and back-end databases.
Delete all of the records from the copy of your back-end database. Compact both databases
and send them to me. If you know how to add them to a .zip archive file, then do that
before sending them. I will take a look at your current design and make recommendations.
I'll also do an analysis of your database using Total Access Analyzer and e-mail you the
results. If you'd like to take me up on this offer (no charge), send your databases to:

AOS168 AT Comcast DOT net

(Replace the AT and DOT with the appropriate symbols).

Tom
___________________________________


Tom --

1. My db compressed to 10.5mb from 18mb. 3854 records.

2. You said "the maximum size for a record is 2 KB".
Didn't know that. I do have 70 fields with full text
values of more than 10 chars in each so could easily be
pushing that number. What happens when one gets bigger? Is
there a way to find out how big any record is? Is there a
reference to that subject that you know of?

3. On this problem got general comments to separate data
from queries, forms and so on. Did that some time ago and
it got complicated to compress, backup and restore. What
are some other reasons to have multiple files? Again, do
you know of a reference for this?

Thanks.

Dick Pape
 
On Tue, 6 Jan 2004 12:49:56 -0800, "DP"

Just to add to Tom's accurate statements...
1. My db compressed to 10.5mb from 18mb. 3854 records.
Click to expand...

That's not at all big. 250MByte is getting largish.
2. You said "the maximum size for a record is 2 KB".
Didn't know that. I do have 70 fields with full text
values of more than 10 chars in each so could easily be
pushing that number. What happens when one gets bigger? Is
there a way to find out how big any record is? Is there a
reference to that subject that you know of?
Click to expand...

Help for "Specifications". If you actually try to create a record with
over 2000 bytes (not counting memo fields or trailing blanks, which
aren't stored) you won't be able to do so; you'll get an error
message. You can only find out the size of a record by adding up the
Len() of all the text fields, plus the size of each non-text.
3. On this problem got general comments to separate data
from queries, forms and so on. Did that some time ago and
it got complicated to compress, backup and restore. What
are some other reasons to have multiple files? Again, do
you know of a reference for this?
Click to expand...

Do split it, especially if it will be multiuser, or even if the single
user is someone other than yourself; by doing so you'll be able to
give the user a new frontend when you improve a form or add a new
report, without the considerable complexity of working around their
stored data. Backing up should not be a problem - the backend should
be backed up and compacted regularly; the frontend should be backed up
but don't bother compacting it. Just junk it and replace it with a
clean copy if it bloats.
 
Back
Top