Problems Compacting Access in Office 2007

R

Russ

Access 2007 does not appear to compact. For example, I had a database
containing 50K records and it created a database size of approx 70MB. I
deleted all records and then ran a compact. File size did not change. In
2003, that file would've shrunk to a much smaller size.

Am I missing something or has anyone else experienced the same problem?

Thanks.

Russ
 
B

Bill Mosca

Russ

Do you have a lot of forms and reports with images embedded in them? They
take up a lot of space.
 
A

a a r o n _ k e m p f

you don't need to compact and repair-- you need to upsize

JET is a bloated pig database.
XML is even worse.

Move to SQL Server, unless you like dealing with these headaches.
SQL Server uses 'auto-shrink' or you can use maintenance wizard to
setup a recurring schedule.
 
J

Jerry Whittle

Russ,

Check that there aren't a lot of DB1.mdb or DB1.accdb files littering up the
folder where the database resides. That would means something is preventing
the c&r from completing and leaving the original database file as is.
 
R

Russ

Thanks, but that's unfortunately not an option for me. I'm confused as to
why 2003 would shrink but 2007 is not.

Weird.
 
R

Russ

Thanks, but that's the only file in the folder. I've had other databases do
this and it's stumping as to why. I've tried saving the database using the
2000, 2003 and 2007 file formats with no success.

I was hoping there a was secret trick that I was missing. Darn.
 
R

Russ

I just found a workaround in the New Users section. Copy the files to the C
drive and then compact. That seems to work. Seems Access is having problems
compacting on network drives (that's what I'm working on).

Seems office 2007 is having some problems working on network drives.
Powerpoint was having linking problems until they issued a hotfix.

I wonder if MS will issue a hotfix for this problem.

Cheers,
 
R

Russ

Well, I agree that it's better to work locally, but my current occupation has
many databases on our network to where it's not feasible to move at this
point in time.

However, I do find it strange that 2003 would compress on the network
drives, but 2007 won't.
 
A

a a r o n _ k e m p f

It is an option.
You're just too weak to really know the benefits of SQL Server.

That's all you need to say is 'I don't have the cojones for a real
database'.
 
R

Russ

Brilliant! Why are you on this forum? Seems you got lost on your way to the
SQL Server forums and ended up in our inconsequential and inferior forum.

Amazing how you know nothing about me or my place of employment, yet you
know SQL Server is an option for me. Talk to my employer first and then let
me know it's an option. While I know using an SQL server would be very
helpful, it is something my employer is not willing to fund, thus it's not an
option.

Why don't you do something productive and practical like trying to answer
Access questions with Access solutions? That is what we're seeking in this
forum. Otherwise, have a nice day.

Cheers,

Russ
 
T

Tom Wickerath

Hi Russ,

While it may take some effort to copy each file to your local hard drive,
prior to compacting, this provides three benefits:

1.) Faster compaction, as Bill mentioned.
2.) Less chance of corruption, due to possible network noise. Access is very
suseptible to corruption, due to bad writes.
3.) The original copy on the network serves as your backup copy. You should
always back up a JET (.mdb) database prior to compacting. (And don't enable
compact on close, because it doesn't give you a chance to make that backup
copy).

I just helped a friend earlier this week who initiated a compact and repair
of his database over the network. The result was database corruption, with
the error message "AOIndex is not an index in this table" when he attempted
to open the database. He could not open it, link to it, or import objects
from it. Also, the JETComp utility did not help recover the corruption. So,
save yourself from future headaches, by only compacting locally. Verify that
the compacted database opens correctly. Then close it and copy it back to the
file server. Leave the local copy as a backup copy.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
R

Russ

Thanks. I'm totally agree. You made a great post. It's a keeper!

I ran into problems this weekend working on our network and lost access to
all of my modules. The program is now being written on my local drive.

Hard lesson learned, but a lesson learned nevertheless.

Cheers,

Russ D.
 
B

Bill Mosca

Russ

One more bit of advice...When developing, I always do it locally and use a
utility like KarenWare's Replicator or QuikSync that backs up my work every
hour to the network, keeping a 15-copy history just in case I blow up
something. Not using the network to store your files is just as bad as
developing directly on it. Your hard drive is most likely not backed up
daily like the network is (or should be).

--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals
 
P

pdok

Russ,

Try converting to an executable-type file (.accde) if you have that option.
It fixed the compacting problem for me.

Cheers,

Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"MSysCompactError", "System resource exceeded" & "You do not have the necessary permissions to use the...." 0
Compacting Access database 2
Compact and Repair Advice 14
Compacting Access Databases on an NTFS file system 4
Compact & Repair 14
Access 2007 "compact and repair" asking password 0
Compact Database 3
Compacting issue after installing SP3 3

Top