Creates db1, db2 on "compact on close"

[JohnC]

Has anybody figured out a solution for this? We are having this issue on
our network .mdb files. Started in October 2007. Very frustrating..
John


Newsgroups: microsoft.public.access
From: (e-mail address removed)
Date: 21 Oct 2007 03:44:33 -0700
Local: Sun, Oct 21 2007 5:44 am
Subject: Creates db1, db2 on "compact on close"

Smart people: I have a split database I have been using for over a
year with no funky issues. We add and delete records every day, so I
have "compact on close" set up on the front end db. Last week, it
started a) creating the db1, db2, etc, and not deleting them and b)
not compacting. db1, db2, etc are all functional databases. My
understanding of the process is this:

1. User closes db
2. Access creates a new db, and compact it, naming it db1, db2,
whatever.
3. Access deletes original, then renames db1 to original file name.


Mine seems to be hanging at step three. I get a new, compacted db, but
it's got a new name (db1, db2, whatever), and my original uncompacted
db is still there.


So, did some testing, found this out:
1) It only occurs on network drives. C: acts normally.
2) It occurs on both back end and front end dbs. So even moduless db's
do this, so it's not a corrupt db issue (I did a decompile, made no
difference, just for the heck of it).
3) I have all permissions on the folder except "full control" and
"special permissions". That is set my out IT folks, and that hasn't
changed. I can add and delete stuff all the time, so I don't think
it's a security thing.
4) I have oodles of space, so it's not the space shortage issue.


Thoughts? I'm stumped. I can't keep creating infinite dbs, and have
the db keep growing, and growing.... I need to compact!


Help!

On Oct 25, 11:53 am, Rajesh Patel wrote:


Figured it out: it's a SP3 issue. SP3 got pushed 10/17, which is when
this magically appeared. So I emailed MS, as did my IT dept, and we'll
see if anything happens...

Very annoying... how many hours did I put into dinking with it. But on
the plus side, I got smarter on a couple of things...

We have the same issue university-wide on network drives. Has anyone
found a soultion yet?

Thanks,
djsco

 

[Nona]

I am interested in this issue too because it has been a big problem on our
network. I read in a previous blog that it's preferable to set the database
NOT to compact each time it closes. Rather to compact once a month or so. If
there's a better solution, I'd like to hear it.

 

[tcarlsonsussex]

Thank you John for that very clear description of the issue. We have
had the exact same behavior with our databases beginning in September
2007, on databases that worked fine for years previous to that. Even
a command line compact yields the same result. The only successful
way I've found is to copy the database to a local drive, compact it,
then copy it back to the network. I am about ready to redeploy the
databases on shared drives on local workstations which does seem to
miss the point of the network.

 

[tcarlsonsussex]

Solution found. Found the fix for the db1 on compact issue.

http://support.microsoft.com/kb/945674

The issue was introduced with Service Pack 3 and fixed with the
December 2007 Service Pack 3 hotfix.

Installed, tested, it worked for me!

 

[JohnC]

I believe this was tried by one of our network users and it did not work.
Our IT department said they applied the hotfix on the user's PC (we are not
admins on our PCs). Perhaps I should verify file versions after the alleged
hotfix. What is the best way to verify:

Msaccess.exe Version 11.0.8204.0

Msaexp30.dll Version 11.0.8153.0

Mscdm.dll Version 11.0.8204.0 ?

John

 

[tcarlsonsussex]

What is the best way to verify:
Msaccess.exe Version 11.0.8204.0
Msaexp30.dll Version 11.0.8153.0
Mscdm.dll Version 11.0.8204.0   ?

John,

To verify file versions, the first two program files were in
C:\Program Files\Microsoft Office\OFFICE11

The third was in
C:\Program Files\Common Files\Microsoft Shared\MSClientDataMgr

For each file, right click, properties, version tab, file version

I'm very confident this hotfix did it for us. Likely it would have to
be applied to each front end.

My versions match those listed on the hotfix page

msaccess.exe 11.0.8204.0
msaexp30.dll 11.0.8153.0
mscdm.dll 11.0.8204.0

Hotfix page
http://support.microsoft.com/kb/945674

 

[cew47]

THANK YOU! This has been driving me crazy. This solution worked wonders.
Thanks for the help.

 

[David W. Fenton]

Has anybody figured out a solution for this? We are having this
issue on our network .mdb files. Started in October 2007. Very
frustrating..

Well, for one, you should never under any circumstances enable
"COMPACT ON CLOSE" as it's a useless and dangerous "feature" of
Access that never should have been implemented (at least, not in the
way it was done -- if it prompted you and allowed you to abort the
compact, it would be OK; without that, it's way too dangerous to
ever be used).

 

[news.virginmedia.com]

But the mde file grows with use and without regular compaction slows down.
Alex

 

[Douglas J. Steele]

There's a big difference between "regular compaction" and "compacting every
time the database is used".

Perhaps some time should be spent determining why the mde file is growing.
Front-end databases shouldn't be growing that much.

 

[John W. Vinson]

There's a big difference between "regular compaction" and "compacting every
time the database is used".

Perhaps some time should be spent determining why the mde file is growing.
Front-end databases shouldn't be growing that much.

and even if they do, they can be replaced as needed with a clean copy from the
server.

 

[David W. Fenton]

But the mde file grows with use and without regular compaction
slows down.

A properly-engineered MDE front end will grow for a few weeks and
then stop growing. My experience has been that a 3MB compacted MDE
can grow to almost 5MBs, but then it stops.

If it's growing more than that, it suggests that you're storing data
in it, or using temp tables in the front end. Temp tables should
never be stored in your application front end (nor in the back end).
Create a dedicated temp database that you distribute along with your
front in that has your temp tables in it and link to those. That one
would be safe to use COMPACT ON CLOSE, since it's not going to be
opened often, and it will either need to be compacted every now and
again, or replaced from a pristine, empty copy (or created from
scratch in code, as Tony Toews does it).