Compacting NOT deleteing db1.mdb

[cbjames]

All of a sudden when we compact a database the db1.mdb file is created and
compacted into, but it does not delete the original mdb and rename the
db1.mdb to the original name. After compacting we have the original mdb and a
db1.mdb. The next day we have the original mdb, a db1.mdb and now a db2.mdb.
So on and so on. Can not figure out what changed. Anyone have any thoughts?

 

[cbjames]

We do have delete permission on the drives as we can manually delete the
original mdb and rename the db1.mdb

-cbjames

 

[Arvin Meyer [MVP]]

There is something wrong with either the compact process, i.e. the new file
is somehow corrupt, at least in part, or the old file has a problem. Or
there is a permissions problem. What compacting does is to create a new file
(db1.mdb) copy every thing to it, check it for corruption, delete the old
file, and rename the new to the old.

 

[John W. Vinson]

Anyone have any thoughts?

As Arvin says, something's wrong with the compact. See if there is a table
named MSysCompactErrors in the db1.mdb and read the errors therein if so.

John W. Vinson [MVP]

 

[FredFred]

I'm more confused by the answers than by the question.

What cbjames is describing is standard behavior for the compact function
unless you override it's default choice for the new file name from db1, 2
etc. to the current database file name.

Am I missing something?

Fred

 

[John W. Vinson]

I'm more confused by the answers than by the question.

What cbjames is describing is standard behavior for the compact function
unless you override it's default choice for the new file name from db1, 2
etc. to the current database file name.

Am I missing something?

Yes; the fact that ordinarily the old database is deleted, the db1.mdb
database is renamed to the old database's name, and there is no dbN database
left over after the compaction is done.

John W. Vinson [MVP]

 

[FredFred]

John,

Thanks for the response.

I know that that is the overall sequence to follow when one is compacting, ,
but as far I knew, the compact command alone doesn't handle all of that, one
has to do those other steps manually or programatically. (?) (I.e. that
the compact command ALONE does just what he is describing.)

Sincerely,

Fred

 

[John W. Vinson]

I know that that is the overall sequence to follow when one is compacting, ,
but as far I knew, the compact command alone doesn't handle all of that, one
has to do those other steps manually or programatically.

Not in my experience! When I open a .mdb file and select Tools... Database
Utilities... Compact and Repair, it whirs for a while and gives me my database
back, smaller (usually), and no extraneous files.

Compacting from code is indeed different though, you must specify the source
and destination databases and they must be different, if I recall aright.

John W. Vinson [MVP]

 

[FredFred]

John,

You probably know it so well that you are doing the second step without
thinking about it.

Based on my experience in Access 95.97/2000/2003: (I don't know about 2007)

First you pick the db to be compacted

Then you pick the "compact into" name. If you pick the same name (e.g.
click on the same .mdb file a second time) , it behaves as you describe. If
you make no selection, the default behavior is as cbjames describes.

Fred

 

[Arvin Meyer [MVP]]

Fred,

The difference is that we compact from within the current database. All
versions work the way that John and I describe. Even in Access 2.0 where
repair and compact were separate. It should even work that way outside of
the current database if you pick the same name. In that case, you get a
message asking if you want to replace the database with the same name.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[FredFred]

Dear Arvin,

Thanks for the info. All of this time I was under the wrong impression
that the only way to do it was from outside of the database (i.e. with it
closed)

Sincerely,

Fred

 

[AndreasJ.Mueller]

s. http://access.softwareheadlines.com...-not-require-full-control-permiss-t54583.html

Regards Andreas